Drizzle Team•5mo 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•5mo ago

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

edarcode•5mo 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•5mo ago

Darren•5mo 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•5mo 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•5mo 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•5mo 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•5mo ago

if this is many to many you need a junction table

edarcode•5mo 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•5mo ago

Darren•5mo ago

yes thats how it would work

edarcode•5mo ago

my table union is this

edarcode•5mo ago

removed with so success

edarcode•5mo ago

agg with so return err

Darren•5mo 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],
  }),
}));

edarcode•5mo ago

one ? im dead xD

Darren•5mo ago

sorry I messed up try add this to your 2 exisitng relations

export const usersToFollowersRelations = relations(followsTable, ({ one }) => ({
  follower: one(usersTable, {
    fields: [followsTable.followerId],
    references: [usersTable.id],
  }),
  following: one(usersTable, {
    fields: [followsTable.followingId],
    references: [usersTable.id],
  }),
}));

export const usersToFollowersRelations = relations(followsTable, ({ one }) => ({
  follower: one(usersTable, {
    fields: [followsTable.followerId],
    references: [usersTable.id],
  }),
  following: one(usersTable, {
    fields: [followsTable.followingId],
    references: [usersTable.id],
  }),
}));

edarcode•5mo ago

You burned me, I have no idea how to do it. xD

Darren•5mo ago

if youre stuck jsut run drizzle-kit introspect and it will generate the relations for you but make sure you copy your esiting schema etc or set it to output somewhere else so it doesnt overwrite what you have

edarcode•5mo ago

this is my schema

message.txt

edarcode•5mo ago

Does that command generate the relationships for me simply by having my schema or how?

Darren•5mo ago

it generates from your database you have one user can appear many time in followers table both in followers and folloiwng, so you habve the many side of relation but not the 2(one) side of it so when you said with it doesnt know wwether youre looking at followersid or followingid so it doesnt currently work

edarcode•5mo ago

ok 1min It seems that it generated a file with the relationships of all the tables. I suppose I should migrate again?

Darren•5mo ago

you dont havbe to migrate if you dont want to jsut take out the relations it made for what u needed if thats all you want the relations are totally seperate to your schema its just for your code

edarcode•5mo ago

ok. let me try. hopefully it works xD

edarcode•5mo ago

He doesn't recommend me anymore T.T

Darren•5mo ago

are the relations in a sperate file? if so you will need to include them in your db file

edarcode•5mo ago

aaaa sorry

Darren•5mo ago

const db = drizzle(client, { schema: { ...schema, ...relations } });

const db = drizzle(client, { schema: { ...schema, ...relations } });

edarcode•5mo ago

i have import in my schema aaaaa

edarcode•5mo ago

Darren•5mo ago

are your relations in that schema file?

edarcode•5mo ago

I think it will take me 1 minute to try

Darren•5mo ago

you can add import * as relations from *./relations

edarcode•5mo ago

e.e

edarcode•5mo ago

1s check if if It works for me now, but I think I get the result inverted

edarcode•5mo ago

In my SQLite db, I have this that indicates that this user or this account follows X user (lore), however it appears in follower instead of following

Darren•5mo ago

that looks right to me your saying this user in the follows table in followerID ... has the followers in followingID tbh its poor naming

export const followsTable = sqliteTable(
  "follows",
  {
    userId: text("user_id")
      .notNull()
      .references(() => usersTable.id, { onDelete: "cascade" }),
    followingId: text("following_id")
      .notNull()
      .references(() => usersTable.id, { onDelete: "cascade" }),
    createdAt: text("created_at")
      .default(sql`(CURRENT_TIMESTAMP)`)
      .notNull(),
  },
  (followsTable) => {
    return {
      id: primaryKey({
        columns: [followsTable.userId, followsTable.followingId],
      }),
    };
  }
);

export const followsTable = sqliteTable(
  "follows",
  {
    userId: text("user_id")
      .notNull()
      .references(() => usersTable.id, { onDelete: "cascade" }),
    followingId: text("following_id")
      .notNull()
      .references(() => usersTable.id, { onDelete: "cascade" }),
    createdAt: text("created_at")
      .default(sql`(CURRENT_TIMESTAMP)`)
      .notNull(),
  },
  (followsTable) => {
    return {
      id: primaryKey({
        columns: [followsTable.userId, followsTable.followingId],
      }),
    };
  }
);

might be easier to decipher what youre trying to do

edarcode•5mo ago

I'm super confused, so what is this for? "follows_followingId": []

Darren•5mo ago

you wrote the tables 😄

edarcode•5mo ago

this object should not come out below ? the following ?

 {
      "followerId": "dabac6a1-b01e-4263-91cc-befa23399f8a",
      "followingId": "5a4d6d33-64b8-4a96-9e39-61b79a21b3c5",
      "createdAt": "2024-08-17 19:44:26"
    }

this object should not come out below ? the following ?

 {
      "followerId": "dabac6a1-b01e-4263-91cc-befa23399f8a",
      "followingId": "5a4d6d33-64b8-4a96-9e39-61b79a21b3c5",
      "createdAt": "2024-08-17 19:44:26"
    }

Darren•5mo ago

this is the user you were looking at followerId this is who they are following followingId": "5a4d6d33-64b8-4a96-9e39-61b79a21b3c5",

edarcode•5mo ago

yes

Darren•5mo ago

this "followerId": "dabac6a1-b01e-4263-91cc-befa23399f8a" matches the userid above it if you dont want to see it you can exclude it

edarcode•5mo ago

Haha, I think I'll retire from programming. If I'm looking for information about A, then A:{ id, followers, following }

Darren•5mo ago

then you have not set up youre tables correctly you need a junction table actually nm ok 1 sec

edarcode•5mo ago

But isn't the union table supposed to be the intermediate one that has both fk?

Darren•5mo ago

with: {
  follows_followerId: {
    columns: {followerId: false}
  },
  follows_followingId: {
    columns: {followingId: false}
}

with: {
  follows_followerId: {
    columns: {followerId: false}
  },
  follows_followingId: {
    columns: {followingId: false}
}

edarcode•5mo ago

With this table I'm supposed to want followerId to represent who is following a user and followingId to represent who they are following. Am I wrong?

Darren•5mo ago

so you will then get

user: {following: [], followers: [] }

user: {following: [], followers: [] }

the table is fine see the qbove additions to your with

edarcode•5mo ago

i will try to do it

edarcode•5mo ago

It seems really ugly to me that the following appears in the follows_followerId key. I don't see the point T.T

Darren•5mo ago

what do you mean? that is who they are following what do u want it to say

edarcode•5mo ago

It seems that I have a bad concept of SQL, or I don't know. I just can't understand why it comes out inverted. In fact, if I go to my normal SQLite DB, I read it as simple.

Darren•5mo ago

it isnt inverted

edarcode•5mo ago

//I wanted something like that

{
  id: "sadfsafd",
  followings: [id, id, id]
  followers: [id, id, id]
}

//I wanted something like that

{
  id: "sadfsafd",
  followings: [id, id, id]
  followers: [id, id, id]
}

Darren•5mo ago

the first array will list all the users that user is following then 2nd array will list all the userss who is following them then you entered it in the wrong column when you added that user ot just change the labels in your relations file

edarcode•5mo ago

It's that in SQLite it is perfectly tabulated, so I entered it correctly, right?

AndréLBOP•5mo ago

So is there a way of doing it with the select-syntax or do I have to use query syntax? I have used select everywhere else in the program so I prefer sticking to it?

Darren•5mo ago

followings: [id, id, id] that is literally what its lisitng you

Darren•5mo ago

edarcode•5mo ago

select * with == you used prisma ? but you must establish relationships before using with

edarcode•5mo ago

I read it backwards, because that following is in a higher key follower I also wanted to switch to drizzleORM precisely because I thought it was a Prisma bug, but I see that I'm the one with the problem. It's like I read backwards.

Darren•5mo ago

its formatted such that it is an array, since you can follow many users, same eith the followers array also you can rename the field names in your relations file if you dont like it saying follows_followerId: ... you can change it to read jsut followers: it would prob be easier to read

edarcode•5mo ago

ok, my plan is to format the final result and rename the key. is that ok with you? so that the front-end reads it naturally and doesn't have to think about the array Or do you think it would be very dirty to invert the fields in the schema? xD @Darren

Darren•5mo ago

dont change the schema just rename the field in the relations file, what you have is 100% correct

edarcode•5mo ago

The truth is I was sad, I have to analyze well what I will do. I saw that the SQL works the same with the tables that I configure as those generated by the migration. Which of the 2 should I use? @Darren

edarcode•5mo ago

These are the things that disappoint, now I have to change 20 thousand files. T.T and it gives me err depending on which schema I use and they are supposed to be the same

Darren•5mo ago

Just continue to use your schema, the introspection was only to get the correct relations for your code to work. You just need to change the reference in the relations file to point to your schema

edarcode•5mo ago

@Darren its is very crazy. The user I'm looking at doesn't have a single follower, but it appears as if he does. And in follows_followindId there's supposedly no one, it's all backwards T.T

edarcode•5mo ago

Don't you think an inner join would be cheaper?

edarcode•5mo ago

The problem is that the object will be repeated as many times as it is repeated in follows xD

Darren•5mo ago

It matches exactly with what you have entered. If you're saying this user isn't following anyone then you need you be much clearer with your field names and relation names. Just change the relation names so it says what you want it to say At the moment it just reads as this user is following this other (The id in following is different) user. Which to me says this user is following someone but not being followed by anyone. If you're saying this is wrong then I'd say you entered it backwards or you're naming is really poor.

edarcode•5mo ago

Ok bro, thanks a lot. You've helped me a lot. @Darren

Darren•5mo ago

You only need to name the following action one way. For instance if the fields were named userId and followingId then when a user follows someone you enter their userId in first col and the person they follow in the second. So you get all the people a user follows by matching the userId in the first col. But you get all the people who follow you for free by matching your userId with the second col. I think the main issue is just with how you have named your columns.

edarcode•5mo ago

I didn't change my schema because it makes sense to me as it is. I like this way a lot more, and I feel that it's easier for me to read. I look for the user and then I look for their followers and following in the "follows" table and then I join them. For me it's easier to read, but of course there are 3 select vs 1 query, I don't know how efficient it is. I know I can use promise.all, but anyway, what do you think? @Darren

edarcode•5mo ago

err cap. this is correct xD

Darren•5mo ago

Take a look at what you have returned in following. You have just returned their own userid. OK you fixed it

edarcode•5mo ago

I can't change the column names, because the schema makes sense to me. What I don't find makes sense is when I consult with a query. In fact, many documentations use followerBy instead of followerId, but it doesn't matter in the end. The schema is correct for me. @Darren Likewise, I think it is possible to do those 3 selects in 1, right? The truth is that I have never studied SQL in depth, only the basics. So I am not very good at consulting with SQL, but I like it better. There you go with query, hahaha, this reminds me of the death callback.

edarcode•5mo ago

@Darren I've been doing crazy things all day, and I came to the conclusion that with doesn't actually bring you the followers or following, it simply brings you the records where the specified id appears and then I decide based on the result who is a follower and followed.

edarcode•5mo ago

Now I'm happy with the result. The problem was me in the end. xD @Darren

Darren•5mo ago

https://drizzle.run/afinhb8o2re30lxsp9wrzxuz

edarcode•5mo ago

how did you do the query ? @Darren

Darren•5mo ago

Sorry forgot to share properly will do tomorrow

edarcode•5mo ago

wait for you

edarcode•5mo ago

@Darren === result and i think is more readable and also with fewer lines of code xD

edarcode•5mo ago

performance: style select vs style query @Darren xD

edarcode•5mo ago

If I put promise.all in the select both forms occur at the same time

edarcode•5mo ago

although I don't know if the cache is tricking me haha

AndréLBOP•5mo ago

So if anyone could help me with the question that I posted: How do I do this in one select:

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

edarcode•5mo ago

haha bro Just like I did. With with, I think a high-ranking official here told me that the form with with is more effective. @AndréLB

edarcode•5mo ago

doc https://orm.drizzle.team/docs/rqb#select-filters

Drizzle ORM - Query

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

edarcode•5mo ago

agg with you can query information of other tables, if you do click control + space ts help you

Mario564•5mo ago

Hello. I can help you here, but I'll have to assume a few things about your schema and query

await tx
  .select()
  .from(orderServices)
  .innerJoin(orderLocalServices, eq(orderLocalServices.orderID, orderServices.id))
  .innerJoin(orders, eq(orders.oderID, orderServices.id))
  .leftJoin(rentCarBookings, eq(rentCarBookings.orderID, orderServices.id))
  .where(eq(orderServices.orderID, order));
  .limit(1);

await tx
  .select()
  .from(orderServices)
  .innerJoin(orderLocalServices, eq(orderLocalServices.orderID, orderServices.id))
  .innerJoin(orders, eq(orders.oderID, orderServices.id))
  .leftJoin(rentCarBookings, eq(rentCarBookings.orderID, orderServices.id))
  .where(eq(orderServices.orderID, order));
  .limit(1);

@AndréLB Try this

AndréLBOP•5mo ago

Isn't that just limiting it to one?

Mario564•5mo ago

The 3 queries you provided are simplified by this 1 query. In the first two queries you're searching for a specific order ID which will return 1 result and in the 3rd query, I see you're destructuring the array to get the first element anyways, so the query I wrote should be what you're looking for

Rakib•5mo ago

search "drizzle jsonAgg, jsonBuildObject"

Gaming

Programming

Joins with results in arrays

Did you find this page helpful?