2

Given a Prisma schema (version 3.7.0) that looks like this:

model Category {
  id           String @id @default(cuid())
  name         String
  transactions Transaction[]
}

model Transaction {
  id          String @id @default(cuid())
  date        Int
  category    Category @relation(fields: [categoryId], references: [id])
  categoryId  String
  amount      Float
  notes       String? @db.LongText
}

I want to basically write the following query using Prisma:

SELECT
  t.categoryId,
  c.name,
  c.income,
  SUM(t.amount) AS total
FROM Transaction t
  JOIN Category c ON t.categoryID=c.id
WHERE t.date >= <start date> AND t.date < <end date>
GROUP BY t.categoryId;

I know I can aggregate on the Category table with something like this:

prisma.transaction.groupBy({
  by: ['categoryId'],
  _sum: { amount: true },
  where: {
    AND: [
      { date: { gte: startDate } },
      { date: { lt: endDate } },
    ]
  },
});

But I can't figure out how to include the name column from the Category table.

I saw this question which asks for a solution to this problem. In this question, the author writes

I'm not exactly sure how/whether I can achieve something similar with Prisma, because as it stands right now, it seems like this would require two separate queries, which isn't optimal because there is sometimes the need for 2 or 3 joins or SELECTs from other tables.

The accepted answer to the question goes ahead & solves the problem with two separate queries, so I'm left wondering if there is a way to do this in one query.

Kryten
  • 15,230
  • 6
  • 45
  • 68
  • Your GROUP BY query is invalid and expected to raise an error. You probably want a correct query instead, but for Prisma. – jarlh Feb 17 '22 at 19:40
  • @jarlh Which are you referring to, the SQL or the Prisma query and in what way? Both work in my local env and with a small set of sample data; I should mention that I'm using MySQL and inserting valid date values in the WHERE clause, in case that makes a difference. – Kryten Feb 17 '22 at 19:55
  • Either you're running an very old version, or has ONLY_FULL_GROUP_BY disabled (i.e. in compatibility mode.) – jarlh Feb 17 '22 at 20:06
  • Interesting... according to the MySQL docs, it's enabled by default in v5.7 but disabled in previous versions. I'm using MariaDB 10.6 which, although fairly recent, appears not to have added this change. – Kryten Feb 17 '22 at 21:21
  • That's a bit surprising. I thought MariaDB was pretty close to MySQL. However, as a SQL puritan, I'd consider turning ONLY_FULL_GROUP_BY on. But I also realize it can cause compatibility problems. – jarlh Feb 17 '22 at 21:34
  • Ok, so I suspect the answer to this question is "you can't do that in Prisma because it's not valid SQL". I'm going to dig around in the docs & see if I can't find a definitive answer. Thanks for your help @jarlh – Kryten Feb 17 '22 at 21:38
  • Something like that. – jarlh Feb 17 '22 at 21:40

0 Answers0