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

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 51 Next »

Your Rating: Results: 1 Star2 Star3 Star4 Star5 Star 0 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