...
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 |
...
...
= |
...
'eric@example.com' |
...
//element(*, |
...
mgnl:user) |
...
...
= |
...
'eric@example.com' |
...
] |
...
select |
...
* |
...
from |
...
[mgnl:user] |
...
where |
...
...
= |
...
'eric@example.com |
...
' | |||
| Component with template id | select * from [mgnl:component] where [mgnl:template] = 'project-site-name:components/landing/callout' | ||
|---|---|---|---|
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 template | select * from nt:base where jcr:path like '/demo-project/%' AND mgnl:template = 'standard-templating-kit:stkNews' | SELECT parent.* ** 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 UUID | select * 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 insensitive | select * 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 | ||
| Get all nodes which have any property with a given value. E.g., useful for finding references to a given uuid. | SELECT * FROM [nt:base] WHERE contains([nt:base].*, '4055e292-7b01-4075-b4c8-47d73e2e7d47') |
Java code example for 'date' query:
| Code Pro | ||||
|---|---|---|---|---|
| ||||
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