Is this the right way of using relations ?

hi, this is my first project using drizzle, and I'm not that good at sql query as mostly i use ORMs, but now im using drizzle to learn more sql like syntax,

This is the schema is question:

export const repairOrder = pgTable(
  'repairOrder',
  {
    id: text('id').primaryKey(),
    userId: text('userId').notNull(),
    mechanicId: text('mechanicId'),
    status: roStatusEnum('status').notNull().default('part sent'),
    roNumber: text('roNumber').notNull(),
    carMaker: text('carMaker').notNull(),
    carModel: text('carModel').notNull(),
    createdAt: timestamp('createdAt').notNull().defaultNow(),
    updatedAt: timestamp('updatedAt')
      .defaultNow()
      .$onUpdate(() => new Date())
  } to
  }
)

export const repairOrderFilesRelation = relations(repairOrder, ({  many }) => ({
  files: many(files)
}))

export const files = pgTable(
  'files',
  {
    id: text('id').primaryKey(),
    roId: text('roId').notNull(),
    name: text('name').notNull(),
    key: text('key').notNull(),
    url: text('url').notNull(),
    type: text('type').notNull(),
  }jjk
)

export const fileToRepairOrder = relations(files, ({ one }) => ({
  repairOrder: one(repairOrder, {
    fields: [files.roId],
    references: [repairOrder.id]
  })
}))

export const repairOrder = pgTable(
  'repairOrder',
  {
    id: text('id').primaryKey(),
    userId: text('userId').notNull(),
    mechanicId: text('mechanicId'),
    status: roStatusEnum('status').notNull().default('part sent'),
    roNumber: text('roNumber').notNull(),
    carMaker: text('carMaker').notNull(),
    carModel: text('carModel').notNull(),
    createdAt: timestamp('createdAt').notNull().defaultNow(),
    updatedAt: timestamp('updatedAt')
      .defaultNow()
      .$onUpdate(() => new Date())
  } to
  }
)

export const repairOrderFilesRelation = relations(repairOrder, ({  many }) => ({
  files: many(files)
}))

export const files = pgTable(
  'files',
  {
    id: text('id').primaryKey(),
    roId: text('roId').notNull(),
    name: text('name').notNull(),
    key: text('key').notNull(),
    url: text('url').notNull(),
    type: text('type').notNull(),
  }jjk
)

export const fileToRepairOrder = relations(files, ({ one }) => ({
  repairOrder: one(repairOrder, {
    fields: [files.roId],
    references: [repairOrder.id]
  })
}))

export const repairOrder = pgTable(
  'repairOrder',
  {
    id: text('id').primaryKey(),
    userId: text('userId').notNull(),
    mechanicId: text('mechanicId'),
    status: roStatusEnum('status').notNull().default('part sent'),
    roNumber: text('roNumber').notNull(),
    carMaker: text('carMaker').notNull(),
    carModel: text('carModel').notNull(),
    createdAt: timestamp('createdAt').notNull().defaultNow(),
    updatedAt: timestamp('updatedAt')
      .defaultNow()
      .$onUpdate(() => new Date())
  } to
  }
)

export const repairOrderFilesRelation = relations(repairOrder, ({  many }) => ({
  files: many(files)
}))

export const files = pgTable(
  'files',
  {
    id: text('id').primaryKey(),
    roId: text('roId').notNull(),
    name: text('name').notNull(),
    key: text('key').notNull(),
    url: text('url').notNull(),
    type: text('type').notNull(),
  }jjk
)

export const fileToRepairOrder = relations(files, ({ one }) => ({
  repairOrder: one(repairOrder, {
    fields: [files.roId],
    references: [repairOrder.id]
  })
}))

export const repairOrder = pgTable(
  'repairOrder',
  {
    id: text('id').primaryKey(),
    userId: text('userId').notNull(),
    mechanicId: text('mechanicId'),
    status: roStatusEnum('status').notNull().default('part sent'),
    roNumber: text('roNumber').notNull(),
    carMaker: text('carMaker').notNull(),
    carModel: text('carModel').notNull(),
    createdAt: timestamp('createdAt').notNull().defaultNow(),
    updatedAt: timestamp('updatedAt')
      .defaultNow()
      .$onUpdate(() => new Date())
  } to
  }
)

export const repairOrderFilesRelation = relations(repairOrder, ({  many }) => ({
  files: many(files)
}))

export const files = pgTable(
  'files',
  {
    id: text('id').primaryKey(),
    roId: text('roId').notNull(),
    name: text('name').notNull(),
    key: text('key').notNull(),
    url: text('url').notNull(),
    type: text('type').notNull(),
  }jjk
)

export const fileToRepairOrder = relations(files, ({ one }) => ({
  repairOrder: one(repairOrder, {
    fields: [files.roId],
    references: [repairOrder.id]
  })
}))

and this is the query

      const [data] = await db
        .select()
        .from(repairOrder)
        .innerJoin(estimator, eq(estimator.id, repairOrder.userId))
        .leftJoin(mechanic, eq(mechanic.id, repairOrder.mechanicId))
        .where(eq(repairOrder.id, id))

      const filesData = await db.select().from(files).where(eq(files.roId, id))

      const [data] = await db
        .select()
        .from(repairOrder)
        .innerJoin(estimator, eq(estimator.id, repairOrder.userId))
        .leftJoin(mechanic, eq(mechanic.id, repairOrder.mechanicId))
        .where(eq(repairOrder.id, id))

      const filesData = await db.select().from(files).where(eq(files.roId, id))

      const [data] = await db
        .select()
        .from(repairOrder)
        .innerJoin(estimator, eq(estimator.id, repairOrder.userId))
        .leftJoin(mechanic, eq(mechanic.id, repairOrder.mechanicId))
        .where(eq(repairOrder.id, id))

      const filesData = await db.select().from(files).where(eq(files.roId, id))

      const [data] = await db
        .select()
        .from(repairOrder)
        .innerJoin(estimator, eq(estimator.id, repairOrder.userId))
        .leftJoin(mechanic, eq(mechanic.id, repairOrder.mechanicId))
        .where(eq(repairOrder.id, id))

      const filesData = await db.select().from(files).where(eq(files.roId, id))

I did the relation between files and repair orders, if i try to join them directly into one query (

leftJoin(files, eq(files.roId, repairOrder.id))

leftJoin(files, eq(files.roId, repairOrder.id))

leftJoin(files, eq(files.roId, repairOrder.id))

leftJoin(files, eq(files.roId, repairOrder.id))

) then it will only return 1 file, this is why i splitted it into 2 so i can get all, is this the right way of doing it?

PupokOP•8/7/24, 3:47 PM

Any idea ?

rphlmr ⚡•8/7/24, 4:25 PM

Isn't files.roIdfiles.roId instead of id?

leftJoin(files, eq(files.roId, repairOrder.id))

leftJoin(files, eq(files.roId, repairOrder.id))

rphlmr ⚡•8/7/24, 4:29 PM

Branded types are a way to prevent wrong association when everything is named id https://orm.drizzle.team/docs/column-types/pg#customizing-column-data-type

Drizzle ORM - PostgreSQL column types

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

rphlmr ⚡•8/7/24, 4:30 PM

Also, Relations are needed only if you plan to use the Query API https://orm.drizzle.team/docs/rqb

For the Select API (what you shared here) it is not needed

Drizzle ORM - Query

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

rphlmr ⚡•8/7/24, 4:38 PM

But important: your actual query will not return you a single result with a file array. For that, you need to use groupBygroupBy and an agregator (not builtin for Select API)

rphlmr ⚡•8/7/24, 4:38 PM

Here a full demo https://drizzle.run/p9u6g3z67goqmeyrljf0k2dj

Drizzle Run

Aggregate as array of objects - Drizzle Run

PupokOP•8/7/24, 9:23 PM

So if I'm not using RQB, i don't have to create the relations like I did ? So I don't have to create any relation ?

PupokOP•8/7/24, 9:26 PM

Yeah it is roId I had it correct in my code but since I removed it I just typed it directly here as an example

PupokOP•8/7/24, 10:59 PM

I see in the code you sent, that there is a declaration for a relation, it's required or not ?

PupokOP•8/8/24, 1:19 AM

i tested your suggestion and its working good, thanks i learned a lot here!, also is there a way for the util functions, to return empty array if there is no files? i tried to modify the functions but sql is beyond my knowdlge

PPupok i tested your suggestion and its working good, thanks i learned a lot here!, als...

rphlmr ⚡•8/8/24, 9:39 AM

I have updated the playground, should be good now

PupokOP•8/9/24, 2:00 AM

thanks you so much for the help!

PupokOP•8/10/24, 7:38 PM

hey @Raphaël M (@rphlmr)

sorry to bring this back, but sometimes the queries return duplicated item that dont exist in the db this is my query right now

      const [data] = await db
        .select({
          ...getTableColumns(repairOrder),
          estimator: getTableColumns(estimator),
          mechanic: getTableColumns(mechanic),
          files: jsonAggBuildObject(getTableColumns(files)),
          etas: jsonAggBuildObject(getTableColumns(eta))
        })
        .from(repairOrder)
        .innerJoin(estimator, eq(estimator.id, repairOrder.userId))
        .leftJoin(mechanic, eq(mechanic.id, repairOrder.mechanicId))
        .leftJoin(files, eq(files.roId, repairOrder.id))
        .leftJoin(eta, eq(eta.roId, repairOrder.id))
        .groupBy(repairOrder.id, estimator.id, mechanic.id)
        .where(eq(repairOrder.id, id))

      const [data] = await db
        .select({
          ...getTableColumns(repairOrder),
          estimator: getTableColumns(estimator),
          mechanic: getTableColumns(mechanic),
          files: jsonAggBuildObject(getTableColumns(files)),
          etas: jsonAggBuildObject(getTableColumns(eta))
        })
        .from(repairOrder)
        .innerJoin(estimator, eq(estimator.id, repairOrder.userId))
        .leftJoin(mechanic, eq(mechanic.id, repairOrder.mechanicId))
        .leftJoin(files, eq(files.roId, repairOrder.id))
        .leftJoin(eta, eq(eta.roId, repairOrder.id))
        .groupBy(repairOrder.id, estimator.id, mechanic.id)
        .where(eq(repairOrder.id, id))

files and etas sometimes return the same record 2 or 3 times, it only happen in some repairOrders not all of them

rphlmr ⚡•8/10/24, 7:44 PM

I haven’t any computer to test but maybe you could add eta.roId and file.roId to groupBy.
This is a cartesian join issue. Maybe it is not enough

rphlmr ⚡•8/10/24, 7:48 PM

When this issue happens, we have to extract some joins to a subquery with there own filters
Something like that: https://gist.github.com/rphlmr/de869cf24816d02068c3dd089b45ae82#file-query-ts

Gist

Drizzle ORM, deep sub queries

Drizzle ORM, deep sub queries. GitHub Gist: instantly share code, notes, and snippets.

PupokOP•8/11/24, 2:00 AM

so is better to just create more queries for each field? i tried adding eta.roid and files.roid to the groupby but still getting duplicated entries

Is this the right way of using relations ?

Similar Threads

Similar Threads

Similar Threads