How to sort query results by combining multiple fields(as single field)? | Community
Skip to main content
H
HariharanKabc
August 16, 2021
Solved

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

  • August 16, 2021
  • 1 reply
  • 2340 views

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

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Vijayalakshmi_S

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. 

1 reply

Vijayalakshmi_S
Vijayalakshmi_SAccepted solution
Level 10
August 16, 2021

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. 

    H
    HariharanKabcAuthor
    August 19, 2021

    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
      Vijayalakshmi_S
      Level 10
      August 19, 2021

      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)
        Terms & ConditionsAccessibility statement

        Loading footer...