Eternal Mori Posts - Answer Overflow

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];

4 replies

DTDrizzle Team

•Created by Eternal Mori on 7/3/2023 in #help

Broken typescript in select

3 replies

DTDrizzle Team

•Created by Eternal Mori on 6/29/2023 in #help

Typescript error with custom column type

2 replies

Gaming

Programming