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:
and this is the query
I did the relation between files and repair orders, if i try to join them directly into one query () 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
Any idea ?
👋 Isn't
files.roId
instead of id?
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.
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.
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)Here a full demo https://drizzle.run/p9u6g3z67goqmeyrljf0k2dj
Drizzle Run
Aggregate as array of objects - Drizzle Run
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
I have updated the playground, should be good now
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
files and etas sometimes return the same record 2 or 3 times, it only happen in some repairOrders not all of them
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
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.
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 😦