Hey everyone, Question and I may just be reading the documentation wrong. Is there a way to sort a query on a relationship, passing a where clause to the relationship? (or a work around that does not mean dropping to a rawQuery to do that?) Example would be like, If I have books, and books have reviews, reviews are tied to a user. I want a list of books, sorted by whether I have reviewed them or not (ie by the reviews table where userid = mine)

Hello @Hayley Dawson 👋 Considering this schema file:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["filteredRelationCount"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id        Int       @id @default(autoincrement())
  createdAt DateTime  @default(now()) @map("created_at")
  updatedAt DateTime  @updatedAt @map("updated_at")
  name      String
  email     String
  Reviews   Reviews[]

  @@map("user")
}

model Books {
  id        Int       @id @default(autoincrement())
  createdAt DateTime  @default(now()) @map("created_at")
  updatedAt DateTime  @updatedAt @map("updated_at")
  name      String
  author    String
  Reviews   Reviews[]

  @@map("books")
}

model Reviews {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")
  bookId    Int
  book      Books    @relation(fields: [bookId], references: [id])
  rating    Int
  comment   String
  userId    Int
  user      User     @relation(fields: [userId], references: [id])

  @@map("reviews")
}

Would something like this work in your case? Script:

import { PrismaClient, Prisma } from '@prisma/client';

const prisma = new PrismaClient({
  log: ['query', 'info', 'warn'],
});

async function main() {
  await prisma.books.create({
    data: {
      author: 'John Doe',
      name: 'My Book',
      Reviews: {
        create: {
          comment: 'This is a comment',
          rating: 5,
          user: {
            create: {
              email: '<mailto:jane@prisma.io|jane@prisma.io>',
              name: 'Jane',
              id: 1,
            },
          },
        },
      },
    },
  });

  const response = await prisma.books.findMany({
    where: {
      Reviews: {
        every: {
          userId: 1, // My user id
        },
      },
    },
    orderBy: {
      Reviews: {
        _count: 'asc',
      },
    },
  });

  console.log(response);
}

main()
  .catch((e) => {
    throw e;
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

Response:

> ts-node index.ts

prisma:info Starting a postgresql pool with 0 connections.
prisma:query BEGIN
prisma:query INSERT INTO "public"."books" ("created_at","updated_at","name","author") VALUES ($1,$2,$3,$4) RETURNING "public"."books"."id" /* traceparent=00-00-00-00 */
prisma:query INSERT INTO "public"."user" ("id","created_at","updated_at","name","email") VALUES ($1,$2,$3,$4,$5) RETURNING "public"."user"."id" /* traceparent=00-00-00-00 */
prisma:query INSERT INTO "public"."reviews" ("created_at","updated_at","bookId","rating","comment","userId") VALUES ($1,$2,$3,$4,$5,$6) RETURNING "public"."reviews"."id" /* traceparent=00-00-00-00 */
prisma:query SELECT "public"."books"."id", "public"."books"."created_at", "public"."books"."updated_at", "public"."books"."name", "public"."books"."author" FROM "public"."books" WHERE "public"."books"."id" = $1 LIMIT $2 OFFSET $3 /* traceparent=00-00-00-00 */
prisma:query COMMIT
prisma:query SELECT "public"."books"."id", "public"."books"."created_at", "public"."books"."updated_at", "public"."books"."name", "public"."books"."author" FROM "public"."books" LEFT JOIN (SELECT "public"."reviews"."bookId", COUNT(*) AS "orderby_aggregator" FROM "public"."reviews" WHERE 1=1 GROUP BY "public"."reviews"."bookId") AS "orderby_0_Reviews" ON ("public"."books"."id" = "orderby_0_Reviews"."bookId") WHERE ("public"."books"."id") NOT IN (SELECT "t0"."id" FROM "public"."books" AS "t0" INNER JOIN "public"."reviews" AS "j0" ON ("j0"."bookId") = ("t0"."id") WHERE ((NOT "j0"."userId" = $1) AND "t0"."id" IS NOT NULL)) ORDER BY COALESCE("orderby_0_Reviews"."orderby_aggregator", $2) ASC OFFSET $3 /* traceparent=00-00-00-00 */
[
  {
    id: 6,
    createdAt: 2022-10-14T06:35:52.456Z,
    updatedAt: 2022-10-14T06:35:52.458Z,
    name: 'My Book',
    author: 'John Doe'
  }
]

that might i will dig into it today. thank you for the help!