Select only particular pages of a template in AEM if that component exist in that page

Question

I have a template in AEM where I have to select all pages for the template where the component is present in pages.

Current query:

SELECT * FROM [cq:PageContent] AS page
WHERE  ISDESCENDANTNODE(page, '/content')
AND [cq:template]="/conf/we-retail/settings/wcm/templates/experience-page"

This returns all pages but I have to return the selective pages. For example, currently, there are 2 text components present in the template, and one of the text components is unlocked and modified on page P1. Now that component is created on Page p1.

I have to return that P1 page in the result

Answer 1

You could achieve this with a join:

SELECT page.*
FROM [nt:unstructured] AS component
INNER JOIN [cq:PageContent] AS page
ON ISDESCENDANTNODE(component, page)
WHERE component.[sling:resourceType] = 'your/text/node/resource/type/here'
AND ISDESCENDANTNODE(page, '/content')
AND page.[cq:template]="/conf/we-retail/settings/wcm/templates/experience-page"

However, this could be a slow query unless you have all the right indexes in place. Also, it will return duplicate entries for pages that contain multiple instances of this component (I tried using SELECT DISTINCT instead but it didn’t help).