Body: <p>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 (<code>topic</code> and <code>subject</code>) with it. This is my working code to find the related entries:</p>
  <pre><code>{% set related_offers_query = craft.entries()
      .type(entry.type.handle)
      .limit(8)
      .id(&quot;not #{entry.id}&quot;) %}
  {% 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() %}
  </code></pre>
  <p>This will match all entries that share at least one category with the current entry, either in the <code>topic</code> or <code>subject</code> fields. I would like to order the results by the <strong>number of shared categories</strong> 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 <strong>retrieving all results and sorting in-memory is not an option</strong>. I'd need a way to solve this in the query itself.</p>
  <p>I guess this is not possible using the built-in orderBy syntax? So I would have to pass a custom <a href="https://www.yiiframework.com/doc/api/2.0/yii-db-expression" rel="nofollow noreferrer">Expression</a> 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!</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-08-26T15:56:46.833Z
Comments.Text: By _retrieving all results and sorting in-memory is not an option_ do you mean no Twig tricks ™ ? ;)
Comments.UserDisplayName: Oli
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-08-27T07:29:33.263Z
Comments.Text: @Oli Yes ^^ Sorting in Twig afterwards would be trivial, but isn't a good idea in my case. This is for a 'related pages' feed where there are a couple thousand potential pages to display, but I only need to display  ~8 pages. So fetching thousands of pages only to sort them manually is not feasible.
Comments.UserDisplayName: MoritzLost
Tags: <relations><query><mysql><ordering><sorting>
Title: Order element query by number of related categories
ThreadText: # Order element query by number of related categories
  
  (Asked by: username_0 on 2021-08-18)
  
  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](https://www.yiiframework.com/doc/api/2.0/yii-db-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!
  
  **Tags:** relations, query, mysql, ordering, sorting
  
  > **Comments:**
  > **username_1**: By _retrieving all results and sorting in-memory is not an option_ do you mean no Twig tricks ™ ? ;)  
  > **username_0**: @username_1 Yes ^^ Sorting in Twig afterwards would be trivial, but isn't a good idea in my case. This is for a 'related pages' feed where there are a couple thousand potential pages to display, but I only need to display  ~8 pages. So fetching thousands of pages only to sort them manually is not feasible.  
  
  ---
  
  ## 0 answers
  
  