You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@jackrabbit.apache.org by Benjamin Papez <ja...@gmail.com> on 2009/10/21 10:01:54 UTC
SQL-2 Child Axis in order by clause
Hello,
I am using Jackrabbit 2.0 alpha10 and I am testing the following SQL-2
query:
select press.* from [web_templates:pressContainer] as press inner join
[jnt:translation] as translation on ischildnode(translation, press) where
contains(press.*, '${searchTerm}') or contains(translation.*,
'${searchTerm}') order by translation.[jcr:title_en] desc
but the ordering does not work. Debugging the SimpleScoreDocComparator I see
that the values for comparing are null all the time. Looking at
*JCR-800<http://issues.apache.org/jira/browse/JCR-800>
*I assume that RelPathScoreDocComparator should be used instead. Does this
already work with SQL-2 queries or do I have to change the query in order to
make it work ?
Regards,
Benjamin
Re: SQL-2 Child Axis in order by clause
Posted by Benjamin Papez <ja...@gmail.com>.
Hello Thomas,
thanks again, but thats exactly what I was talking about in my comment.
Please read it carefully.
I already wrote why " order by translation.[jcr:title_en] desc " does not
work.
Then I explained why I tried using a relative path and that this seems not
to be a valid SQL-2 syntax.
And at last I am talking about a possible patch, so I would be happy if you
could answer to my questions in my previous comment.
Regards,
Benjamin
2009/11/4 Thomas Müller <th...@day.com>
> Hi,
>
> You wrote:
> order by press.[jnt:translation/jcr:title_en] desc
>
> This is not valid SQL-2 syntax. What about:
> order by translation.[jcr:title_en] desc
>
> Regards,
> Thomas
>
>
> On Wed, Nov 4, 2009 at 1:41 PM, Benjamin Papez <ja...@gmail.com>
> wrote:
> > Hello,
> >
> > my wanted query is like this, which seems to be a valid query from
> > specification point of view:
> >
> > select press.* from [web_templates:pressContainer] as press inner join
> > [jnt:translation] as translation on ischildnode(translation, press) where
> > contains(press.*, '${searchTerm}') or contains(translation.*,
> > '${searchTerm}') order by translation.[jcr:title_en] desc
> >
> > unfortunately it does not work, because it looks like
> > SimpleScoreDocComparator is looking in Lucene documents representing
> > pressContainer and not translation (child node).
> >
> > I saw that Jackrabbit implemented a RelPathScoreDocComparator for use
> cases
> > like mine, but that comparator implementation is not picked with queries
> > like the one above. Like Marcel mentioned it is only used when there is a
> > relative path in the order by statement.
> >
> > Now according to JSR-283 specifications it looks like relative path is
> not
> > allowed in the order by statement. That is also why I get the following
> > error, trying it:
> >
> > 2009-11-04 12:42:08,750: ERROR [JCRSQLTag] - InvalidQueryException --->
> > [select press.* from [web_templates:pressContainer] as press inner join
> > [jnt:translation] as translation on ischildnode(translation, press) inner
> > join [nt:file] as file on translation.pdfVersion_en = file.[jcr:uuid]
> inner
> > join [nt:resource] as filecontent on ischildnode(filecontent, file) where
> > contains(filecontent.*, 'Europe') order by
> > press.[jnt:translation/jcr:title_en] desc] is not valid.
> > javax.jcr.query.InvalidQueryException: '/' not allowed in name
> > at
> >
> org.apache.jackrabbit.spi.commons.query.qom.QueryObjectModelFactoryImpl.checkPropertyName(QueryObjectModelFactoryImpl.java:1032)
> > at
> >
> org.apache.jackrabbit.spi.commons.query.qom.QueryObjectModelFactoryImpl.propertyValue(QueryObjectModelFactoryImpl.java:668)
> > at
> >
> org.apache.jackrabbit.spi.commons.query.sql2.Parser.parsePropertyValue(Parser.java:425)
> > at
> >
> org.apache.jackrabbit.spi.commons.query.sql2.Parser.parseDynamicOperand(Parser.java:386)
> > at
> >
> org.apache.jackrabbit.spi.commons.query.sql2.Parser.parseOrder(Parser.java:542)
> > at
> >
> org.apache.jackrabbit.spi.commons.query.sql2.Parser.createQueryObjectModel(Parser.java:121)
> > at
> >
> org.apache.jackrabbit.spi.commons.query.sql2.SQL2QOMBuilder.createQueryObjectModel(SQL2QOMBuilder.java:55)
> > at
> >
> org.apache.jackrabbit.core.query.QOMQueryFactory.createQuery(QOMQueryFactory.java:69)
> > at
> >
> org.apache.jackrabbit.core.query.CompoundQueryFactory.createQuery(CompoundQueryFactory.java:67)
> > at
> >
> org.apache.jackrabbit.core.query.QueryManagerImpl.createQuery(QueryManagerImpl.java:98)
> >
> >
> > Is there another way to achieve my use case?
> > Or is there none so far and should I open a JIRA to once make my original
> > query work, which seems to be a valid SQL-2 query by specification ?
> Perhaps
> > a patch could check in the JoinQuery for the child node join condition
> and
> > internally when creating the lucene orderings create the relative path if
> > the ordering is on the child node ? Or is there a better place to fix
> that ?
> >
> > Regards,
> > Benjamin
> >
> >
> > 2009/10/28 Thomas Müller <th...@day.com>
> >
> >> Hi,
> >>
> >> > I have tried to use a relative path, but I was getting
> >> > syntax errors in SQL-2.
> >>
> >> Could you tell me the exact statement you have used and the exception
> >> message and stack trace?
> >>
> >> Regards,
> >> Thomas
> >>
> >
>
Re: SQL-2 Child Axis in order by clause
Posted by Thomas Müller <th...@day.com>.
Hi,
You wrote:
order by press.[jnt:translation/jcr:title_en] desc
This is not valid SQL-2 syntax. What about:
order by translation.[jcr:title_en] desc
Regards,
Thomas
On Wed, Nov 4, 2009 at 1:41 PM, Benjamin Papez <ja...@gmail.com> wrote:
> Hello,
>
> my wanted query is like this, which seems to be a valid query from
> specification point of view:
>
> select press.* from [web_templates:pressContainer] as press inner join
> [jnt:translation] as translation on ischildnode(translation, press) where
> contains(press.*, '${searchTerm}') or contains(translation.*,
> '${searchTerm}') order by translation.[jcr:title_en] desc
>
> unfortunately it does not work, because it looks like
> SimpleScoreDocComparator is looking in Lucene documents representing
> pressContainer and not translation (child node).
>
> I saw that Jackrabbit implemented a RelPathScoreDocComparator for use cases
> like mine, but that comparator implementation is not picked with queries
> like the one above. Like Marcel mentioned it is only used when there is a
> relative path in the order by statement.
>
> Now according to JSR-283 specifications it looks like relative path is not
> allowed in the order by statement. That is also why I get the following
> error, trying it:
>
> 2009-11-04 12:42:08,750: ERROR [JCRSQLTag] - InvalidQueryException --->
> [select press.* from [web_templates:pressContainer] as press inner join
> [jnt:translation] as translation on ischildnode(translation, press) inner
> join [nt:file] as file on translation.pdfVersion_en = file.[jcr:uuid] inner
> join [nt:resource] as filecontent on ischildnode(filecontent, file) where
> contains(filecontent.*, 'Europe') order by
> press.[jnt:translation/jcr:title_en] desc] is not valid.
> javax.jcr.query.InvalidQueryException: '/' not allowed in name
> at
> org.apache.jackrabbit.spi.commons.query.qom.QueryObjectModelFactoryImpl.checkPropertyName(QueryObjectModelFactoryImpl.java:1032)
> at
> org.apache.jackrabbit.spi.commons.query.qom.QueryObjectModelFactoryImpl.propertyValue(QueryObjectModelFactoryImpl.java:668)
> at
> org.apache.jackrabbit.spi.commons.query.sql2.Parser.parsePropertyValue(Parser.java:425)
> at
> org.apache.jackrabbit.spi.commons.query.sql2.Parser.parseDynamicOperand(Parser.java:386)
> at
> org.apache.jackrabbit.spi.commons.query.sql2.Parser.parseOrder(Parser.java:542)
> at
> org.apache.jackrabbit.spi.commons.query.sql2.Parser.createQueryObjectModel(Parser.java:121)
> at
> org.apache.jackrabbit.spi.commons.query.sql2.SQL2QOMBuilder.createQueryObjectModel(SQL2QOMBuilder.java:55)
> at
> org.apache.jackrabbit.core.query.QOMQueryFactory.createQuery(QOMQueryFactory.java:69)
> at
> org.apache.jackrabbit.core.query.CompoundQueryFactory.createQuery(CompoundQueryFactory.java:67)
> at
> org.apache.jackrabbit.core.query.QueryManagerImpl.createQuery(QueryManagerImpl.java:98)
>
>
> Is there another way to achieve my use case?
> Or is there none so far and should I open a JIRA to once make my original
> query work, which seems to be a valid SQL-2 query by specification ? Perhaps
> a patch could check in the JoinQuery for the child node join condition and
> internally when creating the lucene orderings create the relative path if
> the ordering is on the child node ? Or is there a better place to fix that ?
>
> Regards,
> Benjamin
>
>
> 2009/10/28 Thomas Müller <th...@day.com>
>
>> Hi,
>>
>> > I have tried to use a relative path, but I was getting
>> > syntax errors in SQL-2.
>>
>> Could you tell me the exact statement you have used and the exception
>> message and stack trace?
>>
>> Regards,
>> Thomas
>>
>
Re: SQL-2 Child Axis in order by clause
Posted by Marcel Reutegger <ma...@gmx.net>.
Hi,
On Wed, Nov 4, 2009 at 13:41, Benjamin Papez <ja...@gmail.com> wrote:
> Hello,
>
> my wanted query is like this, which seems to be a valid query from
> specification point of view:
>
> select press.* from [web_templates:pressContainer] as press inner join
> [jnt:translation] as translation on ischildnode(translation, press) where
> contains(press.*, '${searchTerm}') or contains(translation.*,
> '${searchTerm}') order by translation.[jcr:title_en] desc
>
> unfortunately it does not work, because it looks like
> SimpleScoreDocComparator is looking in Lucene documents representing
> pressContainer and not translation (child node).
that sounds like a bug. could you please file a jira issue? thanks.
regards
marcel
> I saw that Jackrabbit implemented a RelPathScoreDocComparator for use cases
> like mine, but that comparator implementation is not picked with queries
> like the one above. Like Marcel mentioned it is only used when there is a
> relative path in the order by statement.
>
> Now according to JSR-283 specifications it looks like relative path is not
> allowed in the order by statement. That is also why I get the following
> error, trying it:
>
> 2009-11-04 12:42:08,750: ERROR [JCRSQLTag] - InvalidQueryException --->
> [select press.* from [web_templates:pressContainer] as press inner join
> [jnt:translation] as translation on ischildnode(translation, press) inner
> join [nt:file] as file on translation.pdfVersion_en = file.[jcr:uuid] inner
> join [nt:resource] as filecontent on ischildnode(filecontent, file) where
> contains(filecontent.*, 'Europe') order by
> press.[jnt:translation/jcr:title_en] desc] is not valid.
> javax.jcr.query.InvalidQueryException: '/' not allowed in name
> at
> org.apache.jackrabbit.spi.commons.query.qom.QueryObjectModelFactoryImpl.checkPropertyName(QueryObjectModelFactoryImpl.java:1032)
> at
> org.apache.jackrabbit.spi.commons.query.qom.QueryObjectModelFactoryImpl.propertyValue(QueryObjectModelFactoryImpl.java:668)
> at
> org.apache.jackrabbit.spi.commons.query.sql2.Parser.parsePropertyValue(Parser.java:425)
> at
> org.apache.jackrabbit.spi.commons.query.sql2.Parser.parseDynamicOperand(Parser.java:386)
> at
> org.apache.jackrabbit.spi.commons.query.sql2.Parser.parseOrder(Parser.java:542)
> at
> org.apache.jackrabbit.spi.commons.query.sql2.Parser.createQueryObjectModel(Parser.java:121)
> at
> org.apache.jackrabbit.spi.commons.query.sql2.SQL2QOMBuilder.createQueryObjectModel(SQL2QOMBuilder.java:55)
> at
> org.apache.jackrabbit.core.query.QOMQueryFactory.createQuery(QOMQueryFactory.java:69)
> at
> org.apache.jackrabbit.core.query.CompoundQueryFactory.createQuery(CompoundQueryFactory.java:67)
> at
> org.apache.jackrabbit.core.query.QueryManagerImpl.createQuery(QueryManagerImpl.java:98)
>
>
> Is there another way to achieve my use case?
> Or is there none so far and should I open a JIRA to once make my original
> query work, which seems to be a valid SQL-2 query by specification ? Perhaps
> a patch could check in the JoinQuery for the child node join condition and
> internally when creating the lucene orderings create the relative path if
> the ordering is on the child node ? Or is there a better place to fix that ?
>
> Regards,
> Benjamin
>
>
> 2009/10/28 Thomas Müller <th...@day.com>
>
>> Hi,
>>
>> > I have tried to use a relative path, but I was getting
>> > syntax errors in SQL-2.
>>
>> Could you tell me the exact statement you have used and the exception
>> message and stack trace?
>>
>> Regards,
>> Thomas
>>
>
Re: SQL-2 Child Axis in order by clause
Posted by Benjamin Papez <ja...@gmail.com>.
Hello,
my wanted query is like this, which seems to be a valid query from
specification point of view:
select press.* from [web_templates:pressContainer] as press inner join
[jnt:translation] as translation on ischildnode(translation, press) where
contains(press.*, '${searchTerm}') or contains(translation.*,
'${searchTerm}') order by translation.[jcr:title_en] desc
unfortunately it does not work, because it looks like
SimpleScoreDocComparator is looking in Lucene documents representing
pressContainer and not translation (child node).
I saw that Jackrabbit implemented a RelPathScoreDocComparator for use cases
like mine, but that comparator implementation is not picked with queries
like the one above. Like Marcel mentioned it is only used when there is a
relative path in the order by statement.
Now according to JSR-283 specifications it looks like relative path is not
allowed in the order by statement. That is also why I get the following
error, trying it:
2009-11-04 12:42:08,750: ERROR [JCRSQLTag] - InvalidQueryException --->
[select press.* from [web_templates:pressContainer] as press inner join
[jnt:translation] as translation on ischildnode(translation, press) inner
join [nt:file] as file on translation.pdfVersion_en = file.[jcr:uuid] inner
join [nt:resource] as filecontent on ischildnode(filecontent, file) where
contains(filecontent.*, 'Europe') order by
press.[jnt:translation/jcr:title_en] desc] is not valid.
javax.jcr.query.InvalidQueryException: '/' not allowed in name
at
org.apache.jackrabbit.spi.commons.query.qom.QueryObjectModelFactoryImpl.checkPropertyName(QueryObjectModelFactoryImpl.java:1032)
at
org.apache.jackrabbit.spi.commons.query.qom.QueryObjectModelFactoryImpl.propertyValue(QueryObjectModelFactoryImpl.java:668)
at
org.apache.jackrabbit.spi.commons.query.sql2.Parser.parsePropertyValue(Parser.java:425)
at
org.apache.jackrabbit.spi.commons.query.sql2.Parser.parseDynamicOperand(Parser.java:386)
at
org.apache.jackrabbit.spi.commons.query.sql2.Parser.parseOrder(Parser.java:542)
at
org.apache.jackrabbit.spi.commons.query.sql2.Parser.createQueryObjectModel(Parser.java:121)
at
org.apache.jackrabbit.spi.commons.query.sql2.SQL2QOMBuilder.createQueryObjectModel(SQL2QOMBuilder.java:55)
at
org.apache.jackrabbit.core.query.QOMQueryFactory.createQuery(QOMQueryFactory.java:69)
at
org.apache.jackrabbit.core.query.CompoundQueryFactory.createQuery(CompoundQueryFactory.java:67)
at
org.apache.jackrabbit.core.query.QueryManagerImpl.createQuery(QueryManagerImpl.java:98)
Is there another way to achieve my use case?
Or is there none so far and should I open a JIRA to once make my original
query work, which seems to be a valid SQL-2 query by specification ? Perhaps
a patch could check in the JoinQuery for the child node join condition and
internally when creating the lucene orderings create the relative path if
the ordering is on the child node ? Or is there a better place to fix that ?
Regards,
Benjamin
2009/10/28 Thomas Müller <th...@day.com>
> Hi,
>
> > I have tried to use a relative path, but I was getting
> > syntax errors in SQL-2.
>
> Could you tell me the exact statement you have used and the exception
> message and stack trace?
>
> Regards,
> Thomas
>
Re: SQL-2 Child Axis in order by clause
Posted by Thomas Müller <th...@day.com>.
Hi,
> I have tried to use a relative path, but I was getting
> syntax errors in SQL-2.
Could you tell me the exact statement you have used and the exception
message and stack trace?
Regards,
Thomas
Re: SQL-2 Child Axis in order by clause
Posted by Benjamin Papez <ja...@gmail.com>.
Hello Marcel,
thanks for your reply. You asked me if ordering on child axis works without
the join and that RelPathScoreDocComparator only works when using a relative
path in the order by. I have tried to use a relative path, but I was getting
syntax errors in SQL-2.
Could you tell me the right SQL-2 syntax for my query using a relative path
in the order by ? And how can I make the same fulltext search select on
web_templates:pressContainer and its jnt:transalation child node without
join? I also only want to get one result even if there is a match in the
pressContainer and the child node.
Thanks for your help.
Regards,
Benjamin
2009/10/26 Marcel Reutegger <ma...@gmx.net>
> Hi,
>
> On Wed, Oct 21, 2009 at 10:01, Benjamin Papez <ja...@gmail.com>
> wrote:
> > Hello,
> >
> > I am using Jackrabbit 2.0 alpha10 and I am testing the following SQL-2
> > query:
> >
> > select press.* from [web_templates:pressContainer] as press inner join
> > [jnt:translation] as translation on ischildnode(translation, press) where
> > contains(press.*, '${searchTerm}') or contains(translation.*,
> > '${searchTerm}') order by translation.[jcr:title_en] desc
> >
> > but the ordering does not work.
>
> does it work without the join?
>
> otherwise please file a jira issue and provide steps how to reproduce
> the issue. thanks.
>
> > Debugging the SimpleScoreDocComparator I see
> > that the values for comparing are null all the time. Looking at
> > *JCR-800<http://issues.apache.org/jira/browse/JCR-800>
> > *I assume that RelPathScoreDocComparator should be used instead.
>
> that one is only used when there is a relative path in the order by
> clause with more than one segment. otherwise the
> SimpleScoreDocComparator is used.
>
> regards
> marcel
>
> > Does this
> > already work with SQL-2 queries or do I have to change the query in order
> to
> > make it work ?
> >
> > Regards,
> > Benjamin
> >
>
Re: SQL-2 Child Axis in order by clause
Posted by Marcel Reutegger <ma...@gmx.net>.
Hi,
On Wed, Oct 21, 2009 at 10:01, Benjamin Papez <ja...@gmail.com> wrote:
> Hello,
>
> I am using Jackrabbit 2.0 alpha10 and I am testing the following SQL-2
> query:
>
> select press.* from [web_templates:pressContainer] as press inner join
> [jnt:translation] as translation on ischildnode(translation, press) where
> contains(press.*, '${searchTerm}') or contains(translation.*,
> '${searchTerm}') order by translation.[jcr:title_en] desc
>
> but the ordering does not work.
does it work without the join?
otherwise please file a jira issue and provide steps how to reproduce
the issue. thanks.
> Debugging the SimpleScoreDocComparator I see
> that the values for comparing are null all the time. Looking at
> *JCR-800<http://issues.apache.org/jira/browse/JCR-800>
> *I assume that RelPathScoreDocComparator should be used instead.
that one is only used when there is a relative path in the order by
clause with more than one segment. otherwise the
SimpleScoreDocComparator is used.
regards
marcel
> Does this
> already work with SQL-2 queries or do I have to change the query in order to
> make it work ?
>
> Regards,
> Benjamin
>