Can I use "where" clause on joins ?

Hello ! I'm trying to query some data based on conditions. I know how to do it in prisma but I don't find a solution in drizzle. In Prisma my query looks like:
const modelId = "dummyModelId";
const userId = "dummyUserId";

const data = await prisma.model.findFirst({
where: {
id: modelId,
task: {
dataset: {
userDatasets: {
some: {
userId,
},
},
},
},
},
});
const modelId = "dummyModelId";
const userId = "dummyUserId";

const data = await prisma.model.findFirst({
where: {
id: modelId,
task: {
dataset: {
userDatasets: {
some: {
userId,
},
},
},
},
},
});
In Drizzle I have currenlty this:
const modelId = "dummyModelId";
const userId = "dummyUserId";

const data = await db.query.model.findFirst({
where({id}){
return eq(id, modelId)
}
})
const modelId = "dummyModelId";
const userId = "dummyUserId";

const data = await db.query.model.findFirst({
where({id}){
return eq(id, modelId)
}
})
I've tried this:
const data = await db.query.model.findFirst({
where({id}){
return eq(id, modelId)
},
with:{
task:{
with:{
dataset:{
with:{
userDatasets:{
// 'where' clause not possible here
}
}
}
}
}
}
})
const data = await db.query.model.findFirst({
where({id}){
return eq(id, modelId)
},
with:{
task:{
with:{
dataset:{
with:{
userDatasets:{
// 'where' clause not possible here
}
}
}
}
}
}
})
I would like to use 'where' clause on fields that are in other tables. Thanks for the help ! šŸ˜„
M
Mykhailoā€¢40d ago
Hello, you can, but if there is many relation. https://orm.drizzle.team/docs/rqb#select-filters
Drizzle ORM - Query
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
M
Marcā€¢40d ago
Thanks ! I've done it like that:
const data = await db
.select()
.from(model)
.leftJoin(task, eq(model.taskId, task.id))
.leftJoin(dataset, eq(task.datasetId, dataset.id))
.where(
and(eq(model.id, modelId), eq(dataset.userDatasetId, userDatasetId))
);
const data = await db
.select()
.from(model)
.leftJoin(task, eq(model.taskId, task.id))
.leftJoin(dataset, eq(task.datasetId, dataset.id))
.where(
and(eq(model.id, modelId), eq(dataset.userDatasetId, userDatasetId))
);
Seems to work šŸ™‚
M
Mykhailoā€¢40d ago
Super! Could you share the schema please? I will try to do it with relational queries