Page tree
Skip to end of metadata
Go to start of metadata

Your Rating: Results: PatheticBadOKGoodOutstanding! 197 rates
This is a collection of mini-recipes for doing JCR queries. Please add your own!

 

SQL (deprecated in JCR 2.0)

XPath (deprecated in JCR 2.0)

SQL2

All pages

select * from mgnl:content

//element(*, mgnl:content)

select * from [mgnl:page]

Pages with "News" in the title

select * from mgnl:content where title like '%News%'

//element(*, mgnl:content)[jcr:like(@title, '%News%')]

select * from [mgnl:page] where title like '%News%'

Pages where the title exactly matches "News" (case sensitive)

select * from mgnl:content where title like 'News'

//element(*, mgnl:content)[@title = 'News']

select * from [mgnl:page] where title like 'News'

STK pages that have a header image

select * from mgnl:content where image is not null

//element(*, mgnl:content)[@image]

select * from [mgnl:page] where image is not null

Instances of a "Teaser" paragraph

select * from nt:base where mgnl:template = 'stkTeaser'

//*[@mgnl:template = 'stkTeaser']

select * from [nt:base] where [mgnl:template] = 'standard-templating-kit:components/teasers/stkTeaser'

Available paragraph types

select * from nt:base where jcr:path like '/modules/%/paragraphs/%' and type is not null

/jcr:root/modules[1]///paragraphs[1]//[@type]

 

User with email 'eric@example.com'

select * from mgnl:user where email = 'eric@example.com'

//element(*, mgnl:user)[@email = 'eric@example.com']

select * from [mgnl:user] where email = 'eric@example.com'

Pages that have the word "component"

SELECT * from nt:base WHERE jcr:path like '/ftl-sample-site%' AND contains(*, 'component') AND (jcr:primaryType = 'mgnl:page' OR jcr:primaryType = 'mgnl:area' OR jcr:primaryType = 'mgnl:component') order by jcr:path

 

SELECT * from [nt:base] AS t WHERE ISDESCENDANTNODE('/ftl-sample-site') AND contains(t.*, 'component')

Template folders in module configuration

select * from mgnl:content where jcr:path like '/modules/%/templates'

 

select * from [mgnl:content] as t where ISDESCENDANTNODE('/modules') and name(t) = 'templates'

Modules that provide commands

select * from nt:base where jcr:path like '/modules/%/commands'

 

select * from [mgnl:content] as t where ISDESCENDANTNODE('/modules') and name(t) = 'commands'

All pages with a specific template ordered by title

 

 

SELECT p.* FROM [nt:base] AS p WHERE [mgnl:template] = 'xxx:pages/jobs' order by p.[title] asc

Pages under given path with given templateselect * from nt:base where jcr:path like '/demo-project/%' AND mgnl:template = 'standard-templating-kit:stkNews' 

SELECT parent.*
FROM [mgnl:page] AS parent
INNER JOIN [mgnl:metaData] AS child ON ISCHILDNODE(child,parent)
WHERE
ISDESCENDANTNODE(parent, '/demo-project')
AND child.[mgnl:template] = 'standard-templating-kit:stkNews'

 

** When using this query, one need to get results via getRows() instead of getNodes() since queries w/ joins can eventually return multiple different node types.

Pages under given path with given template and category ordered by date /jcr:root/demo-project//element(*, mgnl:metaData)[@mgnl:template = 'standard-templating-kit:pages/stkArticle']/..[@categories = 'ab9437db-ab2c-4df5-bb41-87e55409e8e1'] order by @date 
Search a Node with a certain UUIDselect * from nt:base where jcr:uuid = '7fd401be-cada-4634-93fa-88069f46297b' SELECT * FROM [nt:base] WHERE [jcr:uuid] = '7fd401be-cada-4634-93fa-88069f46297b'
Search case insensitiveselect * from nt:base where lower(name) like 'name_in_lowercase' select * from [nt:base] where lower(name) like 'name_in_lowercase'
Search demo-project pages created in given time frame  select * from [mgnl:page] where ISDESCENDANTNODE('/demo-project/') and [jcr:created] > cast('2010-01-01T00:00:00.000+02:00' as date) and [jcr:created] < cast('2014-09-30T23:59:59.000+02:00' as date)
Pages in 'demo-project' which using a specific template ('stkSection' for example) and has the content just been modified by 'eric' /jcr:root/demo-project/*[mgnl:template='standard-templating-kit:pages/stkSection']/*/content[mgnl:lastModifiedBy='eric']/../.. 

Get all nodes which have a property 'date' which is not empty and this date starts at least 1 second after current midnight. Useful for events.

  

SELECT p.* FROM [nt:base] AS p WHERE ISDESCENDANTNODE('/') AND (p.[date] <> '' AND p.[date] > CAST('2015-11-30T00:00:01.000Z' AS DATE)) order by p.[date] asc

 

Java code example for 'date' query:

Date today = Calendar.getInstance().getTime();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Calendar c = Calendar.getInstance();
c.setTime(today); // Now use today date.
String path = "/";

// future events
c.add(Calendar.DATE, -1); // minus 1 day
String date = sdf.format(c.getTime());
String statement = "SELECT p.* FROM [nt:base] AS p WHERE ISDESCENDANTNODE('" + path + "') AND (p.[date] <> '' " + "AND p.[date] > CAST('" + date
        + "T00:00:01.000Z' AS DATE)) "
        + "order by p.[date] asc";
 

Note: you can use the translator to convert from one format to another.

Pages under given path with given template

  • No labels

21 Comments

  1. Is it possible to retrieve node data (e.g. a list of all e-mail addresses) and not users having an e-mail address?

    //element(*, mgnl:user)/@email

    What would be the 'Result ItemType'?

    1. Not sure how this would look like in xpath, but in sql2 you can run such query as 

      SELECT parent.email AS email FROM [mgnl:user] AS user

      but then after calling query.execute() you need to call getRows() instead of getValues() and for each row in the result set you need to call getValue("email")

  2. Needed a solution for how to filter for templates but return content never the less. Here it is:

    String query = "select * from [mgnl:metaData] where [mgnl:template] = 'ns:path/template'";
    NodeIterator result = QueryUtil.search("website", query, "JCR-SQL2", "mgnl:page");

    Hope it could be helpful.

    1. Hi Manuel,

      thanks for the example. I've just added up to the page another query that does the same, but limits search space to only metadata of sub pages and returns page nodes in the result set directly rather then filtering them through the QueryUtil's NodeTypeParentPredicate however such query requires working with rows and can't be executed using QueryUtil. Anyway, unless your server is running short on memory or you return huge number of results, there is most likely no difference in the performance.

  3. BTW for what it's worth there's applet that can verify validity of the sql or xpath query and translate them between each other (AFAIK it's based on JCR 1.0 so don't expect to test more advanced search functions in it) http://people.apache.org/~mreutegg/jcr-query-translator/translator.html

  4. Is it possible to retrieve all versions of a node in SQL2 query?

    1. Not possible over JCR query. At least not directly, you might be able to construct set of queries to get from current node to frozen node from version store over uuid and then search version store for other references, but you would be banking on knowledge of internal structure of the particular repo impl so I would not go down that path.

  5. Hi,

    Can we get the depth of a node over a jcr sql2?

    1. Hi, you can get a node then use method "getDepth()" of javax.jcr.Item interface - the easiest way I think.

    2. Not that I know of. However since you have the node already, you can just count the segments in the path to get the depth, no?

  6. JCR Sql2 with 'inner join' example.

    Use case: You have a table of registered courses with date/time and location of the courses and you have a table of registrations which contains the ID of the course as its foreign key. Then you want to query for registrations of a specified user (using userId) from now on (current querying time). Here is the query:

    SELECT registrations.*, registeredCourses.* 
    FROM [registered-course] AS registeredCourses 
    INNER JOIN [registration] as registrations 
    ON registrations.[registeredCourseId] = registeredCourses.[jcr:uuid] 
    WHERE registrations.[userId] = '%s' 
    AND (registeredCourses.[obsoleted] <> 'true' OR registeredCourses.[obsoleted] IS NULL) 
    AND registeredCourses.[date] >= CAST('%s' AS DATE) 
    ORDER BY registeredCourses.[date] DESC ;

    Here is how we get the result using Java:

    int count = maxItems;
    for (Node user : userList) {
        Session jcrSession = MgnlContext.getJCRSession("data");
        QueryManager jcrQueryManager = jcrSession.getWorkspace().getQueryManager();
        Query query = jcrQueryManager.createQuery(String.format(sql2, user.getId(), dateFromat.format(Calendar.getInstance().getTime())), Query.JCR_SQL2);
        QueryResult queryResult = query.execute();
        RowIterator iterator = queryResult.getRows();
        while (iterator.hasNext() && count>0) {
            count--;
            Row currentRow = iterator.nextRow();
            Node node = currentRow.getNode("registeredCourses");//name defined in sql2 query as an alias
            result.add(node);
        }
    }

    Hope this help as an example of using JCR SQL2.

  7. Is there a way to limit the query results?

    I found this https://docs.jboss.org/jbossdna/0.7/manuals/reference/html/jcr-query-and-search.html#jcr-sql2-limits

    But when I'm trying it out in the JCR Queries Dev Tool with this query for example:

    SELECT * FROM [mgnl:page] LIMIT 10

    I get this result message:

    Query:
    SELECT * FROM [mgnl:page] LIMIT(*)10; expected: <end>

    I don't really know what to do with it.

    1. You can't set limit directly in the query string. You need to set it in the query object instead.

      session = MgnlContext.getJCRSession("website");
      qm = s.getWorkspace().getQueryManager();
      q = qm.createQuery("select * from [mgnl:page]", "JCR-SQL2");
      q.setLimit(10);
      q.execute();
      1. Ah, ok! That's why it didn't work.

        Thanks for your reply!

  8. I whant to execute the following SELECT.

    But now results are showing up.

    SELECT * 
    FROM [nt:base]
    WHERE [mgnl:group] like '%VDB%'

    Do you have an idea, whats wrong here?

    1. More like this:

      select * from [mgnl:group] where title like 'Editors%'
      1. Thank you. Title works so far, but I want to look for strings in the content-node (/VDB_xyz).

        1. No prob. I cannot find a way to do it. ISDESCENDANTNODE doesn't seem to be working with wildcards. If I figure it out I'll let you know.

    2. Try with sql, not SQL2:

      select * from nt:base where contains("mgnl:group", '%VDB%')

      1. Thank you,

        I've tried this too, unfortunately without success → 0 nodes returned

    3. Could you actually try to explain in English what you are searching for? Since the query doesn't work for you, what you are trying to express by it is obviously wrong and so will be any followup since no one knows the real intentions you have.

      There is no property called mgnl:group anywhere in Magnolia that I can think of and I sincerely doubt you had named anyting like that in your node/component yourself.

      What you can see in Magnolia OOTB is either

      • group node type called [mgnl:group]
      • node name of the list of groups assigned to user called [mgnl:group]

      In either case the query would not work.

      In first case, you would need something like select * from [mgnl:group] as t where name(t) = 'exact group name, no wild cards' or something like select * from [mgnl:group] as t where ISDESCENDANTNODE([/%VDB%]) (luckily for you by default there's no structure in groups unless you changed group manager)

      In second case what you want is select * from [nt:base] as t where ISDESCENDANTNODE([/%/mgnl:group/%]) and contains(t.*,'VDB') tho that works only on indexed words and i'm not sure if partial mapping is considered as match or not. Anyway, point I was trying to make is that it's not the property name that is mgnl:group in this case either.

       

      You might want to provide example of how the structure looks like in JCR and highlight what you want to find for anyone to really help you putting together right query.