You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Reuben Firmin <re...@benetech.org> on 2008/09/02 20:26:07 UTC
GroupBy issues (multiple child lists, Postgres limit/offset)
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
Re: GroupBy issues (multiple child lists, Postgres limit/offset)
Posted by Reuben Firmin <re...@benetech.org>.
Thanks guys, that's helpful.
On Wed, Sep 3, 2008 at 9:59 AM, Clinton Begin <cl...@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 <no...@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 <no...@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 <re...@benetech.org>
> >>>> wrote:
> >>>>>
> >>>>> Anybody have any feedback on this?
> >>>>>
> >>>>> Thanks
> >>>>> Reuben
> >>>>>
> >>>>> ---------- Forwarded message ----------
> >>>>> From: Reuben Firmin <re...@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
> >>>>>
> >>>>>
> >>>>
> >>>
> >>>
> >>
> >
> >
>
Re: GroupBy issues (multiple child lists, Postgres limit/offset)
Posted by Clinton Begin <cl...@gmail.com>.
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 <no...@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 <no...@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 <re...@benetech.org>
>>>> wrote:
>>>>>
>>>>> Anybody have any feedback on this?
>>>>>
>>>>> Thanks
>>>>> Reuben
>>>>>
>>>>> ---------- Forwarded message ----------
>>>>> From: Reuben Firmin <re...@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
>>>>>
>>>>>
>>>>
>>>
>>>
>>
>
>
Re: GroupBy issues (multiple child lists, Postgres limit/offset)
Posted by Kai Grabfelder <no...@kinokai.de>.
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 <no...@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 <re...@benetech.org>
>>> wrote:
>>>>
>>>> Anybody have any feedback on this?
>>>>
>>>> Thanks
>>>> Reuben
>>>>
>>>> ---------- Forwarded message ----------
>>>> From: Reuben Firmin <re...@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
>>>>
>>>>
>>>
>>
>>
>
Re: GroupBy issues (multiple child lists, Postgres limit/offset)
Posted by Clinton Begin <cl...@gmail.com>.
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 <no...@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 <re...@benetech.org>
>> wrote:
>>>
>>> Anybody have any feedback on this?
>>>
>>> Thanks
>>> Reuben
>>>
>>> ---------- Forwarded message ----------
>>> From: Reuben Firmin <re...@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
>>>
>>>
>>
>
>
Re: GroupBy issues (multiple child lists, Postgres limit/offset)
Posted by Kai Grabfelder <no...@kinokai.de>.
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 <re...@benetech.org> wrote:
>> Anybody have any feedback on this?
>>
>> Thanks
>> Reuben
>>
>> ---------- Forwarded message ----------
>> From: Reuben Firmin <re...@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
>>
>>
>
Re: GroupBy issues (multiple child lists, Postgres limit/offset)
Posted by Clinton Begin <cl...@gmail.com>.
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 <re...@benetech.org> wrote:
> Anybody have any feedback on this?
>
> Thanks
> Reuben
>
> ---------- Forwarded message ----------
> From: Reuben Firmin <re...@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
>
>
Fwd: GroupBy issues (multiple child lists, Postgres limit/offset)
Posted by Reuben Firmin <re...@benetech.org>.
Anybody have any feedback on this?
Thanks
Reuben
---------- Forwarded message ----------
From: Reuben Firmin <re...@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