Expand my Community achievements bar.

Dive into Adobe Summit 2024! Explore curated list of AEM sessions & labs, register, connect with experts, ask questions, engage, and share insights. Don't miss the excitement.
SOLVED

Question regarding query builder API search excluding a path

Avatar

Level 2

Hi Sir/Madam,

I have a question regarding the query builder API (http://docs.adobe.com/docs/en/cq/current/dam/customizing_and_extendingcq5dam/query_builder.html).

If I have a tree structure as below:

--> Landing
    --> Category 1
       --> Sub Category 1
           - Item 1
           - Item 2
       --> Sub Category 2
           - Item 3

    --> Category 2
       --> Sub Category 1
           - Item 4
           - Item 5

What I need to achieve is by using query builder API:

1. Grab 3 items from a given subcategory

2. If there is less than 3 items in the given subcategory, grab from other subcategory under the same category until I get 3 items.

3. All the items in the list need to be sorted by a date attribute in the Item page properties.

 

What I can think of is to call the query builder twice:

Step 1. Select 3 items from the given subcategory sorted by date attribute. (No Issue)

Step 2. Check if there are 3 items, if not, get the leftover amount from the parent of the subcategory (which is category), but the problem here is I cannot find a way to exclude the query builder API to retrieve item from the subcategory given in Step 1, so there is a possibility my items in the list will repeat.

Step 3. If step 2 working, re-sort the whole list by date attribute.

So my question is if there a way to exclude the subcategory path in the 2nd search? Or is there a better way to achieve this?

My sample code:

Map<String, String> queryMap = new HashMap<String, String>(); queryMap.put("type", "cq:Page"); queryMap.put("path", subCategory.getPath()); queryMap.put("1_property", "jcr:content/jcr:title"); queryMap.put("1_property.operation", "unequals"); queryMap.put("1_property.value", currentPage.getTitle()); queryMap.put("orderby", "@jcr:content/itemDate"); queryMap.put("orderby.sort", "desc"); QueryBuilder queryBuilder = slingRequest.getResourceResolver().adaptTo(QueryBuilder.class); Session session = slingRequest.getResourceResolver().adaptTo(Session.class); Query query = queryBuilder.createQuery(PredicateGroup.create(queryMap), session); query.setHitsPerPage(pageSize); SearchResult searchResults = query.getResult(); for (Hit hit : searchResults.getHits()) { Page childPage = pageManager.getPage(hit.getPath()); if (childPage.isValid()) { pageList.add(childPage); } }

 

Thanks,

Alvin

1 Accepted Solution

Avatar

Correct answer by
Level 10

For this type of query - i would look at using the JCR SQL/2. You may have better results by specifying JCR SQL search criteria. See:

https://wiki.magnolia-cms.com/display/WIKI/JCR+Query+Cheat+Sheet

http://www.day.com/maven/jcr/2.0/6_Query.html.

http://www.h2database.com/jcr/grammar.html

What I like about JCR SQL is the ability is include and exclude JCR paths. For example:

 String sql= "SELECT * FROM sling:Folder WHERE jcr:path LIKE '"+path +"/%'  AND NOT jcr:path LIKE '"+path +"/%/%'";

You should be able to write methods using JCR SQL to grab all content in the tree that you specified and search through it. 

View solution in original post

1 Reply

Avatar

Correct answer by
Level 10

For this type of query - i would look at using the JCR SQL/2. You may have better results by specifying JCR SQL search criteria. See:

https://wiki.magnolia-cms.com/display/WIKI/JCR+Query+Cheat+Sheet

http://www.day.com/maven/jcr/2.0/6_Query.html.

http://www.h2database.com/jcr/grammar.html

What I like about JCR SQL is the ability is include and exclude JCR paths. For example:

 String sql= "SELECT * FROM sling:Folder WHERE jcr:path LIKE '"+path +"/%'  AND NOT jcr:path LIKE '"+path +"/%/%'";

You should be able to write methods using JCR SQL to grab all content in the tree that you specified and search through it.