George Posts - Answer Overflow

George

Explore posts from servers

•Created by George on 10/18/2024 in #help

Querying groups where a given user id is a member

The ideal SQL I'm trying to replicate is the following:

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

Here's the drizzle query builder equivalent:

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))

Can I query across tables using the query api? The following query throws:

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),
})

1 replies

DTDrizzle Team

•Created by George on 1/9/2024 in #help

Circular FK reference

How do I avoid a circular reference on a fk builder? I know you cast with AnyPgColumn when using .reference on a column... but I'm using the fk builder which has a circular reference

39 replies

Gaming

Programming