SQL Query: Search on childnode and property

[Bahl Christian]

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

[Marcel Reutegger]

Hi,

On Thu, Oct 22, 2009 at 16:02, Bahl Christian <[hidden email]> 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
>

[Bodo1981]

Marcel Reutegger wrote:

Hi,

On Thu, Oct 22, 2009 at 16:02, Bahl Christian <[e-mail]> 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

[Marcel Reutegger]

Hi,

On Thu, Oct 29, 2009 at 13:12, Bodo1981 <[hidden email]> 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