You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-dev@db.apache.org by Jakob Braeuchi <jb...@gmx.ch> on 2003/11/16 21:01:14 UTC
Query startAt endAt
hi all,
armin and i are currently working on supporting sql pageing (aka LIMIT).
OJB provides it's own way of paging using startAt and endAt indices in
query. the problem i have now is the meaning of endAt.
ie: startAt = 2, endAt = 5 -> OJB retrieves _3_ object at 2, 3, 4
is this really the correct behaviour ? imo we should return _4_ objects
(2 through 5).
another issue is the way dbms implement the LIMIT parameters. most of
them use an _offset_ and a _number_ of rows to return. some of them only
the number of rows. should we change ojb to support offset/number or do
we stay with startAt/endAt ?
what do you think ?
jakob
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: Query startAt endAt
Posted by Jakob Bräuchi <jb...@gmx.ch>.
hi all,
i have to inform you, that paging by SQL is not yet an option :( because we
execute a select for each extent. but i think i found a way to improve
OJB-paging be it for collections or iterators.
as soon as time permits i will start working on the new solution.
jakob
> Hi Jakob
>
> <snip>
> >>
> >> Personally, I prefer LIMIT and OFFSET approach. Try to explain:
> >
> >
> > i also prefer the OFFSET and LIMIT.
>
> +1
>
> cu,
> Thomas
>
> > we do have a problem with dbms only
> > supporting OFFSET (oracle, sapdb etc.) there is a way to solve this
> > problem by using an enclosing query:
> >
> > select * from
> > (
> > select ... , rownum as ojb_rnum where .... order by ...
> > ) where ojb_rnum between OFFSET and OFFSET + LIMIT
> >
> >
> > jakob
> >
> >
> >>
> >> StartAt = OFFSET
> >> endAt = OFFSET + LIMIT
> >>
> >> But:
> >>
> >> "endAt" indicates that it need to end at a given index. But this index
> >> does not necesary exists. Suppose 5 row in a table and we use:
> >>
> >> startAt = 1, endAt = 10
> >>
> >> The "endAt" index never will be reached. For me the endAt in this case
> >> does not exactly describe what we are requesting.
> >>
> >> On the other hand:
> >>
> >> startAt = 1, limit = 10 => This is correct, it means we will start at 1
> >> and we will retrieve at max 10 rows. But there is not an implicit
> >> index of
> >> the last one. Also using limit= 10 avoid us to doing some innecesary
> >> "maths". We cleary express the max. amount of rows we expect to
> >> recieve in
> >> a very easy way. It is easier from a programming view. You will not
> need
> >> to sum the 2 values to get the endAt value.
> >>
> >> I just tried to explain my view point. Of course, comments are
> >> welcome. :-D
> >>
> >> Best Regards,
> >>
> >> Antonio Gallardo
> >>
> >> ---------------------------------------------------------------------
> >> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> >> For additional commands, e-mail: ojb-dev-help@db.apache.org
> >>
> >>
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> > For additional commands, e-mail: ojb-dev-help@db.apache.org
> >
> >
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-dev-help@db.apache.org
>
--
GMX Weihnachts-Special: Seychellen-Traumreise zu gewinnen!
Rentier entlaufen. Finden Sie Rudolph! Als Belohnung winken
tolle Preise. http://www.gmx.net/de/cgi/specialmail/
+++ GMX - die erste Adresse f�r Mail, Message, More! +++
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: Query startAt endAt
Posted by Thomas Mahler <th...@web.de>.
Hi Jakob
<snip>
>>
>> Personally, I prefer LIMIT and OFFSET approach. Try to explain:
>
>
> i also prefer the OFFSET and LIMIT.
+1
cu,
Thomas
> we do have a problem with dbms only
> supporting OFFSET (oracle, sapdb etc.) there is a way to solve this
> problem by using an enclosing query:
>
> select * from
> (
> select ... , rownum as ojb_rnum where .... order by ...
> ) where ojb_rnum between OFFSET and OFFSET + LIMIT
>
>
> jakob
>
>
>>
>> StartAt = OFFSET
>> endAt = OFFSET + LIMIT
>>
>> But:
>>
>> "endAt" indicates that it need to end at a given index. But this index
>> does not necesary exists. Suppose 5 row in a table and we use:
>>
>> startAt = 1, endAt = 10
>>
>> The "endAt" index never will be reached. For me the endAt in this case
>> does not exactly describe what we are requesting.
>>
>> On the other hand:
>>
>> startAt = 1, limit = 10 => This is correct, it means we will start at 1
>> and we will retrieve at max 10 rows. But there is not an implicit
>> index of
>> the last one. Also using limit= 10 avoid us to doing some innecesary
>> "maths". We cleary express the max. amount of rows we expect to
>> recieve in
>> a very easy way. It is easier from a programming view. You will not need
>> to sum the 2 values to get the endAt value.
>>
>> I just tried to explain my view point. Of course, comments are
>> welcome. :-D
>>
>> Best Regards,
>>
>> Antonio Gallardo
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-dev-help@db.apache.org
>>
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-dev-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: Query startAt endAt
Posted by Antonio Gallardo <ag...@agsoftware.dnsalias.com>.
Jakob Braeuchi dijo:
> i also prefer the OFFSET and LIMIT. we do have a problem with dbms only
> supporting OFFSET (oracle, sapdb etc.) there is a way to solve this
> problem by using an enclosing query:
>
> select * from
> (
> select ... , rownum as ojb_rnum where .... order by ...
> ) where ojb_rnum between OFFSET and OFFSET + LIMIT
Great!
Best Regards,
Antonio Gallardo
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: Query startAt endAt
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi antonio,
Antonio Gallardo wrote:
> Jakob Braeuchi dijo:
>
>>hi all,
>>
>>armin and i are currently working on supporting sql pageing (aka LIMIT).
>> OJB provides it's own way of paging using startAt and endAt indices in
>>query. the problem i have now is the meaning of endAt.
>>
>>ie: startAt = 2, endAt = 5 -> OJB retrieves _3_ object at 2, 3, 4
>>
>>is this really the correct behaviour ? imo we should return _4_ objects
>>(2 through 5).
>
>
> Yep. You need to return 4 rows (5 included).
i will change getCollectionByQuery accordingly.
>
>
>>another issue is the way dbms implement the LIMIT parameters. most of
>>them use an _offset_ and a _number_ of rows to return. some of them only
>>the number of rows. should we change ojb to support offset/number or do
>>we stay with startAt/endAt ?
>
>
> Personally, I prefer LIMIT and OFFSET approach. Try to explain:
i also prefer the OFFSET and LIMIT. we do have a problem with dbms only
supporting OFFSET (oracle, sapdb etc.) there is a way to solve this
problem by using an enclosing query:
select * from
(
select ... , rownum as ojb_rnum where .... order by ...
) where ojb_rnum between OFFSET and OFFSET + LIMIT
jakob
>
> StartAt = OFFSET
> endAt = OFFSET + LIMIT
>
> But:
>
> "endAt" indicates that it need to end at a given index. But this index
> does not necesary exists. Suppose 5 row in a table and we use:
>
> startAt = 1, endAt = 10
>
> The "endAt" index never will be reached. For me the endAt in this case
> does not exactly describe what we are requesting.
>
> On the other hand:
>
> startAt = 1, limit = 10 => This is correct, it means we will start at 1
> and we will retrieve at max 10 rows. But there is not an implicit index of
> the last one. Also using limit= 10 avoid us to doing some innecesary
> "maths". We cleary express the max. amount of rows we expect to recieve in
> a very easy way. It is easier from a programming view. You will not need
> to sum the 2 values to get the endAt value.
>
> I just tried to explain my view point. Of course, comments are welcome. :-D
>
> Best Regards,
>
> Antonio Gallardo
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-dev-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: Query startAt endAt
Posted by Antonio Gallardo <ag...@agsoftware.dnsalias.com>.
Jakob Braeuchi dijo:
> hi all,
>
> armin and i are currently working on supporting sql pageing (aka LIMIT).
> OJB provides it's own way of paging using startAt and endAt indices in
> query. the problem i have now is the meaning of endAt.
>
> ie: startAt = 2, endAt = 5 -> OJB retrieves _3_ object at 2, 3, 4
>
> is this really the correct behaviour ? imo we should return _4_ objects
> (2 through 5).
Yep. You need to return 4 rows (5 included).
> another issue is the way dbms implement the LIMIT parameters. most of
> them use an _offset_ and a _number_ of rows to return. some of them only
> the number of rows. should we change ojb to support offset/number or do
> we stay with startAt/endAt ?
Personally, I prefer LIMIT and OFFSET approach. Try to explain:
StartAt = OFFSET
endAt = OFFSET + LIMIT
But:
"endAt" indicates that it need to end at a given index. But this index
does not necesary exists. Suppose 5 row in a table and we use:
startAt = 1, endAt = 10
The "endAt" index never will be reached. For me the endAt in this case
does not exactly describe what we are requesting.
On the other hand:
startAt = 1, limit = 10 => This is correct, it means we will start at 1
and we will retrieve at max 10 rows. But there is not an implicit index of
the last one. Also using limit= 10 avoid us to doing some innecesary
"maths". We cleary express the max. amount of rows we expect to recieve in
a very easy way. It is easier from a programming view. You will not need
to sum the 2 values to get the endAt value.
I just tried to explain my view point. Of course, comments are welcome. :-D
Best Regards,
Antonio Gallardo
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org