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);
});```
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.