DT
Drizzle Team•2y ago
Eternal Mori

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 Mori
Eternal MoriOP•2y 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
Mendy•2y 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
Mendy•2y ago
Feel free to drop a comment in there - showing interest in this feature

Did you find this page helpful?