Expand my Community achievements bar.

Guidelines for the Responsible Use of Generative AI in the Experience Cloud Community.
SOLVED

SQL2 Help

Avatar

Level 5

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 5

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 5

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'