Query Builder

[amoghd64765494]

I want to search pages which are having node 'contactform' and inside that property name called 'id' whose value is empty/null.

Can some one please help me with this to write a query? I struggled much to get this done.

[Ratna_Kumar]

Hi Amogh,

Take a look at this article which describes for your use case.

https://helpx.adobe.com/experience-manager/using/using-query-builder-servlet.html

Thanks,
Ratna Kumar.

[smacdonald2008]

The thread from Ratna is good at learning how to use QueryBuilder API within an OSGi service. However - this question is about a specific query. I will see if we have good examples that can show the syntax for this type of query.

[smacdonald2008]

For this use case - you are not searching for pages with a specific property. If that was the case - it would be much better as you could search for a page with a specific property - as talked about in this section: 

https://docs.adobe.com/docs/en/aem/6-1/develop/search/querybuilder-api.html  (in the Search for properties section)

The property is actually on a child node (i assume type nt:unstructured) - for example: 

Now because of this - you cannot search for specific page with a property. This does not work since the page does not have the property. 

What you can do is use JCR SQL2 and search for nodes of type cq:Page. To make the code faster - you can specify a path in the JCR to search. For example: 

String sqlStatement = "select * from [cq:Page] where isdescendantnode('/content/geometrixx-outdoors') ";

For each result set - check to see if there is a child node named contactform. If so - see if that has an id property that is empty or null. If you find this - place the node into a Java collection.

Once you iterate through your result set - you will get all of the pages that have a child node with an empty id property. 

[Mani_kumar_]

Hi 

Can you try below query this may help you

path=/content
nodename=nodename(node name you want to search)
1_group.1_property=propertyname(Which property you need to search)
1_group.1_property.operation=not

[Himanshu_Singhal]

Hi,

Using the Query Builder, you can design your own query. 

Like, create a servlet and inside it, define Query Builder, crate a map & execute createQuery. See the code below: 

@Reference private QueryBuilder builder; map.put(path,<path of node or folder containing ndoes>); map.put(p.nodedepth,<node depth>); map.put(nodename, <node name>); map.put(p.limit,<max limit>); map.put(p.hits,<no of hits>); map.put(property,<property name>); map.put(property.value,<proerty value>); map.put(orderBy,<orderby clause>); Query query = builder.createQuery(PredicateGroup.create(map), session); SearchResult result = query.getResult(); Iterator<Resource> iterator = result.getResources();

I hope, it'd be helpful.

[amoghd64765494]

korthivada wrote...

Hi 

Can you try below query this may help you

path=/content
nodename=nodename(node name you want to search)
1_group.1_property=propertyname(Which property you need to search)
1_group.1_property.operation=not

Hi,

Thanks for your quick reply.

Above given code when i am running from QueryBuilder its showing result with no required property under node. I want result like node who have required property with null or empty value.

Thanks in Advance

[amoghd64765494]

smacdonald2008 wrote...

For this use case - you are not searching for pages with a specific property. If that was the case - it would be much better as you could search for a page with a specific property - as talked about in this section: 

https://docs.adobe.com/docs/en/aem/6-1/develop/search/querybuilder-api.html  (in the Search for properties section)

The property is actually on a child node (i assume type nt:unstructured) - for example: 

Now because of this - you cannot search for specific page with a property. This does not work since the page does not have the property. 

What you can do is use JCR SQL2 and search for nodes of type cq:Page. To make the code faster - you can specify a path in the JCR to search. For example: 

String sqlStatement = "select * from [cq:Page] where isdescendantnode('/content/geometrixx-outdoors') ";

For each result set - check to see if there is a child node named contactform. If so - see if that has an id property that is empty or null. If you find this - place the node into a Java collection.

Once you iterate through your result set - you will get all of the pages that have a child node with an empty id property. 

 I have already tried this and its giving almost hundreds of result and so it difficult to iterate each and every page for required property. I just want query which we can run through query builder or from crxde.

[smacdonald2008]

If you have hundreds of pages under /content - that is true. If you can narrow your search using a more specific JCR path under /content- it will reduce the result set to iterate over. However - if you need all pages under /content - then you cannot do this. 

Have you tired the suggestion by himanshusinghal?  

Also - what do you need this for. Are you using this for a report of pages and you will only run this every so often?

You are not using this logic in a web page or AEM app are you? I mean the performace will not effect any AEM app. If you need it for a report - i would do JCR SQL - it may be slower - but it will be the best way. 

[Kunal_Gaba_ · Accepted solution]

Here is the JCR SQL2 query for this use case- 

SELECT parent.* FROM [cq:Page] AS parent INNER JOIN [nt:unstructured] AS child ON ISDESCENDANTNODE(child,parent) WHERE ISDESCENDANTNODE(parent, [/content/xxx]) AND  LOCALNAME(child) = 'contactform' AND  child.[id] IS NULL 

[smacdonald2008]

I am going to test this and report back!!

[Kunal_Gaba_]

One more point to add IS NULL check returns true only if the property does not exists on that node. So if the property does exist on the node and we need to check the value as blank then following condition should be used-

child.[id] IS NULL OR child.[id]=''

[smacdonald2008]

Also - ** When using this query, you need to get results via getRows() instead of getNodes() since queries with joins can eventually return multiple different node types.

Here is the a Java class that returns the data you want.  You have more control using JCR SQL2 than QUeryBuilder with respect to join statements. 

This works....

import javax.jcr.Repository;
import javax.jcr.Session;
import javax.jcr.SimpleCredentials;
import javax.jcr.Node;
import javax.jcr.query.Query;
import java.util.List ;
import java.util.ArrayList ;
import org.apache.jackrabbit.commons.JcrUtils;
import org.apache.jackrabbit.core.TransientRepository;

public class TestJCR {

    
    
    private static ArrayList<String> myList = null; 
    
    /**
     * @param args
     */
    public static void main(String[] args) {
        // TODO Auto-generated method stub

    
    
        try {
             
            String aemUrl = "http://localhost:4502/crx/server" ;
            //Create a connection to the CQ repository running on local host
            Repository repository = JcrUtils.getRepository(aemUrl);
 

       
            javax.jcr.Session session = repository.login( new SimpleCredentials("admin", "admin".toCharArray()));
 
            //Obtain the query manager for the session ...
            javax.jcr.query.QueryManager queryManager = session.getWorkspace().getQueryManager();
 
    
            
            String sqlStatement = "SELECT parent.* FROM [cq:PageContent] AS parent INNER JOIN [nt:unstructured] AS child ON isdescendantnode(child,parent) where isdescendantnode(parent, [/content/geometrixx-outdoors]) AND  LOCALNAME(child) = 'contactform' AND  (child.[id]='' OR child.[id] IS NULL) ";
                   
             javax.jcr.query.Query query = queryManager.createQuery(sqlStatement,"JCR-SQL2");
 
            //Execute the query and get the results ...
            javax.jcr.query.QueryResult result = query.execute();
 
            //Iterate over the nodes in the results ...
            javax.jcr.query.RowIterator nodeIter = result.getRows() ;
 
            long mySize = nodeIter.getSize();
            
            myList = new ArrayList(); //allocate mem to the list
 
            while ( nodeIter.hasNext() ) {
 
                //For each node-- get the path of the node
                String myPath = nodeIter.nextRow().getPath("parent");
                System.out.println("@@@@ THE path is "+myPath);    
 
               
           }

                       
           session.logout();
           
          
 
           System.out.println("@@@@ THE END");
           
        }
        catch(Exception e){
            e.printStackTrace();
        }
    }
    
    
        

}

[Kunal_Gaba_]

Also, It will be good to fire this query on cq:PageContent node type to limit the number of results returned- 

SELECT parent.* FROM [cq:PageContent] AS parent INNER JOIN [nt:unstructured] AS child ON isdescendantnode(child,parent) where isdescendantnode(parent, [/content/geometrixx-outdoors]) AND  LOCALNAME(child) = 'contactform' AND  (child.[id]='' OR child.[id] IS NULL) 

[smacdonald2008]

Interesting - when i use this:

SELECT parent.* FROM [cq:PageContent]

It give me a 0 result set.

When I use cq:Page - it give me the result set I am looking for. 

[Kunal_Gaba_]

May be while testing you might have added the contactform node directly under cq:Page node rather than under cq:PageContent node - 

/content/geometrixx-outdoors/en/TestPage/contactform

[smacdonald2008]

Good call - when setting up the nodes for testing - that is exactly what happened. Modified the Java code in above response. Nice pickup! 

[amoghd64765494]

Thanks Guys..For all the replies. My problem is resolved now