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
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

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

Avatar

Level 5

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.

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

Hi @B_Stockwell 
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.

View solution in original post

4 Replies

Avatar

Community Advisor

@B_Stockwell 
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.

Avatar

Level 5

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.

Avatar

Correct answer by
Community Advisor

Hi @B_Stockwell 
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.

Avatar

Level 5

Ok, that clarifies some things. I was hoping this could all be done in the query so I could pass results directly to the front end, but sorting the result set is pretty easy. Thanks.