Mountain wrote:
Thanks for the info. I do have the option to change the db design or take other alternative approaches. What would you recommend as the best approach in general?
Is there an alternative db design that is equivalent to an m:n relation with self, but that works better with LLBL?
No, it's the m:n relation with self which is a bit of a problem, or better: was a bit of a problem. The thing is that the generated code has to produce filters for the m:n fetch. At the moment it doesn't alias the start and end entity of the m:n relation, which means that if you would get these m:n relations with self generated into the code, the query won't work as an entity is joined twice without an alias.
I vote for turning on the m:n relations with self in the latest version of LLBL to at least provide that option when it is needed.
Thanks! (and thanks for the quick response)
Well, it IS problematic. The point is this:
SELECT E2.*
FROM Employee E1 INNER JOIN IntermediateTable I ON E1.EmployeeID = I.EmployeeID1
INNER JOIN Employee E2 ON I.EmployeeID2 = E2.EmployeeID
Now, this is probably not that hard to generate, but let's try to add a filter. Normally you'd just add a predicate and it will be all right, but with this, you'll have to specify an object alias as well, because if you want to filter on employee, which one will it be? So you'll have to specify an alias, but as this is inside generated code, how is it aliased?
I could add a default alias to either one of them, but that would break existing code, and I therefore won't do that. I could add an alias for the end entity if it's the same entity, but that's a bit messy, as the developer has to know if the end entity is different and then has to lookup what the alias is.
Relations with self are often better specified with a custom filter than with normal generated code, like this:
say I want all employees for which employee with ID 1 is the manager. I could do that in two ways:
SELECT e2.*
FROM Employee e1 INNER JOIN Employee e2
ON e1.ReportsTo = e2.EmployeeID
WHERE e2.EmployeeID = 1
or:
SELECT Employee.*
FROM Employee
WHERE ReportsTo = 1
The second query is the one being generated for relations with self. No join, just a filter.
Now, a normal m:1 relation would result in a join, though the generated code already makes this more optimal as the table joined is the same table, so why not just filter on the same table...
With m:n relations with self it's the same thing. Say I want all managers for a given employee 1
I could join it like the query I gave above:
SELECT E2.*
FROM Employee E1 INNER JOIN IntermediateTable I ON E1.EmployeeID = I.EmployeeID
INNER JOIN Employee E2 ON I.Manager = E2.EmployeeID
WHERE E1.Employee = 1
I could also do:
SELECT Employee.*
FROM Employee
WHERE EmployeeID IN
( SELECT Manager FROM IntermediateTable WHERE EmployeeID = 1)
(From my bare head, but I think it's the same)
The second is way faster. Though impossible to generate as it would require a special case in the template which is hard to determine.