Querying groups where a given user id is a member
The ideal SQL I'm trying to replicate is the following:
Here's the drizzle query builder equivalent:
Can I query across tables using the query api? The following query throws:
SELECT groups.id,
groups.type,
groups.owner_user_id,
groups.name,
groups.last_active_at
FROM groups
INNER JOIN group_members
on groups.id = group_members.group_id
WHERE group_members.user_id = '01JAG82X0JBWSGYDRNNHD24BTH'
AND groups.deleted_at is NULL
ORDER BY groups.last_active_at DESC
SELECT groups.id,
groups.type,
groups.owner_user_id,
groups.name,
groups.last_active_at
FROM groups
INNER JOIN group_members
on groups.id = group_members.group_id
WHERE group_members.user_id = '01JAG82X0JBWSGYDRNNHD24BTH'
AND groups.deleted_at is NULL
ORDER BY groups.last_active_at DESC
this.drizzle
.select({
id: groups.id,
type: groups.type,
ownerUserId: groups.ownerUserId,
name: groups.name,
lastActiveAt: groups.lastActiveAt,
})
.from(groups)
.innerJoin(groupMembers, eq(groupMembers.groupId, groups.id))
.where(
and(
eq(groupMembers.userId, userId),
isNull(groups.deletedAt),
),
)
.orderBy(desc(groups.lastActiveAt))
this.drizzle
.select({
id: groups.id,
type: groups.type,
ownerUserId: groups.ownerUserId,
name: groups.name,
lastActiveAt: groups.lastActiveAt,
})
.from(groups)
.innerJoin(groupMembers, eq(groupMembers.groupId, groups.id))
.where(
and(
eq(groupMembers.userId, userId),
isNull(groups.deletedAt),
),
)
.orderBy(desc(groups.lastActiveAt))
Error: column groups.user_id does not exist
Error: column groups.user_id does not exist
this.drizzle.query.groups.findMany({
columns: {
id: true,
type: true,
ownerUserId: true,
name: true,
lastActiveAt: true,
},
with: {
groupMembers: {
columns: {
groupId: true,
userId: true,
},
},
},
where: and(
eq(groupMembers.userId, userId),
isNull(groups.deletedAt),
),
orderBy: desc(groups.lastActiveAt),
})
this.drizzle.query.groups.findMany({
columns: {
id: true,
type: true,
ownerUserId: true,
name: true,
lastActiveAt: true,
},
with: {
groupMembers: {
columns: {
groupId: true,
userId: true,
},
},
},
where: and(
eq(groupMembers.userId, userId),
isNull(groups.deletedAt),
),
orderBy: desc(groups.lastActiveAt),
})
0 Replies