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
Solved! Go to Solution.
Topics help categorize Community content and increase your ability to discover relevant content.
Views
Replies
Total Likes
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.
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.
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.
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)
Views
Likes
Replies
Views
Likes
Replies
Views
Likes
Replies