You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@sling.apache.org by Clemens Wyss - MySign AG <cl...@mysign.ch> on 2010/08/18 15:53:36 UTC

JCR-SQL2 : paging

how would you implement paging with JCR-SQL2? Im looking for MySQL's LIMIT equivalent. Or is there a "JCR pattern" for paging?

Thx in advance

Re: JCR-SQL2 : paging

Posted by Felix Meschberger <fm...@gmail.com>.
Hi,

On 30.09.2010 09:45, Clemens Wyss wrote:
> should I post this "issue" in the jackrabbit newsgroup?

This would probably make sense ...

Regards
Fleix

> 
>> -----Original Message-----
>> From: Clemens Wyss [mailto:clemensdev@mysign.ch]
>> Sent: Wednesday, September 29, 2010 11:27 AM
>> To: 'users@sling.apache.org'
>> Subject: RE: JCR-SQL2 : paging
>>
>>
>> sorry for re-activating this thread again.
>> I just noticed that adding "order by [jcr:score]" only works,
>> ie delivers the hit count, when there is no WHERE-clause.
>> E.g.:
>>   select * from [nt:unstructured] order by [jcr:score]
>> returns the hit count (query.execute().getRows().getSize()), whereas
>>   select * from [nt:unstructured] where entity = "customer"
>> order by [jcr:score]
>> doesn't. Bug, feature, (jackrabbit-)limitation?
>>
>>> -----Original Message-----
>>> From: Greg Klebus [mailto:gklebus@day.com]
>>> Sent: Tuesday, August 24, 2010 4:26 PM
>>> To: users@sling.apache.org
>>> Subject: Re: JCR-SQL2 : paging
>>>
>>>
>>> On Tue, Aug 24, 2010 at 3:55 PM, Clemens Wyss
>>> <cl...@mysign.ch> wrote:
>>>> @Mark: could you link me to the mentioned thread?
>>>
>>> Not sure about the thread. This is, however, a Jackrabbit
>> FAQ [1]. You
>>> can use "order by jcr:score" if you don't have more
>> specific ordering
>>> needs.
>>>
>>> [1]
>>> http://jackrabbit.apache.org/frequently-asked-questions.html#F
>> requentlyAskedQuestions-Whyisthesizeofmyqueryresult1%3F
>>
>> HTH
>> Greg
>>
>> --
>> Greg Klebus
>> Sr. Product Manager
>> mailto: greg.klebus@day.com
>> T +41 61 226 9898
>> F +41 61 226 9897
>>
>> Info-World 2009 Technology of the Year Award:  Best Web CMS
>> http://www.infoworld.com/slideshow/2009/01/194-2009_technology-4.html
>>
>> Day Software AG
>> Barfuesserplatz 6,  4001 Basel, Switzerland
>> web:  http://www.day.com/
>> twitter: @gklebus @daysoftware
>> ____________________________________________________
>> This message is a private communication. If you are not the intended
>> recipient, please do not read, copy or use it, and do not disclose to
>> others. Please notify the sender of the delivery error by replying to
>> this message, and then delete it from your system. Thank you.
>> The sender does not assume any liability for timely,
>> trouble-free, complete,
>> virus free, secure, error free or uninterrupted arrival of this email.
>> For verification please request a hard-copy version.
>> ____________________________________________________
>>
> 

RE: JCR-SQL2 : paging

Posted by Clemens Wyss <cl...@mysign.ch>.
should I post this "issue" in the jackrabbit newsgroup?

> -----Original Message-----
> From: Clemens Wyss [mailto:clemensdev@mysign.ch]
> Sent: Wednesday, September 29, 2010 11:27 AM
> To: 'users@sling.apache.org'
> Subject: RE: JCR-SQL2 : paging
>
>
> sorry for re-activating this thread again.
> I just noticed that adding "order by [jcr:score]" only works,
> ie delivers the hit count, when there is no WHERE-clause.
> E.g.:
>   select * from [nt:unstructured] order by [jcr:score]
> returns the hit count (query.execute().getRows().getSize()), whereas
>   select * from [nt:unstructured] where entity = "customer"
> order by [jcr:score]
> doesn't. Bug, feature, (jackrabbit-)limitation?
>
> > -----Original Message-----
> > From: Greg Klebus [mailto:gklebus@day.com]
> > Sent: Tuesday, August 24, 2010 4:26 PM
> > To: users@sling.apache.org
> > Subject: Re: JCR-SQL2 : paging
> >
> >
> > On Tue, Aug 24, 2010 at 3:55 PM, Clemens Wyss
> > <cl...@mysign.ch> wrote:
> > > @Mark: could you link me to the mentioned thread?
> >
> > Not sure about the thread. This is, however, a Jackrabbit
> FAQ [1]. You
> > can use "order by jcr:score" if you don't have more
> specific ordering
> > needs.
> >
> > [1]
> > http://jackrabbit.apache.org/frequently-asked-questions.html#F
> requentlyAskedQuestions-Whyisthesizeofmyqueryresult1%3F
>
> HTH
> Greg
>
> --
> Greg Klebus
> Sr. Product Manager
> mailto: greg.klebus@day.com
> T +41 61 226 9898
> F +41 61 226 9897
>
> Info-World 2009 Technology of the Year Award:  Best Web CMS
> http://www.infoworld.com/slideshow/2009/01/194-2009_technology-4.html
>
> Day Software AG
> Barfuesserplatz 6,  4001 Basel, Switzerland
> web:  http://www.day.com/
> twitter: @gklebus @daysoftware
> ____________________________________________________
> This message is a private communication. If you are not the intended
> recipient, please do not read, copy or use it, and do not disclose to
> others. Please notify the sender of the delivery error by replying to
> this message, and then delete it from your system. Thank you.
> The sender does not assume any liability for timely,
> trouble-free, complete,
> virus free, secure, error free or uninterrupted arrival of this email.
> For verification please request a hard-copy version.
> ____________________________________________________
>

RE: JCR-SQL2 : paging

Posted by Clemens Wyss <cl...@mysign.ch>.
sorry for re-activating this thread again.
I just noticed that adding "order by [jcr:score]" only works, ie delivers the hit count, when there is no WHERE-clause.
E.g.:
  select * from [nt:unstructured] order by [jcr:score]
returns the hit count (query.execute().getRows().getSize()), whereas
  select * from [nt:unstructured] where entity = "customer" order by [jcr:score]
doesn't. Bug, feature, (jackrabbit-)limitation?

> -----Original Message-----
> From: Greg Klebus [mailto:gklebus@day.com]
> Sent: Tuesday, August 24, 2010 4:26 PM
> To: users@sling.apache.org
> Subject: Re: JCR-SQL2 : paging
>
>
> On Tue, Aug 24, 2010 at 3:55 PM, Clemens Wyss
> <cl...@mysign.ch> wrote:
> > @Mark: could you link me to the mentioned thread?
>
> Not sure about the thread. This is, however, a Jackrabbit FAQ [1]. You
> can use "order by jcr:score" if you don't have more specific ordering
> needs.
>
> [1]
> http://jackrabbit.apache.org/frequently-asked-questions.html#F
requentlyAskedQuestions-Whyisthesizeofmyqueryresult1%3F

HTH
Greg

--
Greg Klebus
Sr. Product Manager
mailto: greg.klebus@day.com
T +41 61 226 9898
F +41 61 226 9897

Info-World 2009 Technology of the Year Award:  Best Web CMS
http://www.infoworld.com/slideshow/2009/01/194-2009_technology-4.html

Day Software AG
Barfuesserplatz 6,  4001 Basel, Switzerland
web:  http://www.day.com/
twitter: @gklebus @daysoftware
____________________________________________________
This message is a private communication. If you are not the intended
recipient, please do not read, copy or use it, and do not disclose to
others. Please notify the sender of the delivery error by replying to
this message, and then delete it from your system. Thank you.
The sender does not assume any liability for timely, trouble-free, complete,
virus free, secure, error free or uninterrupted arrival of this email.
For verification please request a hard-copy version.
____________________________________________________

Re: JCR-SQL2 : paging

Posted by Greg Klebus <gk...@day.com>.
On Tue, Aug 24, 2010 at 3:55 PM, Clemens Wyss <cl...@mysign.ch> wrote:
> @Mark: could you link me to the mentioned thread?

Not sure about the thread. This is, however, a Jackrabbit FAQ [1]. You
can use "order by jcr:score" if you don't have more specific ordering
needs.

[1] http://jackrabbit.apache.org/frequently-asked-questions.html#FrequentlyAskedQuestions-Whyisthesizeofmyqueryresult1%3F

HTH
Greg

-- 
Greg Klebus
Sr. Product Manager
mailto: greg.klebus@day.com
T +41 61 226 9898
F +41 61 226 9897

Info-World 2009 Technology of the Year Award:  Best Web CMS
http://www.infoworld.com/slideshow/2009/01/194-2009_technology-4.html

Day Software AG
Barfuesserplatz 6,  4001 Basel, Switzerland
web:  http://www.day.com/
twitter: @gklebus @daysoftware
____________________________________________________
This message is a private communication. If you are not the intended
recipient, please do not read, copy or use it, and do not disclose to
others. Please notify the sender of the delivery error by replying to
this message, and then delete it from your system. Thank you.
The sender does not assume any liability for timely, trouble-free, complete,
virus free, secure, error free or uninterrupted arrival of this email.
For verification please request a hard-copy version.
____________________________________________________

RE: JCR-SQL2 : paging

Posted by Clemens Wyss <cl...@mysign.ch>.
@Mark: could you link me to the mentioned thread?
Thx
Clemens

> -----Original Message-----
> From: Justin Edelson [mailto:justinedelson@gmail.com]
> Sent: Tuesday, August 24, 2010 3:50 PM
> To: users@sling.apache.org
> Subject: Re: JCR-SQL2 : paging
>
>
> On 8/24/10 9:47 AM, Clemens Wyss wrote:
> >> but I think that someone mentioned in a
> >> thread last week
> > that was not "here", right?
> probably jackrabbit-users.
>
> >
> >> -----Original Message-----
> >> From: Mark Adamcin [mailto:adamcin@gmail.com]
> >> Sent: Tuesday, August 24, 2010 3:37 PM
> >> To: users@sling.apache.org
> >> Subject: Re: JCR-SQL2 : paging
> >>
> >>
> >> Clemens,
> >>
> >> I haven't tried it yet myself, but I think that someone
> mentioned in a
> >> thread last week that you can have a query compute the number
> >> of rows by
> >> ordering by jcr:score. In jcr-sql2, that probably requires
> >> you to include
> >> jcr:score as a column.
> >>
> >> Mark Adamcin
> >> Acquity Group
> >>
> >>
> >>
> >> On Tue, Aug 24, 2010 at 1:17 AM, Clemens Wyss
> >> <cl...@mysign.ch> wrote:
> >>
> >>> Just to clarify I didn't miss anything in the API doc:
> >>> If I want to provide a paging navigation with a "link per
> >> page" and/or a
> >>> "go to last page" link, then I need to execute the overall
> >> query (in order
> >>> to get the total amount of rows/pages (*)) and then
> >> re-execute the query for
> >>> the current page.
> >>>
> >>> (*) even worse:
> >>> as the RowIterator does not provide the number of rows (at
> >> least not for
> >>> JCR-SQL2), I have to iterate over all rows just to get the
> >> total number of
> >>> rows. Yes, SQL has COUNT but xpath doesn't (
> >>> https://issues.apache.org/jira/browse/JCR-260), so there is
> >> no generic
> >>> count for the various query languages...
> >>>
> >>>> -----Original Message-----
> >>>> From: Clemens Wyss - MySign AG [mailto:clemens.wyss@mysign.ch]
> >>>> Sent: Wednesday, August 18, 2010 4:05 PM
> >>>> To: 'users@sling.apache.org'
> >>>> Subject: RE: JCR-SQL2 : paging
> >>>>
> >>>>
> >>>>> Or is there a "JCR pattern" for paging?
> >>>> Query.setLimit and Query.setOffset seem to provide the
> >>>> generic (search language independent) approach for paging.
> >>>>
> >>>> Sorry for the noise...
> >>>> Clemens
> >>>>
> >>>>> -----Original Message-----
> >>>>> From: Clemens Wyss - MySign AG
> >>>>> Sent: Wednesday, August 18, 2010 3:54 PM
> >>>>> To: 'users@sling.apache.org'
> >>>>> Subject: JCR-SQL2 : paging
> >>>>>
> >>>>>
> >>>>> how would you implement paging with JCR-SQL2? Im looking for
> >>>>> MySQL's LIMIT equivalent. Or is there a "JCR pattern"
> >> for paging?
> >>>>>
> >>>>> Thx in advance
> >>>>>
> >>>>
> >>>
> >>
>
>

Re: JCR-SQL2 : paging

Posted by Justin Edelson <ju...@gmail.com>.
On 8/24/10 9:47 AM, Clemens Wyss wrote:
>> but I think that someone mentioned in a
>> thread last week
> that was not "here", right?
probably jackrabbit-users.

> 
>> -----Original Message-----
>> From: Mark Adamcin [mailto:adamcin@gmail.com]
>> Sent: Tuesday, August 24, 2010 3:37 PM
>> To: users@sling.apache.org
>> Subject: Re: JCR-SQL2 : paging
>>
>>
>> Clemens,
>>
>> I haven't tried it yet myself, but I think that someone mentioned in a
>> thread last week that you can have a query compute the number
>> of rows by
>> ordering by jcr:score. In jcr-sql2, that probably requires
>> you to include
>> jcr:score as a column.
>>
>> Mark Adamcin
>> Acquity Group
>>
>>
>>
>> On Tue, Aug 24, 2010 at 1:17 AM, Clemens Wyss
>> <cl...@mysign.ch> wrote:
>>
>>> Just to clarify I didn't miss anything in the API doc:
>>> If I want to provide a paging navigation with a "link per
>> page" and/or a
>>> "go to last page" link, then I need to execute the overall
>> query (in order
>>> to get the total amount of rows/pages (*)) and then
>> re-execute the query for
>>> the current page.
>>>
>>> (*) even worse:
>>> as the RowIterator does not provide the number of rows (at
>> least not for
>>> JCR-SQL2), I have to iterate over all rows just to get the
>> total number of
>>> rows. Yes, SQL has COUNT but xpath doesn't (
>>> https://issues.apache.org/jira/browse/JCR-260), so there is
>> no generic
>>> count for the various query languages...
>>>
>>>> -----Original Message-----
>>>> From: Clemens Wyss - MySign AG [mailto:clemens.wyss@mysign.ch]
>>>> Sent: Wednesday, August 18, 2010 4:05 PM
>>>> To: 'users@sling.apache.org'
>>>> Subject: RE: JCR-SQL2 : paging
>>>>
>>>>
>>>>> Or is there a "JCR pattern" for paging?
>>>> Query.setLimit and Query.setOffset seem to provide the
>>>> generic (search language independent) approach for paging.
>>>>
>>>> Sorry for the noise...
>>>> Clemens
>>>>
>>>>> -----Original Message-----
>>>>> From: Clemens Wyss - MySign AG
>>>>> Sent: Wednesday, August 18, 2010 3:54 PM
>>>>> To: 'users@sling.apache.org'
>>>>> Subject: JCR-SQL2 : paging
>>>>>
>>>>>
>>>>> how would you implement paging with JCR-SQL2? Im looking for
>>>>> MySQL's LIMIT equivalent. Or is there a "JCR pattern"
>> for paging?
>>>>>
>>>>> Thx in advance
>>>>>
>>>>
>>>
>>


RE: JCR-SQL2 : paging

Posted by Clemens Wyss <cl...@mysign.ch>.
>but I think that someone mentioned in a
>thread last week
that was not "here", right?

> -----Original Message-----
> From: Mark Adamcin [mailto:adamcin@gmail.com]
> Sent: Tuesday, August 24, 2010 3:37 PM
> To: users@sling.apache.org
> Subject: Re: JCR-SQL2 : paging
>
>
> Clemens,
>
> I haven't tried it yet myself, but I think that someone mentioned in a
> thread last week that you can have a query compute the number
> of rows by
> ordering by jcr:score. In jcr-sql2, that probably requires
> you to include
> jcr:score as a column.
>
> Mark Adamcin
> Acquity Group
>
>
>
> On Tue, Aug 24, 2010 at 1:17 AM, Clemens Wyss
> <cl...@mysign.ch> wrote:
>
> > Just to clarify I didn't miss anything in the API doc:
> > If I want to provide a paging navigation with a "link per
> page" and/or a
> > "go to last page" link, then I need to execute the overall
> query (in order
> > to get the total amount of rows/pages (*)) and then
> re-execute the query for
> > the current page.
> >
> > (*) even worse:
> > as the RowIterator does not provide the number of rows (at
> least not for
> > JCR-SQL2), I have to iterate over all rows just to get the
> total number of
> > rows. Yes, SQL has COUNT but xpath doesn't (
> > https://issues.apache.org/jira/browse/JCR-260), so there is
> no generic
> > count for the various query languages...
> >
> > > -----Original Message-----
> > > From: Clemens Wyss - MySign AG [mailto:clemens.wyss@mysign.ch]
> > > Sent: Wednesday, August 18, 2010 4:05 PM
> > > To: 'users@sling.apache.org'
> > > Subject: RE: JCR-SQL2 : paging
> > >
> > >
> > > >Or is there a "JCR pattern" for paging?
> > > Query.setLimit and Query.setOffset seem to provide the
> > > generic (search language independent) approach for paging.
> > >
> > > Sorry for the noise...
> > > Clemens
> > >
> > > > -----Original Message-----
> > > > From: Clemens Wyss - MySign AG
> > > > Sent: Wednesday, August 18, 2010 3:54 PM
> > > > To: 'users@sling.apache.org'
> > > > Subject: JCR-SQL2 : paging
> > > >
> > > >
> > > > how would you implement paging with JCR-SQL2? Im looking for
> > > > MySQL's LIMIT equivalent. Or is there a "JCR pattern"
> for paging?
> > > >
> > > > Thx in advance
> > > >
> > >
> >
>

Re: JCR-SQL2 : paging

Posted by Mark Adamcin <ad...@gmail.com>.
Clemens,

I haven't tried it yet myself, but I think that someone mentioned in a
thread last week that you can have a query compute the number of rows by
ordering by jcr:score. In jcr-sql2, that probably requires you to include
jcr:score as a column.

Mark Adamcin
Acquity Group



On Tue, Aug 24, 2010 at 1:17 AM, Clemens Wyss <cl...@mysign.ch> wrote:

> Just to clarify I didn't miss anything in the API doc:
> If I want to provide a paging navigation with a "link per page" and/or a
> "go to last page" link, then I need to execute the overall query (in order
> to get the total amount of rows/pages (*)) and then re-execute the query for
> the current page.
>
> (*) even worse:
> as the RowIterator does not provide the number of rows (at least not for
> JCR-SQL2), I have to iterate over all rows just to get the total number of
> rows. Yes, SQL has COUNT but xpath doesn't (
> https://issues.apache.org/jira/browse/JCR-260), so there is no generic
> count for the various query languages...
>
> > -----Original Message-----
> > From: Clemens Wyss - MySign AG [mailto:clemens.wyss@mysign.ch]
> > Sent: Wednesday, August 18, 2010 4:05 PM
> > To: 'users@sling.apache.org'
> > Subject: RE: JCR-SQL2 : paging
> >
> >
> > >Or is there a "JCR pattern" for paging?
> > Query.setLimit and Query.setOffset seem to provide the
> > generic (search language independent) approach for paging.
> >
> > Sorry for the noise...
> > Clemens
> >
> > > -----Original Message-----
> > > From: Clemens Wyss - MySign AG
> > > Sent: Wednesday, August 18, 2010 3:54 PM
> > > To: 'users@sling.apache.org'
> > > Subject: JCR-SQL2 : paging
> > >
> > >
> > > how would you implement paging with JCR-SQL2? Im looking for
> > > MySQL's LIMIT equivalent. Or is there a "JCR pattern" for paging?
> > >
> > > Thx in advance
> > >
> >
>

RE: JCR-SQL2 : paging

Posted by Clemens Wyss <cl...@mysign.ch>.
Just to clarify I didn't miss anything in the API doc:
If I want to provide a paging navigation with a "link per page" and/or a "go to last page" link, then I need to execute the overall query (in order to get the total amount of rows/pages (*)) and then re-execute the query for the current page.

(*) even worse:
as the RowIterator does not provide the number of rows (at least not for JCR-SQL2), I have to iterate over all rows just to get the total number of rows. Yes, SQL has COUNT but xpath doesn't (https://issues.apache.org/jira/browse/JCR-260), so there is no generic count for the various query languages...

> -----Original Message-----
> From: Clemens Wyss - MySign AG [mailto:clemens.wyss@mysign.ch]
> Sent: Wednesday, August 18, 2010 4:05 PM
> To: 'users@sling.apache.org'
> Subject: RE: JCR-SQL2 : paging
>
>
> >Or is there a "JCR pattern" for paging?
> Query.setLimit and Query.setOffset seem to provide the
> generic (search language independent) approach for paging.
>
> Sorry for the noise...
> Clemens
>
> > -----Original Message-----
> > From: Clemens Wyss - MySign AG
> > Sent: Wednesday, August 18, 2010 3:54 PM
> > To: 'users@sling.apache.org'
> > Subject: JCR-SQL2 : paging
> >
> >
> > how would you implement paging with JCR-SQL2? Im looking for
> > MySQL's LIMIT equivalent. Or is there a "JCR pattern" for paging?
> >
> > Thx in advance
> >
>

RE: JCR-SQL2 : paging

Posted by Clemens Wyss - MySign AG <cl...@mysign.ch>.
>Or is there a "JCR pattern" for paging?
Query.setLimit and Query.setOffset seem to provide the generic (search language independent) approach for paging.

Sorry for the noise...
Clemens

> -----Original Message-----
> From: Clemens Wyss - MySign AG
> Sent: Wednesday, August 18, 2010 3:54 PM
> To: 'users@sling.apache.org'
> Subject: JCR-SQL2 : paging
>
>
> how would you implement paging with JCR-SQL2? Im looking for
> MySQL's LIMIT equivalent. Or is there a "JCR pattern" for paging?
>
> Thx in advance
>