Query Builder Order by issue

[neerajg29767140]

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>

[edubey]

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.

[neerajg29767140]

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.

[cquser1]

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

[neerajg29767140]

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