You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@jackrabbit.apache.org by Will Scheidegger <wi...@mac.com> on 2008/06/29 12:28:37 UTC
SQL query questions: all nodes which do not have certain subnodes
I've got a node structure like this
- path
- to
- my
- productCategories
- category1
- category2
- superCategories
- 0 = category1
- category3
- superCategories
- 0 = category1
- category4
- superCategories
- 0 = category2
- 1 = cagegory3
- category5
As you can see, a category can have references to multiple
superCategories. Now I would like to get all "top level"
productcategories, i.e. all product categories with no supercategories
assigned (= category1 and category5 in my sample above). Strange
enough finding all categories which do have a super category assign
works fine:
select * from nt:base where superCategories IS NOT NULL
The oposit does not work:
select * from nt:base where superCategories IS NULL
lists all product categories! Strange to me, but maybe one of you has
a good explanation or even better: A solution to my problem? And to
make it a bit more difficult: This node should also be found by the
query:
- category6
- superCategories
(i.e. a node with a subnode "superCategories", but this node does not
contain any references).
Thanks for the help!
-will
Re: SQL query questions: all nodes which do not have certain subnodes
Posted by Will Scheidegger <wi...@mac.com>.
Thanks for the help. I thought about trying the Xpath approach. But I
don't have much experience there... ;-)
And also thanks for the advice about multi-value properties. I guess I
have to rethink my setup.
-will
On 30.06.2008, at 12:55, Alexander Klimetschek wrote:
> Hi Will!
>
> Not very experienced with SQL in JCR, since I like using Xpath more
> for JCR queries, so this is an Xpath solution ;-)
>
> You should store the list of supercategories in a multi-value property
> instead of a subnode (using multival properties is more natural for
> JCR than using node structures with self-created indices like 0,1,..
> in your example). Then you would have a multi-value property
> superCategories in each category node (or none, if it is empty). The
> Xpath query for your use case of finding categories with a
> supercategoriy would then be:
>
> /jcr:root/path/to/my/productCategories/*[@superCategories]
>
> Or for finding nodes that don't have a supercategory:
>
> /jcr:root/path/to/my/productCategories/*[not(@superCategories)]
>
> Hope this helps!
>
> Regards,
> Alex
>
> On Sun, Jun 29, 2008 at 6:28 AM, Will Scheidegger
> <wi...@mac.com> wrote:
>> I've got a node structure like this
>>
>> - path
>> - to
>> - my
>> - productCategories
>> - category1
>> - category2
>> - superCategories
>> - 0 = category1
>> - category3
>> - superCategories
>> - 0 = category1
>> - category4
>> - superCategories
>> - 0 = category2
>> - 1 = cagegory3
>> - category5
>>
>> As you can see, a category can have references to multiple
>> superCategories.
>> Now I would like to get all "top level" productcategories, i.e. all
>> product
>> categories with no supercategories assigned (= category1 and
>> category5 in my
>> sample above). Strange enough finding all categories which do have
>> a super
>> category assign works fine:
>>
>> select * from nt:base where superCategories IS NOT NULL
>>
>> The oposit does not work:
>>
>> select * from nt:base where superCategories IS NULL
>>
>> lists all product categories! Strange to me, but maybe one of you
>> has a good
>> explanation or even better: A solution to my problem? And to make
>> it a bit
>> more difficult: This node should also be found by the query:
>>
>> - category6
>> - superCategories
>>
>> (i.e. a node with a subnode "superCategories", but this node does not
>> contain any references).
>>
>> Thanks for the help!
>> -will
>>
>
>
>
> --
> Alexander Klimetschek
> alexander.klimetschek@day.com
Re: SQL query questions: all nodes which do not have certain subnodes
Posted by Alexander Klimetschek <ak...@day.com>.
Hi Will!
Not very experienced with SQL in JCR, since I like using Xpath more
for JCR queries, so this is an Xpath solution ;-)
You should store the list of supercategories in a multi-value property
instead of a subnode (using multival properties is more natural for
JCR than using node structures with self-created indices like 0,1,..
in your example). Then you would have a multi-value property
superCategories in each category node (or none, if it is empty). The
Xpath query for your use case of finding categories with a
supercategoriy would then be:
/jcr:root/path/to/my/productCategories/*[@superCategories]
Or for finding nodes that don't have a supercategory:
/jcr:root/path/to/my/productCategories/*[not(@superCategories)]
Hope this helps!
Regards,
Alex
On Sun, Jun 29, 2008 at 6:28 AM, Will Scheidegger
<wi...@mac.com> wrote:
> I've got a node structure like this
>
> - path
> - to
> - my
> - productCategories
> - category1
> - category2
> - superCategories
> - 0 = category1
> - category3
> - superCategories
> - 0 = category1
> - category4
> - superCategories
> - 0 = category2
> - 1 = cagegory3
> - category5
>
> As you can see, a category can have references to multiple superCategories.
> Now I would like to get all "top level" productcategories, i.e. all product
> categories with no supercategories assigned (= category1 and category5 in my
> sample above). Strange enough finding all categories which do have a super
> category assign works fine:
>
> select * from nt:base where superCategories IS NOT NULL
>
> The oposit does not work:
>
> select * from nt:base where superCategories IS NULL
>
> lists all product categories! Strange to me, but maybe one of you has a good
> explanation or even better: A solution to my problem? And to make it a bit
> more difficult: This node should also be found by the query:
>
> - category6
> - superCategories
>
> (i.e. a node with a subnode "superCategories", but this node does not
> contain any references).
>
> Thanks for the help!
> -will
>
--
Alexander Klimetschek
alexander.klimetschek@day.com