ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Reuben Firmin" <reub...@benetech.org>
Subject Re: GroupBy issues (multiple child lists, Postgres limit/offset)
Date Wed, 03 Sep 2008 17:52:31 GMT
Thanks guys, that's helpful.

On Wed, Sep 3, 2008 at 9:59 AM, Clinton Begin <clinton.begin@gmail.com>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 <nospam@kinokai.de> 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 <nospam@kinokai.de>
> 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 <reubenf@benetech.org>
> >>>> wrote:
> >>>>>
> >>>>> Anybody have any feedback on this?
> >>>>>
> >>>>> Thanks
> >>>>> Reuben
> >>>>>
> >>>>> ---------- Forwarded message ----------
> >>>>> From: Reuben Firmin <reubenf@benetech.org>
> >>>>> Date: Tue, Sep 2, 2008 at 11:26 AM
> >>>>> Subject: GroupBy issues (multiple child lists, Postgres limit/offset)
> >>>>> To: user-java@ibatis.apache.org
> >>>>>
> >>>>>
> >>>>> 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
> >>>>>
> >>>>>
> >>>>
> >>>
> >>>
> >>
> >
> >
>

Mime
View raw message