Query Builder

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

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

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

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. 

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) 

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();
        }
    }
    
    
        

}

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]=''

I am going to test this and report back!!

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.