ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Reuben Firmin" <reub...@benetech.org>
Subject Fwd: GroupBy issues (multiple child lists, Postgres limit/offset)
Date Wed, 03 Sep 2008 13:32:04 GMT
Anybody have any feedback on this?


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

Thanks for any advice,

View raw message