Drizzle Team•2mo ago

Joins with results in arrays

      const fetchedOrderServices = await tx
        .select()
        .from(orderServices)
        .where(eq(orderServices.orderID, order))

      const fetchedOrderLocalServices = await tx
        .select()
        .from(orderLocalServices)
        .where(eq(orderLocalServices.orderID, order))

      const [fetchedOrder] = await tx
        .select()
        .from(orders)
        .where(eq(orders.orderID, order))
        .leftJoin(rentCarBookings, eq(rentCarBookings.orderID, order))

      const fetchedOrderServices = await tx
        .select()
        .from(orderServices)
        .where(eq(orderServices.orderID, order))

      const fetchedOrderLocalServices = await tx
        .select()
        .from(orderLocalServices)
        .where(eq(orderLocalServices.orderID, order))

      const [fetchedOrder] = await tx
        .select()
        .from(orders)
        .where(eq(orders.orderID, order))
        .leftJoin(rentCarBookings, eq(rentCarBookings.orderID, order))

I would like a single select with joins that gives me more or less the same result. Something along the lines of:

{
fetchedOrder: {
  orderID: OrderID,
  customerID: CustomerID
},
localServices: {orderID: OrderID, localServiceID: LocalServiceID}[},
globalServices: {orderID: OrderID, globalServiceID: GlobalServiceID}[]
}

{
fetchedOrder: {
  orderID: OrderID,
  customerID: CustomerID
},
localServices: {orderID: OrderID, localServiceID: LocalServiceID}[},
globalServices: {orderID: OrderID, globalServiceID: GlobalServiceID}[]
}

102 Replies

Jitendra•2mo ago

Yea I would like something like this too. I am currently using promise.all

edarcode•2mo ago

According to the doc, it should be possible with query and with, but I have managed to get it to work. And I have the same problem.

edarcode•2mo ago

Darren•2mo ago

tx.query.orders.findMany({
    columns: {
        orderID: true,
        customerID: true
        },
    with: {
        orderServices: true,
        orderLocalServices: true,
        globalServices: {
            columns: {
                somecolumnnameonly: true
                }
                },
        rentCarBookings: true
        }
    where: eq(orders.orderID, order)
})

tx.query.orders.findMany({
    columns: {
        orderID: true,
        customerID: true
        },
    with: {
        orderServices: true,
        orderLocalServices: true,
        globalServices: {
            columns: {
                somecolumnnameonly: true
                }
                },
        rentCarBookings: true
        }
    where: eq(orders.orderID, order)
})

edarcode•2mo ago

hello. bro and it's time to configure something additional to the references? i have

import { db } from "../../../db/db";
import { EdarErr } from "../../../error/EdarErr";
import { Uuid } from "../../../types";

export const getAccountService = async (id: Uuid) => {
  const account = await db.query.usersTable.findFirst({
    where: (user, { eq }) => eq(user.id, id),
    with: { usersToFollows: true },
  });

  if (!account) throw new EdarErr(404, "Account not found");

  return account;
};

import { db } from "../../../db/db";
import { EdarErr } from "../../../error/EdarErr";
import { Uuid } from "../../../types";

export const getAccountService = async (id: Uuid) => {
  const account = await db.query.usersTable.findFirst({
    where: (user, { eq }) => eq(user.id, id),
    with: { usersToFollows: true },
  });

  if (!account) throw new EdarErr(404, "Account not found");

  return account;
};

but throw err T.T

Darren•2mo ago

you would need to setup the relationships yes, if you havent done so already, you can do an introspect and it will generate your current relations providing they are all linked with pkey -> fkey etc

edarcode•2mo ago

export const usersTableRelations = relations(usersTable, ({ many }) => ({
  usersToFollows: many(followsTable),
}));

and 

export const followsTableRelations = relations(followsTable, ({ many }) => ({
  followsToUsers: many(usersTable),
}));

export const usersTableRelations = relations(usersTable, ({ many }) => ({
  usersToFollows: many(followsTable),
}));

and 

export const followsTableRelations = relations(followsTable, ({ many }) => ({
  followsToUsers: many(usersTable),
}));

Darren•2mo ago

if this is many to many you need a junction table

edarcode•2mo ago

I have it, in fact TS recommends the key, but it seems to give me err when I put with and if I remove it it works normally

edarcode•2mo ago

Darren•2mo ago

yes thats how it would work

edarcode•2mo ago

my table union is this

edarcode•2mo ago

removed with so success

edarcode•2mo ago

agg with so return err

Darren•2mo ago

add this relation

export const followertoFollowingRelations = relations(followsTable, ({ one }) => ({
  user: one(usersTable, {
    fields: [followsTable.followerId],
    references: [usersTable.id],
  }),
}));

export const followertoFollowingRelations = relations(followsTable, ({ one }) => ({
  user: one(usersTable, {
    fields: [followsTable.followerId],
    references: [usersTable.id],
  }),
}));

Gaming

Programming

Joins with results in arrays