DT
Drizzle Team•4mo ago
Pupok

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]
})
}))
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))
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))
) 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?
12 Replies
Pupok
PupokOP•3mo ago
Any idea ?
rphlmr âš¡
rphlmr ⚡•3mo ago
👋 Isn't files.roId instead of id?
leftJoin(files, eq(files.roId, repairOrder.id))
leftJoin(files, eq(files.roId, repairOrder.id))
rphlmr âš¡
rphlmr ⚡•3mo ago
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 âš¡
rphlmr ⚡•3mo ago
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 âš¡
rphlmr ⚡•3mo ago
But important: your actual query will not return you a single result with a file array. For that, you need to use groupBy and an agregator (not builtin for Select API)
Pupok
PupokOP•3mo ago
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 ? 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 I see in the code you sent, that there is a declaration for a relation, it's required or not ? 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
rphlmr âš¡
rphlmr ⚡•3mo ago
I have updated the playground, should be good now
Pupok
PupokOP•3mo ago
thanks you so much for the help! 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 âš¡
rphlmr ⚡•3mo ago
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 âš¡
rphlmr ⚡•3mo ago
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.
Pupok
PupokOP•3mo ago
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 😦
Want results from more Discord servers?
Add your server