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 14:31:09 GMT
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
book_table.id in (select id from book_table order by sort_crit desc limit 100 offset 10)



--- 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

View raw message