Thanks guys, that's helpful. On Wed, Sep 3, 2008 at 9:59 AM, Clinton Begin <[EMAIL PROTECTED]>wrote:
> 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 > >>>>> > >>>>> > >>>> > >>> > >>> > >> > > > > >
