I'm building an entry template that displays related entries to the current entry. To be related, an entry needs to be the same type as the current entry and share at least one category in one of two category fields (topic and subject) with it. This is my working code to find the related entries:
{% set related_offers_query = craft.entries()
.type(entry.type.handle)
.limit(8)
.id("not #{entry.id}") %}
{% set related_categories = []|merge(entry.topic.ids())|merge(entry.subject.ids()) %}
{% if related_categories is not empty %}
{% do related_offers_query.relatedTo(related_categories) %}
{% endif %}
{% set related_offers = related_offers_query.all() %}
This will match all entries that share at least one category with the current entry, either in the topic or subject fields. I would like to order the results by the number of shared categories so that entries that have the most categories in common with the current entry are displayed first. The number of result candidates (entries that share at least one category with the current entry) is potentially very large, so just retrieving all results and sorting in-memory is not an option. I'd need a way to solve this in the query itself.
I guess this is not possible using the built-in orderBy syntax? So I would have to pass a custom Expression to orderBy? I'd be thankful for any examples for queries like that. I haven't interacted with Craft's low-level database details, so I'm a bit lost on where to start. Thanks!
