Get SearchResult list using SQL2

[wizard04wsu]

I have a SQL2 query that returns the pages that include content matching my search terms. I'm using

javax.jcr.query.QueryResult to get the results. I'd like to use

com.day.cq.search.result.SearchResult so I have excerpts to use. How can I convert this SQL2 query to predicates? Or is there a better way to go about this?

SELECT DISTINCT page.*

FROM [cq:Page] AS page

INNER JOIN [cq:PageContent] AS content

    ON ISCHILDNODE(content, page)

INNER JOIN [nt:base] AS node

    ON ISDESCENDANTNODE(node, content)

WHERE ISDESCENDANTNODE(page, [/content/mysite/en])

AND CONTAINS(node.*, 'mysearchterm')";

[h_kataria · Accepted solution]

I am not able to fully understand what you trying to do with those INNER JOINS but if its just a fulltext search then you can easily convert it to predicates like

path=/content/mysite/en
fulltext=mysearchterm

https://experienceleague.adobe.com/en/docs/experience-manager-65/content/implementing/developing/pla... 

[wizard04wsu]

I'm using the inner joins to get the list of pages that contain the hits, not the hits themselves. However, I realize now that this wouldn't get me an excerpt to go along with a page anyway.