Expand my Community achievements bar.

Don’t miss the AEM Skill Exchange in SF on Nov 14—hear from industry leaders, learn best practices, and enhance your AEM strategy with practical tips.
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.