Drizzle Team•11mo ago

Aggregation - limitations and mapping results

Using the aggregation method proposed in the docs (https://orm.drizzle.team/docs/joins#aggregating-results) is fairly limited as it makes for instance the limiting of results quite obsolete: using the documentation's example of user and pets, if we wished to limit() the results for 10 users, but one user has 10 pets, we would only get that one user with their 10 pets, rather than the desired 10 users with however many pets they might have (unless we slice the resulting array, but this seems like a huge waste of resources in large datasets) One solution of course in a raw PG query is to do some kind of aggregation in the query, for instance:

const rows = db.select({
    user: users,
    pets: sql<SnakeCasePet[]>`json_agg(row_to_json(${pets}))`,
  }).from(users).leftJoin(pets, eq(users.id, pets.ownerId)).limit(n);

const rows = db.select({
    user: users,
    pets: sql<SnakeCasePet[]>`json_agg(row_to_json(${pets}))`,
  }).from(users).leftJoin(pets, eq(users.id, pets.ownerId)).limit(n);

What happens then is that the resulting pets, although being the aggregation I want, is of course not processed by Drizzle's column mapper. So I typically get snake case objects rather than my camelCase Drizzle mappings. Is there a way to somehow use Drizzle's internal API to parse aggregated JSON results and get the corresponding type inference etc? Or, alternatively, has anyone found another way of dealing with this? Thank you!

Drizzle ORM - next gen TypeScript ORM

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

4 Replies

Angelelz•11mo ago

It seems like this is the perfect use case for RQB

Angelelz•11mo ago

Those do json aggregation for you: https://orm.drizzle.team/docs/rqb

Drizzle ORM - next gen TypeScript ORM

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

AdiccoOP•11mo ago

I'll have a look to see if RQB could work for my use case, thanks. In the meantime working with WITH cte queries seem to be an option too

Angelelz•11mo ago

Sure, there's probably many ways to go about solving this

Gaming

Programming

Aggregation - limitations and mapping results