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

Adobe Summit 2023 [19th to 23rd March, Las Vegas and Virtual] | Complete AEM Session & Lab list

Query Builder Order by issue

Avatar

Level 2

Hi All,

I am facing an issue in query builder debug tool where order by is not working with below query format.

Query 1:

1_group.1_group.path=/content/we-retail

1_group.2_group.path=/content/dam/we-retail/en

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 = businies

3_group.1_group.1_group.fulltext = consumer

3_group.1_group.2_group.fulltext = marketing

3_group.1_group.p.or=true

3_group.p.or=true

orderby = @jcr:content/jcr:lastModified

orderby.sort = desc

group.p.or=true

Query formation:

ROOT=group: [

    {orderby=orderby: orderby=@jcr:content/jcr:lastModified, sort=desc}

    {1_group=group: or=true[

        {1_group=group: [

            {path=path: path=/content/we-retail}

        ]}

        {2_group=group: [

            {path=path: path=/content/dam/we-retail/en}

        ]}

    ]}

    {2_group=group: or=true[

        {1_group=group: [

            {type=type: type=cq:Page}

        ]}

        {2_group=group: [

            {type=type: type=dam:Asset}

        ]}

    ]}

    {3_group=group: or=true[

        {1_group=group: or=true[

            {1_group=group: [

                {fulltext=fulltext: fulltext=consumer}

            ]}

            {2_group=group: [

                {fulltext=fulltext: fulltext=marketing}

            ]}

        ]}

        {2_group=group: [

            {fulltext=fulltext: fulltext=businies}

        ]}

    ]}

    {4_group=group: or=true[

    ]}

]

Query 2:

1_group.1_group.path=/content/we-retail

1_group.2_group.path=/content/dam/we-retail

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 = business

3_group.1_group.1_group.fulltext = consumer

3_group.1_group.2_group.fulltext = marketing

3_group.1_group.p.or=true

3_group.p.or=true

orderby = @jcr:content/cq:lastModified

orderby.sort = desc

Query formation:

(/jcr:root/content/we-retail//element(*, cq:Page)

[

(jcr:contains(., 'consumer'))

] | /jcr:root/content/dam/we-retail//element(*, cq:Page)[(jcr:contains(., 'consumer'))] | /jcr:root/content/we-retail//element(*, dam:Asset)[(jcr:contains(., 'consumer'))] | /jcr:root/content/dam/we-retail//element(*, dam:Asset)[(jcr:contains(., 'consumer'))] | /jcr:root/content/we-retail//element(*, cq:Page)[(jcr:contains(., 'marketing'))] | /jcr:root/content/dam/we-retail//element(*, cq:Page)[(jcr:contains(., 'marketing'))] | /jcr:root/content/we-retail//element(*, dam:Asset)[(jcr:contains(., 'marketing'))] | /jcr:root/content/dam/we-retail//element(*, dam:Asset)[(jcr:contains(., 'marketing'))] | /jcr:root/content/we-retail//element(*, cq:Page)[(jcr:contains(., 'business'))] | /jcr:root/content/dam/we-retail//element(*, cq:Page)[(jcr:contains(., 'business'))] | /jcr:root/content/we-retail//element(*, dam:Asset)[(jcr:contains(., 'business'))] | /jcr:root/content/dam/we-retail//element(*, dam:Asset)[(jcr:contains(., 'business'))])

Debug logs:

Caused by: java.text.ParseException: (//element(*, cq:Page)[(jcr:contains(., 'marketing'))] | //element(*, cq:Page)[(jcr:contains(@jcr:title, 'consumer'))] | //element(*, cq:Page)[(jcr:contains(@jcr:description, 'business'))] | //element(*, dam:Asset)[(jcr:contains(., 'marketing'))] | //element(*, dam:Asset)[(jcr:contains(@jcr:title, 'consumer'))] | //element(*, dam:Asset)[(jcr:contains(@jcr:description, 'business'))]) order by @jcr:score descending converted to SQL-2 Query: select [jcr:path], [jcr:score], * from [cq:Page] as a where contains(*, 'marketing') /* xpath: //element(*, cq:Page)[(jcr:contains(., 'marketing'))]  order by @jcr:score descending */ union select [jcr:path], [jcr:score], * from [cq:Page] as a where contains([jcr:title], 'consumer') /* xpath: //element(*, cq:Page)[(jcr:contains(@jcr:title, 'consumer'))]  order by @jcr:score descending */ order by [jcr:score] desc union(*)select [jcr:path], [jcr:score], * from [cq:Page] as a where contains([jcr:description], 'business') /* xpath: //element(*, cq:Page)[(jcr:contains(@jcr:description, 'business'))]  order by @jcr:score descending */ order by [jcr:score] desc union select [jcr:path], [jcr:score], * from [dam:Asset] as a where contains(*, 'marketing') /* xpath: //element(*, dam:Asset)[(jcr:contains(., 'marketing'))]  order by @jcr:score descending */ order by [jcr:score] desc union select [jcr:path], [jcr:score], * from [dam:Asset] as a where contains([jcr:title], 'consumer') /* xpath: //element(*, dam:Asset)[(jcr:contains(@jcr:title, 'consumer'))]  order by @jcr:score descending */ order by [jcr:score] desc union select [jcr:path], [jcr:score], * from [dam:Asset] as a where contains([jcr:description], 'business') /* xpath: //element(*, dam:Asset)[(jcr:contains(@jcr:description, 'business'))] order by @jcr:score descending */ order by [jcr:score] desc; expected: <end>

at org.apache.jackrabbit.oak.query.QueryEngineImpl.parseQuery(QueryEngineImpl.java:186)

at org.apache.jackrabbit.oak.query.QueryEngineImpl.executeQuery(QueryEngineImpl.java:253)

at org.apache.jackrabbit.oak.jcr.query.QueryManagerImpl.executeQuery(QueryManagerImpl.java:136)

... 126 common frames omitted

Caused by: java.text.ParseException: Query: select [jcr:path], [jcr:score], * from [cq:Page] as a where contains(*, 'marketing') /* xpath: //element(*, cq:Page)[(jcr:contains(., 'marketing'))]  order by @jcr:score descending */ union select [jcr:path], [jcr:score], * from [cq:Page] as a where contains([jcr:title], 'consumer') /* xpath: //element(*, cq:Page)[(jcr:contains(@jcr:title, 'consumer'))]  order by @jcr:score descending */ order by [jcr:score] desc union(*)select [jcr:path], [jcr:score], * from [cq:Page] as a where contains([jcr:description], 'business') /* xpath: //element(*, cq:Page)[(jcr:contains(@jcr:description, 'business'))]  order by @jcr:score descending */ order by [jcr:score] desc union select [jcr:path], [jcr:score], * from [dam:Asset] as a where contains(*, 'marketing') /* xpath: //element(*, dam:Asset)[(jcr:contains(., 'marketing'))]  order by @jcr:score descending */ order by [jcr:score] desc union select [jcr:path], [jcr:score], * from [dam:Asset] as a where contains([jcr:title], 'consumer') /* xpath: //element(*, dam:Asset)[(jcr:contains(@jcr:title, 'consumer'))]  order by @jcr:score descending */ order by [jcr:score] desc union select [jcr:path], [jcr:score], * from [dam:Asset] as a where contains([jcr:description], 'business') /* xpath: //element(*, dam:Asset)[(jcr:contains(@jcr:description, 'business'))] order by @jcr:score descending */ order by [jcr:score] desc; expected: <end>

0 Replies

Avatar

Level 10

Can you give 1-2 line description of what kind of data you are planning to extract from these 2 queries,

It will help community to understand the problem in easier way.

Avatar

Level 2

I am trying to fetch data from dam and page content where my query will have category, sub category and keyword search and a able to get data but when i try to sort the data based on last modify, my query is failing and error showing up in logs pasted in question.

Avatar

Level 8

Hi,

Just a thought.

Since you are getting the results back without ordering, may be try to narrow down the query to just pages or assets with limited set of results [say 2 or 3] and then try see what happens. This way we can rule out other things that might be causing some issues

Avatar

Level 2

Yes, exactly same i tried earlier, with 1 set and more.below are few examples, it is only working in 1 group and including multiple group with orderby not working.


1.type=cq:Page

orderby=@jcr:content/cq:lastModified


2.

type=cq:Page

orderby=@jcr:content/cq:lastModified

orderby.sort=desc


3.

fulltext=Managemen

group.p.or=true

group.1_path=/content/geometrixx/en/company/management

group.2_path=/content/geometrixx/en/company/bod

4 .

type=cq:Page

1_property=jcr:content/cq:template

1_property.value=/apps/geometrixx/templates/homepage

2_property=jcr:content/jcr:title

2_property.value=English