You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@jackrabbit.apache.org by Bahl Christian <ch...@gwvs.de> on 2009/10/22 16:02:36 UTC

SQL Query: Search on childnode and property 

Hi,

i´ve the following sql query:

SELECT * FROM nt:unstructured WHERE title='any title' AND articleDocument='false' AND /companyIds/collection-element IS NULL AND /menuIds/collection-element/menuId='1234'

Now my problem is this part " AND /companyIds/collection-element IS NULL AND /menuIds/collection-element IS NULL". It shows error in sql query language.
So my question: is it possible to do search properties on childnodes with sql query language? If so, can you please give me some examples?

Thx
Christian

Re: SQL Query: Search on childnode and property

Posted by Marcel Reutegger <ma...@gmx.net>.
Hi,

On Thu, Oct 29, 2009 at 13:12, Bodo1981 <ch...@gmail.com> wrote:
> [(menuIds/collection-element/@menuid='1485') and
> not(companyIds/collection-element/@name)]
> -> result 0 rows

hmm, what if you try it with a property that is available on every
node: jcr:primaryType ?

[(menuIds/collection-element/@menuid='1485') and
 not(companyIds/collection-element/@jcr:primaryType)]

regards
 marcel

Re: SQL Query: Search on childnode and property

Posted by Bodo1981 <ch...@gmail.com>.

Marcel Reutegger wrote:
> 
> Hi,
> 
> On Thu, Oct 22, 2009 at 16:02, Bahl Christian <ch...@gwvs.de>
> wrote:
>> Hi,
>>
>> i´ve the following sql query:
>>
>> SELECT * FROM nt:unstructured WHERE title='any title' AND
>> articleDocument='false' AND /companyIds/collection-element IS NULL AND
>> /menuIds/collection-element/menuId='1234'
>>
>> Now my problem is this part " AND /companyIds/collection-element IS NULL
>> AND /menuIds/collection-element IS NULL". It shows error in sql query
>> language.
>> So my question: is it possible to do search properties on childnodes with
>> sql query language?
> 
> no, this is not possible.
> 
>> If so, can you please give me some examples?
> 
> you should use XPath instead:
> 
> //element(*, nt:unstructured)[@title='any title' and @articleDocument
> = 'false' and not(companyIds/@collection-element) and
> menuIds/collection-element/@menuId = '1234']
> 
> notes: JCR does not have null values, so you check for the existence
> of a property. paths that start with a slash are absolute (which is
> probably not what you want here...).
> 
> regards
>  marcel
> 
>> Thx
>> Christian
>>
> 
> 

Thanks for the answer.

I tested it with your suggestion, but my problem is that the path
"companyIds/collection-element" does not exist. there is only the path
"companyIds", and "collection-element" is missed. the next problem,
"collection-element" is not a property like you thought, it´s a
path-element.

So the query you showed me doesn`t go at all.

Is there another possibility to solve my problem? i also tested the
following query:

[(menuIds/collection-element/@menuid='1485') and
not(companyIds/collection-element)]
-> result the same like [(menuIds/collection-element/@menuid='1485')]

[(menuIds/collection-element/@menuid='1485') and
not(companyIds/@collection-element)]
-> result the same like [(menuIds/collection-element/@menuid='1485')]

[(menuIds/collection-element/@menuid='1485') and
not(companyIds/collection-element/@name)]
-> result 0 rows

Thx
-- 
View this message in context: http://www.nabble.com/SQL-Query%3A-Search-on-childnode-and-property-tp26010510p26111877.html
Sent from the Jackrabbit - Users mailing list archive at Nabble.com.

Re: SQL Query: Search on childnode and property

Posted by Marcel Reutegger <ma...@gmx.net>.
Hi,

On Thu, Oct 22, 2009 at 16:02, Bahl Christian <ch...@gwvs.de> wrote:
> Hi,
>
> i´ve the following sql query:
>
> SELECT * FROM nt:unstructured WHERE title='any title' AND articleDocument='false' AND /companyIds/collection-element IS NULL AND /menuIds/collection-element/menuId='1234'
>
> Now my problem is this part " AND /companyIds/collection-element IS NULL AND /menuIds/collection-element IS NULL". It shows error in sql query language.
> So my question: is it possible to do search properties on childnodes with sql query language?

no, this is not possible.

> If so, can you please give me some examples?

you should use XPath instead:

//element(*, nt:unstructured)[@title='any title' and @articleDocument
= 'false' and not(companyIds/@collection-element) and
menuIds/collection-element/@menuId = '1234']

notes: JCR does not have null values, so you check for the existence
of a property. paths that start with a slash are absolute (which is
probably not what you want here...).

regards
 marcel

> Thx
> Christian
>