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
BedrockMission!

Learn more

View all

Sign in to view all badges

SOLVED

How to sort query results by combining multiple fields(as single field)?

HariharanKabc
Level 1
Level 1

Hi Everyone, 

 

We have a use case where we need to sort a result by different fields. E.g. In an existing system we have fields named as countryLang or lang or language in different components, we are introducing some enhancements by giving search and sort option to the system. All these fields have same set of data only, we need to sort the result set by considering all these fields of the components. Is there any way to provide a single alias name in Lucene index and query so that all these fields are considered as same? please let me know if there is any other approach to tackle the problem. It is an existing system with huge content so changing field name is not an option.

 

E.g

Comp 1 countryLang - en_US

Comp 2 countryLang - fr_FR

Comp 3 lang - en_US

Comp 4 language - de_DE

 

Expected sort order:

Comp 4 language - de_DE

Comp 1 countryLang - en_US

Comp 3 lang - en_US

Comp 2 countryLang - fr_FR

index indexing query Query Parameter querybuilder
1 Accepted Solution
Vijayalakshmi_S
Correct answer by
Community Advisor
Community Advisor

Hi @HariharanKabc,

We can make use of coalesce function in JCR-SQL2 query. 

This is explained with an example scenario + query in this blog - https://exadel.com/news/aem-tip-merging-on-fields-with-different-names/

 

Please check and update this thread if you have issues implementing the same for your use case. 

View solution in original post

3 Replies
Vijayalakshmi_S
Correct answer by
Community Advisor
Community Advisor

Hi @HariharanKabc,

We can make use of coalesce function in JCR-SQL2 query. 

This is explained with an example scenario + query in this blog - https://exadel.com/news/aem-tip-merging-on-fields-with-different-names/

 

Please check and update this thread if you have issues implementing the same for your use case. 

View solution in original post

HariharanKabc
Level 1
Level 1

Hi @Vijayalakshmi_S ,

Thanks for the solution. It is perfectly working for two properties. In our use case we have multiple property names like lang, countryLang, language etc. is there any way to accommodate multiple properties names as well? with some regex or wildcard.

Vijayalakshmi_S
Community Advisor
Community Advisor

Hi @HariharanKabc,

There is no explicit mention about using column/field names with regex in JCR-SQL2 grammar official docs - http://jackrabbit.apache.org/oak/docs/query/grammar-sql2.html

Also, could see that Coalesce function is not accepting more than 2 arguments/field names. 

Given this, I tried something like below (nested coalesce) and it works fine for me. Suggest to try this on similar lines and see if it works for you. 

SELECT * FROM [nt:unstructured] AS comp WHERE ISDESCENDANTNODE(comp, '/content/demo/language-masters/en/articles') AND comp.[sling:resourceType] = 'demoproject/components/content/article'
UNION
SELECT * FROM [nt:unstructured] AS comp WHERE ISDESCENDANTNODE(comp, '/content/demo/language-masters/en/reports') AND comp.[sling:resourceType] = 'demoproject/components/content/report'
UNION
SELECT * FROM [nt:unstructured] AS comp WHERE ISDESCENDANTNODE(comp, '/content/demo/language-masters/en/news') AND comp.[sling:resourceType] = 'demoproject/components/content/news'
ORDER BY COALESCE(COALESCE(articleDate,reportDate),newsPublishedDate)