Drizzle Team•15mo ago

many-to-one selection as array

Hi, Let's say I have a user table, and the user can have multiple profiles. When I select using join on user_id I get back two records like so (pseudo-code):

[
  { user_id: 123, profile_id: "abc" },
  { user_id: 123, profile_id: "def" },
]

[
  { user_id: 123, profile_id: "abc" },
  { user_id: 123, profile_id: "def" },
]

What I want is a single record, where profiles are listed in an array on the user object:

[
  {
    user_id: 123,
    profiles: [
      { profile_id: "abc" },
      { profile_id: "def" },
    ]
  }
]

[
  {
    user_id: 123,
    profiles: [
      { profile_id: "abc" },
      { profile_id: "def" },
    ]
  }
]

Is there an elegant way for me to get query back in desired form (w/o needing to resort to two queries and patching together a new type)? Any help is much appreciated! Thanks,

5 Replies

rphlmr ⚡•15mo ago

Hello, yes you can with groupBy and jsonAgg function (at least in postgres) https://gist.github.com/rphlmr/0d1722a794ed5a16da0fdf6652902b15#file-jsonagg_jsonaggbuildobject-ts

Gist

Drizzle snippets

Drizzle snippets. GitHub Gist: instantly share code, notes, and snippets.

hotshoe•15mo ago

Thanks @rphlmr , I failed to mention I'm on mysql (planetscale) but this is something sql should handle, but ORMs (like Prisma) have given this to me for free -- so I'm struggling to get to work. groupBy does not work for me -- late where I am but will post more concrete example if still no luck tomorrow w/ fresh eyes -- in mean time, if anybody has other suggestion please advise -- I wasted my whole eventing trying to get query semantics right on this 😦

rphlmr ⚡•15mo ago

If you can't use groupBy or have some trouble, you can look at this: https://github.com/drizzle-team/drizzle-orm/blob/main/docs/joins.md#aggregating-results

GitHub

drizzle-orm/joins.md at main · drizzle-team/drizzle-orm

TypeScript ORM for SQL. Contribute to drizzle-team/drizzle-orm development by creating an account on GitHub.

rphlmr ⚡•15mo ago

SQL will give you as many row as you have profiles. Then, you can reduce (in js/ts) to consolidate everything. I was here weeks ago too 😅

hotshoe•15mo ago

Thanks for pointing this out -- I was hoping to not send redundant data over the wire (like same city info for each row in this example) but this def looks like a workable solution -- avoids multiple round trips to SQL end. Thank you! My sql is aweful (one of motivations for me switching to drizzle so to take step towards fixing that and not be reliant on fully abstracted ORM like w/ prisma), so perhaps I'm naively thinking this is something SQL should handle in general since pretty basic scenario. Again, thank you 🙂

Gaming

Programming

many-to-one selection as array