Improving a query, ordering by multiple items in query--not seeing a difference. | Community
Skip to main content
B
BenSt10
August 2, 2022
Solved

Improving a query, ordering by multiple items in query--not seeing a difference.

  • August 2, 2022
  • 1 reply
  • 1158 views

Hi Everyone,

 

I am working on a project improving the search results for a "related stories" component in our news system. Here is a simplified query that shows how our current search functions.

 

path=/content/uc/news/articles type=cq:Page tagid.1_value=topics:student-experience tagid.2_value=colleges:nursing tagid.3_value=helpful-marketing:current-bearcats tagid.4_value=helpful-marketing:student-life tagid.property=@jcr:content/cq:tags tagid.property.and=false orderby=@jcr:content/dispDate orderby.sort=desc

 

You can see the draw back, it's finding any stories with any tag and sorting by the display date, which means that a recent story that maybe only shares one tag would appear before another older story which could share all of the tags.

 

By changing the orderby parameter to jcr:score I can already see an improvement, however, since we have something like 32,000 news stories over 20+ years, basing it on just the score could lead to a situation where an older story that has the same score as a younger story would appear first.

path=/content/uc/news/articles type=cq:Page tagid.1_value=topics:student-experience tagid.2_value=colleges:nursing tagid.3_value=helpful-marketing:current-bearcats tagid.4_value=helpful-marketing:student-life tagid.property=@jcr:content/cq:tags tagid.property.and=false orderby=@jcr:score orderby.sort=desc

 

So I have tried using multiple order by clauses, as show below, but this query behaves the same as my original search: stories with the more recent display date are always appearing first. Swapping hte 1_ and 2_ prefix has no effect

path=/content/uc/news/articles type=cq:Page tagid.1_value=topics:student-experience tagid.2_value=colleges:nursing tagid.3_value=helpful-marketing:current-bearcats tagid.4_value=helpful-marketing:student-life tagid.property=@jcr:content/cq:tags tagid.property.and=false 2_orderby=@jcr:content/dispDate 2_orderby.sort=desc 1_orderby=@jcr:score 1_orderby.sort=desc

.

All of these have been tested in the querydebugger and the xpath generated does contain both order by statements. Swapping 1 and 2 does rearrange the xpath order by but has no effect on the results.

 

Is there an additional statement I can use to sort this the way I'm aiming to?

 

Alternatively, is there a better way to do a best match style search?

 

Thanks.

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 arunpatidar

Hi @benst10 
With AEM Queries, it is not possible to combine order column, you can have only one column for order predicate.

I think with custom code, you can order the the resultset before exporting.

e.g. https://stackoverflow.com/questions/22780347/order-by-based-on-a-expression 

 

I am not sure if creating custom predicate would help here.

1 reply

arunpatidar
Community Advisor
arunpatidarCommunity Advisor
Community Advisor
August 3, 2022

@benst10 
I was not able to understand what should be the sorting criteria for the results, Its a date? or number of tags?

Could you please explain what should be the ideal sorting order you are looking for?

This will help everyone here to provide correct guidance.

Arun Patidar
B
BenSt10Author
August 3, 2022

HI Arun,

 

Apologies for not being clear.

 

We want more or less a "best match" that would first sort by # tags matched, then a subsort by display date

 

1st sort: # of matching tags desc

2nd sort: display date desc

 

For 7 stories:

  • Story A: tags: a,c | displayDate: 4/1/22
  • Story B: tags: a,b,c | displayDate: 7/1/22
  • Story C: tags: a,b,c | displayDate: 3/1/22
  • Story D/: tags: a,b,c | displayDate: 6/1/22
  • Story E: tags: a,c | displayDate: 8/1/22
  • Story F: tags: c | displayDate: 5/1/22
  • Story G: tags: d | displayDate: 1/1/22

I would like them to appear in this order in a query for tags a,b,c:

  1. Story B (matched 3 tags, most recent of those with 3 matched)
  2. Story D (matched 3 tags, 2nd most recent of those with 3 matched)
  3. Story C (matched 3 tags, 3rd most recent of those with 3 matched)
  4. Story E (matched 2 tags, most recent with 2 tags)
  5. Story A (matched 2 tags, 2nd most recent with 2 tags)
  6. Story F (Matched 1 tag)
  7. (G would not appear in the results as it matched no tags)

I have been toying with a lucene search for similarity with mixed results but will keep trying. Any advice is appreciated.

arunpatidar
Community Advisor
arunpatidarCommunity AdvisorAccepted solution
Community Advisor
August 3, 2022

Hi @benst10 
With AEM Queries, it is not possible to combine order column, you can have only one column for order predicate.

I think with custom code, you can order the the resultset before exporting.

e.g. https://stackoverflow.com/questions/22780347/order-by-based-on-a-expression 

 

I am not sure if creating custom predicate would help here.

Arun Patidar
Terms & ConditionsAccessibility statement

Loading footer...