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

[B_Stockwell]

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.

[arunpatidar]

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

[B_Stockwell]

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 · Accepted solution]

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.

[B_Stockwell]

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.