RE: [Hibernate] Is it worth contributing this code or posting it somewhere?

[Jordan Laughlin]

Hello, I just wanted to jump in because I'm really not clear on how the queries produced could result in a cartesian product. Since every level of the graph contains an on clause that references the table before it, each level of the tree is limited (by its particular join type) and the amount of results returned at each level will be the amount of rows in the largest subset that meets its join condition. For instance, [SQL QUERY] Select * from A join B on A.id = B.id join C on C.id = B.id join D on D.id = C.id left join E on E.id = D.id This is exactly what our queries do: this does not result in a cartesian product, it returns at a maximum the number of rows in the largest table that meets a join condition. Now in each of our queries we may join any number of properties by left join since we know each join will return only one row (criteria does this as well). The cartesian product would show its ugly head if we instead attempted to load multiple collections per query, which is not what we are doing: instead we are doing one query per collection. For instance, if we wish to load A, its B property, and the collections C and D of property B, we would issue two different queries: [HIBERNATE QUERIES] Select A from A left join fetch B on A.id = B.id left join fetch C on C.id = B.id; And Select B from A left join fetch B on A.id = B.id left join fetch D on D.id = B.id; Since B is a property, it's left join returns only one row. By issuing the above queries in order in the same session, we get A, it's B property loaded, and the collections C and D for the B in A fully loaded. As far as we know Hibernate currently does not allow for object graph loading (loading a collection of a collection, etc.). The purpose of our code was to do that: if you can suggest a better way of doing this, or a way to split up the above queries when loading the graph, which avoids n+1 selects, please point us to the documentation. Thanks, Jordan -----Original Message----- From: Adrian Ridner [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 09, 2005 8:16 PM To: 'hibernate-devel@lists.sourceforge.net' Cc: [EMAIL PROTECTED] Subject: Re: [Hibernate] Is it worth contributing this code or posting it somewhere?   On Feb 7, 2005, at 9:17 PM, Christian Bauer wrote: > > On Feb 7, 2005, at 11:08 PM, Adrian Ridner wrote: > >> Did we miss something? I'll be optimistic and hope we didn't reinvent >> the wheel, but if we did, it was a fun exercise :) > > Read up on the docs some more. What you try to do would create a > Cartesian product and be slower than two queries (which is what you > discovered will happen). This is documented in every other place and a > sensible limitation. You can write a custom SQL query in very special > cases, if you are sure the product is faster than two queries. > Thanks for the reply. Sorry I didn't see it before, it got lost in my mailbox. I appreciate your feedback.  I guess that answers my original question.  We'll read up the docs more to see if we find anything. I am wondering if I didn't communicate what we are doing correctly though, because we are running this on tables with 5,000,000 rows each, and it is faster than anything else we tried. In any case, thank you for a great product! Adrian