repository browser SQL-statement with "NOT LIKE"

[Edwin B.]

Hi,

I'm trying to retrieve all documents of a specific documenttype (mynamespace:mydoctype) that do NOT live in some content-folders.

To find all these documents I can run:

[1] SELECT * FROM mynamespace:mydoctype

To find all documents in a certain folder I can run:

[2] SELECT * FROM mynamespace:mydoctype WHERE jcr:path LIKE '/content/documents/myproject/path/to/folder/%'

But now I want to exclude all the results from [2] from the total results [1], using the NOT LIKE-statement:

[3a] SELECT * FROM mynamespace:mydoctype WHERE jcr:path NOT LIKE '/content/documents/myproject/path/to/myfolder/%'

or

[3b] SELECT * FROM mynamespace:mydoctype WHERE NOT jcr:path LIKE '/content/documents/myproject/path/to/myfolder/%'

Unfortunately, this is not working and does return the same results as [2].

Anyone an idea how to get the mydoctype-documents outside the "my folder"-folder?

Best regards,

Edwin

[Ard Schrijvers]

On Wed, Aug 14, 2013 at 10:10 AM, Edwin B. <ett...@gmail.com> wrote:
> Hi,
>
> I'm trying to retrieve all documents of a specific documenttype
> (mynamespace:mydoctype) that do NOT live in some content-folders.
>
> To find all these documents I can run:
> [1] SELECT * FROM mynamespace:mydoctype
>
> To find all documents in a certain folder I can run:
> [2] SELECT * FROM mynamespace:mydoctype WHERE jcr:path LIKE
> '/content/documents/myproject/path/to/folder/%'

These kind of queries tend to be very expensive in Jackrabbit. You can
better use our hippo:paths property to exclude/include parts of the
tree. This also enables you to include / exclude multiple scopres as
well.

Are you familiar with the HST? The HST Query api lets you easily
create queries in java and you can look at the xpath string
representation then as well. Those are though xpath queries instead of
sql

Regards Ard

>
> But now I want to exclude all the results from [2] from the total results
> [1], using the NOT LIKE-statement:
> [3a] SELECT * FROM mynamespace:mydoctype WHERE jcr:path NOT LIKE
> '/content/documents/myproject/path/to/myfolder/%'
> or
> [3b] SELECT * FROM mynamespace:mydoctype WHERE NOT jcr:path LIKE
> '/content/documents/myproject/path/to/myfolder/%'
>
> Unfortunately, this is not working and does return the same results as [2].
>
> Anyone an idea how to get the mydoctype-documents outside the "my
> folder"-folder?
>
>
> Best regards,
> Edwin
>

> --
> Hippo Community Group: The place for all discussions and announcements about
> Hippo CMS (and HST, repository etc. etc.)
> Most Hippo developers are active here and/or follow the Stackoverflow
> [hippo-cms] tag.
>
> The old Nabble archive can be found here:
> http://hippo.2275632.n2.nabble.com/Hippo-CMS-7-f2274273.html
>
> To post to this group, send email to hippo-c...@googlegroups.com
> RSS:
> https://groups.google.com/group/hippo-community/feed/rss_v2_0_msgs.xml?num=50
> ---
> You received this message because you are subscribed to the Google Groups
> "Hippo Community" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to hippo-communi...@googlegroups.com.
> Visit this group at http://groups.google.com/group/hippo-community.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>



--
Amsterdam - Oosteinde 11, 1017 WT Amsterdam
Boston - 1 Broadway, Cambridge, MA 02142

US +1 877 414 4776 (toll free)
Europe +31(0)20 522 4466
www.onehippo.com

[Edwin B.]

[quote]These kind of queries tend to be very expensive in Jackrabbit.[/quote]

Okay, probably it's expensive.

But when I execute the query [3a/b] it simply returns the same results as [2].

Does this mean querying with "NOT LIKE" doesn't work at all? Is it not supported in the Hippo Repository Browser?

Best regards,

Edwin

Op woensdag 14 augustus 2013 10:31:46 UTC+2 schreef a.schrijvers:

[Ard Schrijvers]

On Wed, Aug 14, 2013 at 10:44 AM, Edwin B. <ett...@gmail.com> wrote:
> [quote]These kind of queries tend to be very expensive in
> Jackrabbit.[/quote]
>
> Okay, probably it's expensive.
> But when I execute the query [3a/b] it simply returns the same results as
> [2].
> Does this mean querying with "NOT LIKE" doesn't work at all? Is it not
> supported in the Hippo Repository Browser?

It just gets delegated to Jackrabbit, so if it is supported in
jackrabbit it works. I am not too familiar with the jcr sql, hence, I
referred to the xpath solution.

At [1] you can find jr sql path tests if you are curious to find out
about your problem. If you just want a solution, go with the much more
flexible hippo:paths approach

Regards Ard

[1] https://svn.apache.org/repos/asf/jackrabbit/branches/2.6/jackrabbit-jcr-tests/src/main/java/org/apache/jackrabbit/test/api/query/SQLPathTest.java

[Mathijs den Burger]

Also see [1] to get started with xpath queries.

Mathijs

[1] http://www.onehippo.org/7_8/library/development/query-the-repository-using-xpath.html

[Jeroen Reijn]

I would advise you to use the XPath approach that both Ard and Mathijs suggested. Using the LIKE on pseudo properties (like jcr:path) is really inefficient and therefor using the hippo path properties is advisable.

The SQL queries which you can perform in side the repository interface only support SQL 1.0 from the JCR 1.0 spec.

In JCR 1.0 only one basic path constraint per query is possible. It boils down to:

1) a node with a given path

2) nodes, which are children of a given path

3) nodes, which are descendants of a given path

See for more info this thread: http://mail-archives.apache.org/mod_mbox/jackrabbit-users/200803.mbox/%3C1205351870.13743.165.camel@blackelf%3E 

and the JCR spec http://www.day.com/specs/jcr/1.0/8.5.2.2_Pseudo-property_jcr_path.html 

AFAIK these queries can be done with the JCR implementation for SQL 2.0 . If you are willing to donate a patch we might be able to add this to the repository interface.

Regards,

Jeroen

Jeroen Reijn
Solution Architect
Hippo

Amsterdam - Oosteinde 11, 1017 WT Amsterdam

Boston - 101 Main Street, Cambridge, MA 02142

US +1 877 414 4776 (toll free)
Europe +31(0)20 522 4466
www.onehippo.com

http://about.me/jeroenreijn

[rlnir...@gmail.com]

Track the path or folder either it is correct or not,check the type of doc and doc is present or not