You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@jackrabbit.apache.org by Torgeir Veimo <to...@gmail.com> on 2015/01/13 05:21:32 UTC

'order by' descendant property with multiple descendant

I'm in need of ordering some node in a query based on the latest
timestamp on one or more child nodes.

This is similar to an article with comments as child nodes, where one
would like to order the articles in a query sorted by the last
timestamp on the comments to each article.

This doesn't seem to be work using an XPATH query, with a child node
designator as eg */@createdProperty. IS this possible to do using
either SQL2 or by using the QOM?

-- 
-Tor

Re: 'order by' descendant property with multiple descendant

Posted by Thomas Mueller <mu...@adobe.com>.
Hi,

>Yes the parent node, there might be multiple child nodes, so the one
>with the latest timestamp would take precedent.

OK I see. So for relational databases, the query would be something like
this:

drop all objects;
create table parent(id int);
insert into parent values(1);
create table child(id int, pid int, d date);
insert into child values(10, 1, '2001-01-01');
insert into child values(11, 1, '2001-01-02');
select * from parent p order by (select max(d) from child c where c.pid =
p.id);


Unfortunately, the neither JCR API nor Oak (nor Jackrabbit 2.x) support
subqueries and aggregate functions ("max"). Derived tables and group by is
also not supported, so I think there is no way to express this as a JCR
query.

Would it be possible to store the 'latest timestamp' in the parent? That
would simplify the query a lot.

Regards,
Thomas



Re: 'order by' descendant property with multiple descendant

Posted by Torgeir Veimo <to...@gmail.com>.
Yes the parent node, there might be multiple child nodes, so the one
with the latest timestamp would take precedent.

One example is a discussion forum, where you have the original message
in a thread as the parent, and the replies as children. The threads
should be sorted by the thread with the latest reply at the top.

On 13 January 2015 at 19:03, Thomas Mueller <mu...@adobe.com> wrote:
> Hi,
>
> I don't quite understand. The query result should be the parent node, and
> those should be ordered by the timestamp on which child node? There could
> be multiple child nodes per result node, right?
>
> Regards,
> Thomas
>
>
>
> On 13/01/15 05:21, "Torgeir Veimo" <to...@gmail.com> wrote:
>
>>I'm in need of ordering some node in a query based on the latest
>>timestamp on one or more child nodes.
>>
>>This is similar to an article with comments as child nodes, where one
>>would like to order the articles in a query sorted by the last
>>timestamp on the comments to each article.
>>
>>This doesn't seem to be work using an XPATH query, with a child node
>>designator as eg */@createdProperty. IS this possible to do using
>>either SQL2 or by using the QOM?
>>
>>--
>>-Tor
>



-- 
-Tor

Re: 'order by' descendant property with multiple descendant

Posted by Thomas Mueller <mu...@adobe.com>.
Hi,

I don't quite understand. The query result should be the parent node, and
those should be ordered by the timestamp on which child node? There could
be multiple child nodes per result node, right?

Regards,
Thomas



On 13/01/15 05:21, "Torgeir Veimo" <to...@gmail.com> wrote:

>I'm in need of ordering some node in a query based on the latest
>timestamp on one or more child nodes.
>
>This is similar to an article with comments as child nodes, where one
>would like to order the articles in a query sorted by the last
>timestamp on the comments to each article.
>
>This doesn't seem to be work using an XPATH query, with a child node
>designator as eg */@createdProperty. IS this possible to do using
>either SQL2 or by using the QOM?
>
>-- 
>-Tor


Re: 'order by' descendant property with multiple descendant

Posted by Davide Giannella <da...@apache.org>.
On 13/01/2015 05:21, Torgeir Veimo wrote:
> I'm in need of ordering some node in a query based on the latest
> timestamp on one or more child nodes.
>
> This is similar to an article with comments as child nodes, where one
> would like to order the articles in a query sorted by the last
> timestamp on the comments to each article.
>
> This doesn't seem to be work using an XPATH query, with a child node
> designator as eg */@createdProperty. IS this possible to do using
> either SQL2 or by using the QOM?
>
Don't know off the top of my head the actual details but probably you
can achieve it with inner joins.

http://www.day.com/maven/jcr/2.0/6_Query.html#6.7.5%20Join

HTH
Davide