ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kai Grabfelder <nos...@kinokai.de>
Subject Re: GroupBy issues (multiple child lists, Postgres limit/offset)
Date Wed, 03 Sep 2008 16:04:03 GMT
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