Making duel queries in prisma client

user_16790110 0
Viewed 48

I'm currently wondering if its possible to get data from more than one table in a single query?

We have a teamMember table, and a user table. We want to fetch information on each user in the teamMember table, and get the corresponding data from the user table.

Is this possible to do in one query? Or would I have to use two findMany queries?

const members = await prisma.teamMember.findMany({
where: {
      teamId,
    },
  });

  const membersInfo = [];

  members.map(async (e) => {
    const response = await prisma.user.findFirst({
      where: {
        id: e.userId,
      },
    });
    if(response) membersInfo.push(response);
  });```
prisma javascript
1 Answers

Yes, you could query User data while fetching TeamMember information.

Consider this example:

schema.prisma

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

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

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

model TeamMember {
  id      Int   @id @default(autoincrement())
  team_id Int
  User    User? @relation(fields: [userId], references: [id])
  userId  Int?
}

model User {
  id       Int          @id @default(autoincrement())
  name     String
  email    String
  password String
  team     TeamMember[]
}

index.ts

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

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

async function main() {
  await prisma.user.create({
    data: {
      email: 'test@test.com',
      name: 'test',
      password: 'test',
      team: {
        create: {
          team_id: 1,
        },
      },
    },
  });

  console.log('Created user');

  const teamWithUsers = await prisma.teamMember.findUnique({
    where: {
      id: 1,
    },
    include: {
      User: true,
    },
  });

  console.log('teamWithUsers', teamWithUsers);
}

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

Here's the response:

teamWithUsers {
  id: 1,
  team_id: 1,
  userId: 1,
  User: { id: 1, name: 'test', email: 'test@test.com', password: 'test' }
}

By default relation fields are not fetched, if you need to get the relation fields data in that case you would need to specify the include clause as demonstrated in the above example.

user_41540620
Related