Prisma: How to select or include on a Group by?

Question

I have a table called financial_transaction which have data like this:

id	debit	credit	financial_type	payment_id	payment	author_id	author
1	150	0	Payment	2	-	1	-
2	0	50	Payment	2	-	1	-

so my desire from this data is to:

Group it by payment_id.
Include true to author.
Sum all the debits and credits.
Display fields like financial_type and payment_id.

So here is what I want:

[
   { 
     payment_id: 2,
     author: {
       first_name: John,
       last_name: Doe
     }
     debit: 150,
     credit: 50,
     financial_type: Payment
   }
]

also here's my Prisma Scehma:

enum FinancialType {
  Payment
  Expense
  Withdraw
}

User Table

model User {
  id                             Int                @id @default(autoincrement())
  first_name                     String
  last_name                      String
  saved_payments                 Payment[]
  saved_financial_transactions FinancialTransaction[]
}

Payment Table

model Payment {
  id                     Int                @id @default(autoincrement())
  payed_price            Decimal
  remaining_price        Decimal?
  price_per_month        Decimal
  financial_transactions FinancialTransaction[]
  author                 User?              @relation(fields: [author_id], references: [id])
  author_id           Int?
}

FinancialTransaction Table

model FinancialTransaction {
  id                  Int                @id @default(autoincrement())
  debit               Decimal
  credit              Decimal
  financial_type      FinancialType
  payment             Payment            @relation(fields: [payment_id], references: [id])
  payment_id          Int
  author              User?              @relation(fields: [author_id], references: [id])
  author_id           Int?

}

I tried to get this with the prisma groupBy like in the following:

const financia_transactions = wait prisma.$queryRaw`select jsonb_agg(to_jsonb(t)) from
        (
          select payment_id,
                  jsonb_build_object('first_name', u.first_name, 'second_name', u.second_name) author,
                  sum(debit) debit, 
                  sum(credit) credit, 
                  max(financial_type) financial_type,
          from "FinancialTransaction" as ft join "User" u on ft.author_id = u.id
          group by payment_id
        ) t;`;

but this is not working... So my question is there a way Prisma can do the trick and get me this?

user_2302032 · Answer

Use a native query.

select jsonb_agg(to_jsonb(t)) from
(
 select payment_id, 
        jsonb_build_object('first_name', u.first_name, 'second_name', u.second_name) author, 
        sum(debit) debit, 
        sum(credit) credit, 
        max(financial_type) financial_type
 from financial_transaction ft join "user" u on ft.author_id = u.id
 group by payment_id
) t;

user_10803535 · Answer

I have done that using the following:

const { PrismaClient } = require('@prisma/client')
const prisma = new PrismaClient()

const execute = async () => {
  const tr = await prisma.financialTransaction.create({
    data: {
      debit: 150,
      credit: 0,
      financialType: 'payment',
      paymentId: 2,
      author: {
        create: {
          name: 'John Doe',
        }
      }
    },
    include: {
      author: true
    }
  })
  await prisma.financialTransaction.create({
    data: {
      debit: 0,
      credit: 50,
      financialType: 'payment',
      paymentId: 2,
      author: {
        connect: {
          id: tr.author.id
        }
      }
    }
  })

  console.log(JSON.stringify(await prisma.financialTransaction.findMany({ include: { author: true } }), null, 2));

  const groupTransactions = await prisma.financialTransaction.groupBy({
    by: ['financialType', 'paymentId', 'authorId'],
    _sum: {
      credit: true,
      debit: true,
    },
  })

  console.log(groupTransactions);
};

execute();

Using this schema

datasource mysql {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

model FinancialTransaction {
  id            Int    @id @default(autoincrement())
  debit         Int
  credit        Int
  financialType String
  paymentId     Int
  author        Author @relation(fields: [authorId], references: [id])
  authorId      Int
}

model Author {
  id           Int                    @id @default(autoincrement())
  name         String
  transactions FinancialTransaction[] @relation
}

and here you go the output

user_10289920 · Answer

It is achievable. First you groupBy then map the result to include the author:

    const groupTransactions = await prisma.financialTransaction.groupBy({
        by: ['financialType', 'paymentId', 'authorId'],
        _sum: {
        credit: true,
        debit: true,
      },
    }).map((d) => {
       const author = await prisma.author.findFirst({where: {id: d.authorId}});
       return {...d, author}
   });

Prisma: How to select or include on a Group by?

3 Answers