Sorry, you're right, I didn't read your SQL fully. :-) I just saw LIMIT 100 and was like Noooooooo!!!! :-)
Your approach is absolutely perfect. Clinton On Wed, Sep 3, 2008 at 10:04 AM, Kai Grabfelder <[EMAIL PROTECTED]> wrote: > Clinton, > > how can I get inconsistent data with my approach? I think we misunderstood > each other ;-) My usecase was the following (and I think it was also the > usecase of the OP): > > Book --< Author = A book can have multiple authors. > > Now I want to select the first 100 books, sorted alpabetically, a book > filled up with its authors. So I do have the following resultMaps > > <resultMap id="book" class="Book" groupBy="bookId"> > <result property="bookId"/> > <result property="title"/> > <result property="authors" resultMap="author"/> > </resultMap> > > > <resultMap id="author" class="Author"> > <result property="authorId"/> > <result property="bookId"/> > <result property="name"/> > </resultMap> > > Here I don't see why the following select should lead to a inconsistent view > of records... > > <select id="booksWithTheirAuthors" resultMap="book"> > select * from book, author where book.bookId = author.bookId > and book.bookId in (select bookId from book order by title asc limit 100) > </select> > > Of course you could do this in two separate selects without having any > impact on the results. > > > > --- Original Nachricht --- > Absender: Clinton Begin > Datum: 03.09.2008 16:47 >> >> But Kai, using that approach, you will get an inconsistent view of the >> records. Some child records will be missing. >> >> the way that ORMs do this is with two queries... >> >> select distinct P.ID from PERSON P, DEPARTMENT D .... LIMIT 100 >> >> select * from PERSON P, DEPARTMENT D ..... and P.ID in (23, 45, 63, .... >> 104) >> >> iBATIS cannot do that automatically. It's a limitation of not >> generating the SQL. You can achieve the same thing by writing both >> queries yourself though. >> >> Clinton >> >> >> On Wed, Sep 3, 2008 at 8:31 AM, Kai Grabfelder <[EMAIL PROTECTED]> wrote: >>> >>> Hi Reuben, >>> >>> regarding your first problem: Have you tried to give a comma separated >>> list >>> for the groupBy criteria? Just an idea, until now I was only grouping for >>> one element not multiple ones as it can get quite expensive to do n+1 >>> joins >>> on several tables on the database level. >>> >>> regarding your second problem: I've used subselects in such cases that >>> limit >>> the number record returned. This may be not the fastest solution for all >>> usescases but it works. In your case it could look like this: >>> >>> select * from book_table, other_join_tables where book_table.id = >>> other_join_tables.book_id and >>> book_table.id in (select id from book_table order by sort_crit desc limit >>> 100 offset 10) >>> >>> Regards >>> >>> Kai >>> >>> --- Original Nachricht --- >>> Absender: Clinton Begin >>> Datum: 03.09.2008 16:20 >>>> >>>> The second problem is a limitation that we cannot do anything about, >>>> which makes the rest of the conversation somewhat FYI only. >>>> >>>> The first problem does sound like a bug, but strangely I have unit >>>> tests confirming that this works. I'll try writing a few more to see >>>> if I can reproduce the problem. It very well might be the combination >>>> of keys being used in the groupBy attribute or something, but I'll >>>> check it out to see. >>>> >>>> Clinton >>>> >>>> On Wed, Sep 3, 2008 at 7:32 AM, Reuben Firmin <[EMAIL PROTECTED]> >>>> wrote: >>>>> >>>>> Anybody have any feedback on this? >>>>> >>>>> Thanks >>>>> Reuben >>>>> >>>>> ---------- Forwarded message ---------- >>>>> From: Reuben Firmin <[EMAIL PROTECTED]> >>>>> Date: Tue, Sep 2, 2008 at 11:26 AM >>>>> Subject: GroupBy issues (multiple child lists, Postgres limit/offset) >>>>> To: [email protected] >>>>> >>>>> >>>>> We are trying to resolve some N+1 query situations in our application, >>>>> and >>>>> are finding a couple of features of our appliation that seem to limit >>>>> our >>>>> ability to use the "groupBy" solution. I'm wondering if there are >>>>> aspects >>>>> of >>>>> the issues we aren't seeing. >>>>> >>>>> The problems are these: >>>>> 1. In places where we have an object structure that has a parent with >>>>> multiple child lists, it appears that we can't use groupBy to get all >>>>> of >>>>> the >>>>> results with one query. For example, >>>>> class Book { >>>>> ... >>>>> List<Author> authors; >>>>> List<Comment> comments; >>>>> List<Subject> subjects; >>>>> ... >>>>> For this type of situation, it seems like our choices are to (a) use >>>>> groupBy >>>>> for one of the child lists, and selects in the resultMap for the other >>>>> children (doesn't completely solve N+1 problem, just reduces it), or >>>>> (b) >>>>> using a cross-product join of all tables and a custom RowHandler to >>>>> manage >>>>> it all with one query. >>>>> >>>>> 2. We are using Postgresql, and taking advantage of the "limit" and >>>>> "offset" >>>>> keywords to help implement paging of the results we display - the >>>>> "limit" >>>>> and "offset" values correspond to the "Results (offset) - (offset + >>>>> limit) >>>>> of (n)" message we can display to users. It seems that these aren't >>>>> going >>>>> to >>>>> be compatible with a "groupBy" approach since "limit" and "offset" work >>>>> at >>>>> the resultSet level, and "groupBy" works by having a resultSet that's a >>>>> cross product of at least a couple of tables. That is, we want to rely >>>>> on >>>>> the limit and offset ability at the database level (makes queries and >>>>> resultset handling simpler), but the values refer to domain entities >>>>> and >>>>> not >>>>> resultset rows. We can use the keywords if we aren't worried about N+1 >>>>> selects, but the values will lose their domain entity meaning if we do >>>>> cross >>>>> product queries with groupBy. Is there any way that people have found >>>>> around >>>>> this? >>>>> >>>>> Thanks for any advice, >>>>> Reuben >>>>> >>>>> >>>> >>> >>> >> > >
