This message was deleted Prisma #orm-help

Join Slack

This message was deleted.

# orm-help

Slackbot

11/09/2021, 2:36 AM

This message was deleted.

Ryan

11/09/2021, 6:13 AM

@Daniel Marques 👋 This query will return the user as well as the count. If you would like to return just the count, then you can store the value in a variable and return the count instead.

Maciek K

11/09/2021, 9:33 AM

I think what he means (I may be wrong) is how to query the count alongside other fields (like from a client such as Apollo Client). I don't know TypeGraphQl but in Nexus I'm declaring count on a model like this (using your users and ratings):

Copy code

export const Account = objectType({
  name: "User",
  definition(t) {
    t.nonNull.string("id");
    t.nonNull.string("name");
    t.field("ratingsCount", {
      type: "Int",
      resolve: (parent, _, ctx) =>
        ctx.prisma.rating.count({ where: { userId: parent.id } }),
    });
  },
});

Then I can query it like:

Copy code

query Users {
   id
   name
   ratingsCount
}

But I'm not sure if that's the proper and performant way to do it. Btw you would want to make that email field on user

@unique

and query the user by

.findUnique()

Maciek K

11/09/2021, 10:07 AM

I'm checking prisma logs and it looks like it does the query count for each record when using my technique. Is there a better way to do this in Nexus in order to maybe take advantage of some dataloader batching? @Ryan?

Maciek K

11/09/2021, 10:38 AM

To be more precise with what I mean. My model simplified:

Copy code

type Account {
  id
  rooms Room[]
}

type Room {
  id        String    @id @default(cuid()) @db.VarChar(30)
  accountId String    @db.VarChar(30)
  account   Account   @relation(fields: [accountId], references: [id])
}

Nexus:

Copy code

export const Account = objectType({
  name: "Account",
  definition(t) {
    t.nonNull.string("id");
    t.field("roomsCount", {
      type: "Int",
      resolve: (parent, _, ctx) =>
        ctx.prisma.room.count({ where: { accountId: parent.id } }),
    });
  },
});

Querying from graphql playground:

Copy code

query accounts {
   id
   roomsCount
}

I get sql query from prisma logs ( I have two accounts):

Copy code

prisma:query SELECT "booking$dev"."Account"."id" FROM "booking$dev"."Account" WHERE 1=1 OFFSET $1
prisma:query SELECT COUNT(*) FROM (SELECT "booking$dev"."Room"."id" FROM "booking$dev"."Room" WHERE "booking$dev"."Room"."account" = $1 OFFSET $2) AS "sub"
prisma:query SELECT COUNT(*) FROM (SELECT "booking$dev"."Room"."id" FROM "booking$dev"."Room" WHERE "booking$dev"."Room"."account" = $1 OFFSET $2) AS "sub"

That's two count queries for two accounts. When doing a prisma query (not by graphql):

Copy code

ctx.prisma.account.findMany({ select: { id: true, _count: { select: { rooms: true } } } });

I get:

Copy code

prisma:query SELECT "booking$dev"."Account"."id", "aggr_selection_0_Room"."_aggr_count_rooms" FROM "booking$dev"."Account" LEFT JOIN (SELECT "booking$dev"."Room"."account", COUNT(*) AS "_aggr_count_rooms" FROM "booking$dev"."Room" GROUP BY "booking$dev"."Room"."account") AS "aggr_selection_0_Room" ON ("booking$dev"."Account"."id" = "aggr_selection_0_Room"."account") WHERE 1=1 OFFSET $1

I'm not an SQL expert by any means. Is the latter more performant (with thousands of accounts) ?? Or should I not worry about this at all and difference will be neglectible?

2 Views

Open in Slack

Previous Next