Select parent rows where child exists

I want to use a relational query to select all users and their posts where users have at least one post.
5 Replies
Sillvva
Sillvva3mo ago
This is from my code. You can use the exists method to filter rows based on a subquery.
const dms = await q.dungeonMasters.findMany({
with: {
logs: {
with: {
character: {
columns: {
id: true,
name: true,
userId: true
}
}
}
}
},
where: (dms, { or, eq, and, exists }) =>
and(
id
? and(or(eq(dms.owner, user.id), eq(dms.uid, user.id)), eq(dms.id, id))
: or(eq(dms.owner, user.id), eq(dms.uid, user.id)),
exists(
db
.select({ id: logs.id })
.from(logs)
.innerJoin(characters, eq(logs.characterId, characters.id))
.where(and(eq(logs.dungeonMasterId, dms.id)))
)
)
});
const dms = await q.dungeonMasters.findMany({
with: {
logs: {
with: {
character: {
columns: {
id: true,
name: true,
userId: true
}
}
}
}
},
where: (dms, { or, eq, and, exists }) =>
and(
id
? and(or(eq(dms.owner, user.id), eq(dms.uid, user.id)), eq(dms.id, id))
: or(eq(dms.owner, user.id), eq(dms.uid, user.id)),
exists(
db
.select({ id: logs.id })
.from(logs)
.innerJoin(characters, eq(logs.characterId, characters.id))
.where(and(eq(logs.dungeonMasterId, dms.id)))
)
)
});
Sillvva
Sillvva3mo ago
Drizzle ORM - Filters
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
Jakesdoc
Jakesdoc3mo ago
@Sillvva Maybe I'm just missing something, but it doesn't appear to do what I want.
const users = await db.query.users.findMany({
where: exists(
db.select({id: post.id}).from(post).innerJoin(post, eq(post.userId, user.id))
)
})
const users = await db.query.users.findMany({
where: exists(
db.select({id: post.id}).from(post).innerJoin(post, eq(post.userId, user.id))
)
})
Does that look right? Because that still just returns every user.
Sillvva
Sillvva3mo ago
const users = await db.query.users.findMany({
where: (users, { exists, eq }) => exists(
db.select({id: post.id}).from(post).where(eq(post.userId, users.id))
)
})
const users = await db.query.users.findMany({
where: (users, { exists, eq }) => exists(
db.select({id: post.id}).from(post).where(eq(post.userId, users.id))
)
})
Jakesdoc
Jakesdoc3mo ago
That worked, thank you very much!