Drizzle Team•14mo ago

Help migrating raw query from prisma to drizzle

Trying to get the below query which has multiple joins into drizzle. It's one of the last ones I have and I am not sure if I should go the route of query or select. Query didnt have or show any docs on joins. Whats the optimal way to do this and is it possible to do also the count in the same query or will that still have to be an extra or separate?

  const results: any = await prisma.$queryRaw`
    SELECT COUNT(*) OVER()::integer AS count, 
             u.id as "userId",
       u."name", 
       u."plan",
       s.id AS "serviceId", 
       s."createdAt", 
       s."updatedAt", 
       s."name", 
       s."proposals" AS "serviceProposals",
       s."ack", 
       d."type",
             d."data"
    FROM "User" u
    LEFT JOIN "Directive" d ON u."userId" = d."id"
    LEFT JOIN "Service" s ON d."serviceId" = s."id"
    WHERE s."ack" = false
    AND u."plan" IN (${Prisma.join(plans)})
        ORDER BY s."createdAt" ASC
    LIMIT ${take} OFFSET ${skip}
  `;

  const results: any = await prisma.$queryRaw`
    SELECT COUNT(*) OVER()::integer AS count, 
             u.id as "userId",
       u."name", 
       u."plan",
       s.id AS "serviceId", 
       s."createdAt", 
       s."updatedAt", 
       s."name", 
       s."proposals" AS "serviceProposals",
       s."ack", 
       d."type",
             d."data"
    FROM "User" u
    LEFT JOIN "Directive" d ON u."userId" = d."id"
    LEFT JOIN "Service" s ON d."serviceId" = s."id"
    WHERE s."ack" = false
    AND u."plan" IN (${Prisma.join(plans)})
        ORDER BY s."createdAt" ASC
    LIMIT ${take} OFFSET ${skip}
  `;

1 Reply

imobyOP•14mo ago

As part of this I'd love to also know how to rename column names

Gaming

Programming

Help migrating raw query from prisma to drizzle