Drizzle Team•13mo 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