Magnolia SQL filter nodes

15 views
Skip to first unread message

Stefan Brockwitz

unread,
Dec 14, 2021, 2:40:38 PM12/14/21
to Magnolia User Mailing List
Hello again,

we managed to filter nodes with offset and limit like the following:

final Query query = jcrQueryManager.createQuery("SELECT * FROM [mgnl:page]), Query.JCR_SQL2);
query.setLimit(resultsPerPage);
query.setOffset(offset);

In content/categories we have a Collection of categoryIds.
How to select only pages that have a specific categoryId with limit and offset?
So like the following. How to achieve it?
SELECT * FROM [mgnl:page] AS p WHERE 'categoryid' IN (p.content.categories)

Another question is: How to get number of total Results when limiting?

Thank you very much.


Roman Kovařík

unread,
Dec 15, 2021, 1:53:31 PM12/15/21
to Magnolia User Mailing List, stefan.b...@neofonie.de
Hello :-), 

SELECT * FROM [mgnl:page] AS p WHERE 'categoryid' IN (p.content.categories)
 
I think you would need to do this like this:

SELECT * FROM [mgnl:page] AS p WHERE (p.categoryid = 'cat1' OR p.categoryid = 'cat2' OR...) 

Another question is: How to get number of total Results when limiting?

query.execute().getNodes().getSize() returns number of results per page. 
I don't know about a possibility to get total size of all items (all pages), but you can keep paging until there are no results.

Regards
Roman 
Reply all
Reply to author
Forward
0 new messages