How to do fulltext search from cq:Page and dam:Asset in single query

[codingStar]

I am able to do search in cq:Page and dam:Asset using query builder (refer below )

1_group.1_group.path=/content/mywebsite/dashboard

1_group.2_group.path=/content/dam/mywebsite

1_group.p.or=true

2_group.1_group.type = cq:Page

2_group.2_group.type = dam:Asset

2_group.p.or=true

3_group.2_group.fulltext =mysearchKeyword

How I can so the same search in SQL2 query.?

[aemmarc · Accepted solution]

1. Create a DEBUG logger for org.apache.jackrabbit.oak.query

2. tail that log

3. run your query builder query above and see what gets logged. It will log both the xpath and jcr-sql equivalent

select [jcr:path], [jcr:score], * from [cq:Page] as a where contains(*, 'mysearchKeyword') and isdescendantnode(a, '/content/mywebsite/dashboard) union select [jcr:path], [jcr:score], * from [cq:Page] as a where contains(*, 'mysearchKeyword') and isdescendantnode(a, '/content/dam/mywebsite') union select [jcr:path], [jcr:score], * from [dam:Asset] as a where contains(*, 'mysearchKeyword') and isdescendantnode(a, '/content/mywebsite/dashboard') union select [jcr:path], [jcr:score], * from [dam:Asset] as a where contains(*, 'mysearchKeyword') and isdescendantnode(a, '/content/dam')