Ordering result by an aggregated value that comes from a filtered relation result

[dreamy plank]

Hi! I'm trying to sort a result by an aggregated field (_count) that comes from a related table which should be filtered by some conditions.
It looks like includes runs after orderBy, because my order is not affected by the include ... where condition.
This is the generated query (where true 🤔 ):

LEFT JOIN
  (SELECT "public"."related_table"."id",
          COUNT(*) AS "orderby_aggregator"
   FROM "public"."related_table"
   WHERE 1=1 

This is the include statement:

include: {
        <relatedTable>: {
          where: {
            <field>: <condition>,
          },
        }
      },

Order by:

orderBy: {
        <relatedTable>: {
          _count: 'desc',
        },
      },

_count will include items that are excluded in the include statement ( 😅 ) and affect the orderBy.

Am I missing something? Or my whole idea is wrong? Is there a workaround for this? 😰

Thank you! 🙇‍♂️

[elder niche]

Hi Prisma doesn't directly support sorting by the count of a filtered relation.

However, you can perform a groupBy query on the related table with the filter conditions you need. After you get the counts, you can map over these results to create an array of IDs that meet your conditions. Then, you can use a findMany query and order your main table by whether the IDs are in the array you created, using a notIn condition to sort them