Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

SQL2 Help

Avatar

Level 4

I'm trying to put together a SQL2 query to find all of the rtePlugins under a cq:dialog node within a path.

Here is what I have so far:

select * from [nt:unstructured] as p where isdescendantnode (p, [/apps]) AND NAME(p)="rtePlugins"

But I'm missing the part where it needs to be under a cq:dialog

It should find these:

/apps/somename/components/page/main/page/cq:dialog/content/items/tabs/items/article/items/articleTitle/rtePlugins

But NOT find this

/apps/somename/components/page/main/page/dialog/content/items/tabs/items/article/items/articleTitle/rtePlugins

I've tried adding another isdescendantnode or contains ... but no luck so far.

Thank you in advance for any assistance.

-Dean

1 Accepted Solution

Avatar

Correct answer by
Level 3

Append your query with this:

AND [jcr:path] like '%/cq:dialog/%'

View solution in original post

8 Replies

Avatar

Level 10

I will look into this tool - my first observation is can you try and make the path you list for  isdescendantnode a deeper JCR path - see if that helps. 

Avatar

Level 4

Hey Scott,

The problem I have is that once it gets under apps, things are all dynamic ... at least projects and page names will be different.

/apps/somename-1/components/page/main/page/cq:dialog/content/items/tabs/items/article/items/articleTitle/rtePlugins

/apps/somename-2/components/page-a/main/component-4/cq:dialog/content/items/tabs/items/article/items/articleTitle/rtePlugins

/apps/somename-3/components/page1/main/component-6/cq:dialog/content/items/tabs/items/article/items/articleTitle/rtePlugins

-Dean

Avatar

Level 10

I ran this same query and its picking up all of the nodes -- see:

What JCR JAR are you using? 

Avatar

Level 10

This code is picking up all nodes for me. I am getting a few other super users to test too. I have included: 

jackrabbit-standalone-2.6.5.jar

Code: 

import javax.jcr.Repository;
import javax.jcr.Session;
import javax.jcr.SimpleCredentials;
import javax.jcr.Node;
import javax.jcr.query.Query;


import org.apache.jackrabbit.commons.JcrUtils;

 

public class TestJCR {


    public static void main(String [] args)
    {


        try {

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

 

            //Create a Session
            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();

            //Specify the AEM JCR path where examples users are stored at /home/users/test

            String sqlStatement = "select * from [nt:unstructured] as p where isdescendantnode (p, [/apps]) AND NAME(p)='rtePlugins'";


            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.NodeIterator nodeIter = result.getNodes();

            long mySize = nodeIter.getSize();

            while ( nodeIter.hasNext() ) {

                //For each node-- get the path of the node
                javax.jcr.Node node = nodeIter.nextNode();

                String myPath = node.getPath();

                System.out.println(myPath);
            }

            // Save the session changes and log out
            session.save();
            session.logout();


        }
        catch(Exception e){
            e.printStackTrace();
        }

    }

 

}

Avatar

Level 10

It picks up these: 

/apps/touchRTE/components/text/cq:editConfig/cq:inplaceEditing/config/rtePlugins
/apps/touchRTE/components/text/dialog/items/tab1/items/text/rtePlugins
/apps/vmwaredevapp/components/content/hcontentcard/cq:dialog/content/items/image/items/column/items/body/rtePlugins
/apps/vmwaredevapp/components/content/hcontentcard/dialog/items/basic/items/body/rtePlugins
/apps/geometrixx-media/components/welcome-message/dialog/items/tab1/items/description/rtePlugins
/apps/geometrixx-gov/components/image/dialog/items/text/rtePlugins
/apps/geometrixx-gov/components/text/dialog/items/tab1/items/text/rtePlugins

 

 

which is correct. 

Avatar

Level 10

As discussed, one way is to process the result set and place the paths that meet your requirements into a collection. 

Avatar

Correct answer by
Level 3

Append your query with this:

AND [jcr:path] like '%/cq:dialog/%'

Avatar

Level 4

Thank you Scott for all your help!!!!!

I can use the JCR API to make this work ... Antons solution will allow me to do this just using one query.

select * from [nt:unstructured] as p where isdescendantnode (p, [/apps]) AND [jcr:path] like '%cq:dialog%' AND NAME(p) LIKE 'rtePlugins'