Cannot create M:N relation

Posts   
 
    
Mountain
User
Posts: 21
Joined: 01-Apr-2005
# Posted on: 01-Apr-2005 16:42:46   

We have a table that models a hierarchical structure of items. (For an example, consider a table of employees where some employees are supervisors of others, and those supervisors in turn have supervisors. In our design, an employee could have more than 1 supervisor.)

We simply have the table of items and an associations table with 2 columns (parent and child) where both columns contain an ID (the PK) from the items table. I would think this is a very common situation.

The LLBL designer doesn't automatically create the full M:N relation. It only creates the 1:n sides (from items to assocations table). It does not let me create the M:N relation manually either.

On other tables where we use an associations table between TWO tables (instead of just one, as in this example), everything works as expected. No problems.

It's just this situation of an associations table on a single items table that doesn't work as desired. What am I doing wrong? Thanks.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 01-Apr-2005 17:14:15   

M:n relations with self are currently not supported (in 1.0.2004.1). If you want I can switch it on in 1.0.2004.2. It was disabled because the runtime engine couldn't alias in the past (before 1.0.2004.1, which wasn't a gui update).

Though as with all relations with self, as you're working on the same table, queries could be more optimial if you simply specify the filters yourself.

Frans Bouma | Lead developer LLBLGen Pro
Mountain
User
Posts: 21
Joined: 01-Apr-2005
# Posted on: 01-Apr-2005 19:03:10   

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?

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)

Mountain
User
Posts: 21
Joined: 01-Apr-2005
# Posted on: 01-Apr-2005 19:10:47   

Otis wrote:

M:n relations with self are currently not supported (in 1.0.2004.1). If you want I can switch it on in 1.0.2004.2.

I assume you wouldn't advise using the beta version in a production environment -- or is this beta such that the generated code will be stable?

If the generated code is suitable for production use, I would like to take advantage of m:n relations with self asap (assuming there isn't a better alternative design). Thanks.

Mountain
User
Posts: 21
Joined: 01-Apr-2005
# Posted on: 01-Apr-2005 19:15:15   

Otis wrote:

Though as with all relations with self, as you're working on the same table, queries could be more optimial if you simply specify the filters yourself.

I would like to understand this in more depth. What do you mean by "simply specify the filters yourself"? Do you mean the normal LLBL method of using a PredicateExpression?

Could you give a code example (or point to one in the docs) of how to specify a filter for a relation with self?

Thanks.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 02-Apr-2005 12:46:28   

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.

Frans Bouma | Lead developer LLBLGen Pro