Drizzle Team•17mo ago

How to implement a where clause on a joined table with the new relation builder?

This was my original query, the big problem is that it returns as many rows as there are messages instead of one entry with multiple messages. As you can see, I have a where clause where the entry is filtered with it's id, and a organization id that is part of another joined table named channel.

 const tickets = await db
    .select({
      id: s.tickets.id,
      status: s.tickets.status,
      channel: {
        id: s.channels.id,
        type: s.channels.type,
        name: s.channels.name,
      },
      contact: {
        id: s.contacts.id,
        name: s.contacts.name,
        email: s.contacts.email,
      },
      messages: {
        id: s.messages.id,
        subject: s.messages.subject,
        text: s.messages.text,
        html: s.messages.html,
        createdAt: s.messages.createdAt,
      },
    })
    .from(s.tickets)
    .innerJoin(s.contacts, eq(s.tickets.contactId, s.contacts.id))
    .innerJoin(s.channels, eq(s.tickets.channelId, s.channels.id))
    .leftJoin(s.messages, eq(s.tickets.id, s.messages.ticketId))
    .where(
      and(
        eq(s.tickets.id, params.id),
        eq(s.channels.organizationId, organizationId)
      )
    );

  const ticket = tickets[0];

 const tickets = await db
    .select({
      id: s.tickets.id,
      status: s.tickets.status,
      channel: {
        id: s.channels.id,
        type: s.channels.type,
        name: s.channels.name,
      },
      contact: {
        id: s.contacts.id,
        name: s.contacts.name,
        email: s.contacts.email,
      },
      messages: {
        id: s.messages.id,
        subject: s.messages.subject,
        text: s.messages.text,
        html: s.messages.html,
        createdAt: s.messages.createdAt,
      },
    })
    .from(s.tickets)
    .innerJoin(s.contacts, eq(s.tickets.contactId, s.contacts.id))
    .innerJoin(s.channels, eq(s.tickets.channelId, s.channels.id))
    .leftJoin(s.messages, eq(s.tickets.id, s.messages.ticketId))
    .where(
      and(
        eq(s.tickets.id, params.id),
        eq(s.channels.organizationId, organizationId)
      )
    );

  const ticket = tickets[0];

3 Replies

Eternal MoriOP•17mo ago

Now I created almost an indentical output with the new builder. But now the problem is that I dont know how to filter on a joined table. in this example on "ticket.channel.organizationId". That part is missing here. How can I implement the filter?

const ticket = await db.query.tickets.findFirst({
    columns: {
      id: true,
      status: true,
    },
    with: {
      channel: {
        columns: {
          id: true,
          type: true,
          name: true,
          organizationId: true,
        },
      },
      contact: {
        columns: {
          id: true,
          name: true,
          email: true,
        },
      },
      messages: {
        columns: {
          id: true,
          subject: true,
          text: true,
          html: true,
          createdAt: true,
        },
      },
    },
    where: (table, { and, eq, sql }) => and(eq(table.id, params.id)),
  });

const ticket = await db.query.tickets.findFirst({
    columns: {
      id: true,
      status: true,
    },
    with: {
      channel: {
        columns: {
          id: true,
          type: true,
          name: true,
          organizationId: true,
        },
      },
      contact: {
        columns: {
          id: true,
          name: true,
          email: true,
        },
      },
      messages: {
        columns: {
          id: true,
          subject: true,
          text: true,
          html: true,
          createdAt: true,
        },
      },
    },
    where: (table, { and, eq, sql }) => and(eq(table.id, params.id)),
  });

Mendy•17mo ago

I didn’t read your entire example but it seems you’re interested in this feature which is not implemented.

GitHub

[QUESTION] Use a relation field in a where clause · Issue #798 · dr...

Is it possible to use a relation field in a where clause? It seems that there is no access to the nested relation: This is the example from the docs: Dose drizzle only provide one nested layer?

Mendy•17mo ago

Feel free to drop a comment in there - showing interest in this feature

Gaming

Programming

How to implement a where clause on a joined table with the new relation builder?