Page History

...

SQL

XPath (deprecated in JCR 2.0)

SQL2

Java code

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'

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.*
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 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 is in future.

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

Code Block

language	java

        Date today = today();

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

        Calendar c = Calendar.getInstance();

        c.setTime(today); // Now use today date.




        String path = "/";




        // all

        String statement = "SELECT p.* FROM [nt:base] AS p WHERE ISDESCENDANTNODE([" + path + "]) AND p.[startDate] <> '' order by p.[startDate] asc";




        // future events

        if (type.equals("future")) {

            c.add(Calendar.DATE, -1); // minus 1 day

            String date = sdf.format(c.getTime());

            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.

...

Page tree

Versions Compared

Old Version 29

New Version 30

Key