ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Clinton Begin" <clinton.be...@gmail.com>
Subject Re: GroupBy issues (multiple child lists, Postgres limit/offset)
Date Wed, 03 Sep 2008 16:59:36 GMT
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