How to reproduce a Prisma `include` statement for arrays of related entities without SQL?

Hello, I'm trying to translate this SQL query to DrizzleORM;

SELECT
  channels.id,
  channels.name,
  channels.slug,
  channels.type,
  array_agg(channel_performances.*) AS channel_performances
FROM channels
LEFT JOIN channel_performance ON channels.id = channel_performance.channel_id
GROUP BY channels.id;

SELECT
  channels.id,
  channels.name,
  channels.slug,
  channels.type,
  array_agg(channel_performances.*) AS channel_performances
FROM channels
LEFT JOIN channel_performance ON channels.id = channel_performance.channel_id
GROUP BY channels.id;

My issue is that I don't know how to use / when to use the array_agg - do I have to use raw SQL ? In Prisma, I could just do a findMany for channels w/ include the channel_performances. In my case the objective is to retrieve the channels with inside, the channel_performances as array for each channels. Do you know how to do that in Drizzle ORM ? Thank you !

14 Replies

Andrii Sherman•2y ago

You can use few approaches for that 1. Just use simple select with join and then aggregate results in your code. Great example with users+cities, that is actually the same you want 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.

Andrii Sherman•2y ago

Second approach would be to use json_agg I'm afraid I can't find it so fast, but I believe @rphlmr has an example for you And we are going to prepare first class support for json_agg. So you won't need to even write it. Syntax will be close as Prisma, but will be definitely in another layer on top of drizzle core. As a helper @rphlmr Sorry to mention you twice sweating

rphlmr ⚡•2y ago

https://gist.github.com/rphlmr/0d1722a794ed5a16da0fdf6652902b15#file-jsonagg_jsonaggbuildobject-ts 🙂

Gist

Drizzle snippets

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

Andrii Sherman•2y ago

you're the best, thanks!

sevenwestonroadsOP•2y ago

Thanks, I'll go with the first implementation for the sake of simplicity. However, I got an issue in the code;

import { InferModel } from 'drizzle-orm';

type User = InferModel<typeof users>;
type City = InferModel<typeof cities>;

const rows = await db
  .select({
    city: cities,
    user: users,
  })
  .from(cities)
  .leftJoin(users, eq(users.cityId, cities.id));

const result = rows.reduce<Record<number, { city: City; users: User[] }>>(
  (acc, row) => {
    const city = row.city;
    const user = row.user;

    if (!acc[city.id]) {
      acc[city.id] = { city, users: [] };
    }

    if (user) {
      acc[cityId].users.push(user);
    }

    return acc;
  },
  {},
);

import { InferModel } from 'drizzle-orm';

type User = InferModel<typeof users>;
type City = InferModel<typeof cities>;

const rows = await db
  .select({
    city: cities,
    user: users,
  })
  .from(cities)
  .leftJoin(users, eq(users.cityId, cities.id));

const result = rows.reduce<Record<number, { city: City; users: User[] }>>(
  (acc, row) => {
    const city = row.city;
    const user = row.user;

    if (!acc[city.id]) {
      acc[city.id] = { city, users: [] };
    }

    if (user) {
      acc[cityId].users.push(user);
    }

    return acc;
  },
  {},
);

Where is defined cityId in the if scoped statement ? My TS gives me an error of an undefined const.

Andrii Sherman•2y ago

oh, maybe a typo will update docs, thanks! I guess you need to use city.id

sevenwestonroadsOP•2y ago

I figured it out ! :)) Thanks again @Andrii Sherman you're the G

jacksn•2y ago

do you have any suggestions on making this work with a nested relation?

// ⚠️ Potential for SQL injections, so you shouldn't allow user-specified key names
export function jsonBuildObject<T extends Record<string, AnyColumn>>(shape: T) {
  const chunks: SQL[] = [];

  Object.entries(shape).forEach(([key, value]) => {
    if (chunks.length > 0) {
      chunks.push(sql.raw(`,`));
    }
    chunks.push(sql.raw(`'${key}',`));
    chunks.push(sql`${value}`);
  });

  return sql<InferColumnsDataTypes<T>[]>`json_build_object(${sql.fromList(
    chunks,
  )})`;
}

// ⚠️ Potential for SQL injections, so you shouldn't allow user-specified key names
export function jsonAggBuildObject<T extends Record<string, AnyColumn>>(
  shape: T,
) {
  return sql<InferColumnsDataTypes<T>[]>`coalesce(json_agg(${jsonBuildObject(
    shape,
  )}), '[]')`;
}

// ⚠️ Potential for SQL injections, so you shouldn't allow user-specified key names
export function jsonBuildObject<T extends Record<string, AnyColumn>>(shape: T) {
  const chunks: SQL[] = [];

  Object.entries(shape).forEach(([key, value]) => {
    if (chunks.length > 0) {
      chunks.push(sql.raw(`,`));
    }
    chunks.push(sql.raw(`'${key}',`));
    chunks.push(sql`${value}`);
  });

  return sql<InferColumnsDataTypes<T>[]>`json_build_object(${sql.fromList(
    chunks,
  )})`;
}

// ⚠️ Potential for SQL injections, so you shouldn't allow user-specified key names
export function jsonAggBuildObject<T extends Record<string, AnyColumn>>(
  shape: T,
) {
  return sql<InferColumnsDataTypes<T>[]>`coalesce(json_agg(${jsonBuildObject(
    shape,
  )}), '[]')`;
}

Allowing for usage like this:

        users: jsonAggBuildObject({
          id: user.id,
          name: user.name,
          image: user.image,
          tasks: jsonBuildObject({ id: task.id, title: task.title }),
        }),

        users: jsonAggBuildObject({
          id: user.id,
          name: user.name,
          image: user.image,
          tasks: jsonBuildObject({ id: task.id, title: task.title }),
        }),

However, I cant really figure out how to stop typescript from complaining here and infer the correct type for tasks Some builtin feature to support such aggregation would be a great addition to the library, I really like what you guys built! @Raphaël Moreau

rphlmr ⚡•2y ago

🧐 even if TypeScript complains, does it works? I’ll try to see but right now I have no idea 😅

jacksn•2y ago

Yeah, it works but i have no idea on how to make it work for typescript

rphlmr ⚡•2y ago

I have to build a repro to test and debug any chance you have something public I can pull ? I know the issue but I don't know how to solve it. jsonAggBuildObject should take Record<string, AnyColumn> or the return type of jsonBuildObject as arg. But jsonBuildObject type relies on T and T can be of type ReturnType of jsonBuildObject. So their is a loop for TS :/

Noahh•2y ago

I'm very interested in this topic, I understand why Drizzle doesn't have this functionality but I'm really looking to replicate it as soon as possible (especially nested includes). My use-case schema is this:

export const menuItems = pgTable('menu_items', {
  id: uuid('id').defaultRandom().primaryKey(),
  name: text('name').notNull()
});

export const modifierGroups = pgTable('modifier_groups', {
  id: uuid('id').defaultRandom().primaryKey(),
  name: text('name').notNull()
});

export const menuItemModifierGroups = pgTable(
  'menu_item_modifier_groups',
  {
    menuItemId: uuid('menu_item_id')
      .notNull()
      .references(() => menuItems.id),
    modifierGroupId: uuid('modifier_group_id')
      .notNull()
      .references(() => modifierGroups.id)
  },
  (table) => ({
    menuItemIdModifierGroupIdOrderPk: primaryKey(
      table.menuItemId,
      table.modifierGroupId
    ),
  }),
);

export const modifiers = pgTable('modifiers', {
  id: uuid('id').defaultRandom().primaryKey(),
  name: text('name')
});

export const modifierGroupModifiers = pgTable(
  'modifier_group_modifiers',
  {
    modifierGroupId: uuid('modifier_group_id')
      .notNull()
      .references(() => modifierGroups.id),
    modifierId: uuid('modifier_id')
      .notNull()
      .references(() => modifiers.id)
  },
  (table) => ({
    modifierGroupIdModifierIdOrderPk: primaryKey(
      table.modifierGroupId,
      table.modifierId
    ),
  }),
);

export const menuItems = pgTable('menu_items', {
  id: uuid('id').defaultRandom().primaryKey(),
  name: text('name').notNull()
});

export const modifierGroups = pgTable('modifier_groups', {
  id: uuid('id').defaultRandom().primaryKey(),
  name: text('name').notNull()
});

export const menuItemModifierGroups = pgTable(
  'menu_item_modifier_groups',
  {
    menuItemId: uuid('menu_item_id')
      .notNull()
      .references(() => menuItems.id),
    modifierGroupId: uuid('modifier_group_id')
      .notNull()
      .references(() => modifierGroups.id)
  },
  (table) => ({
    menuItemIdModifierGroupIdOrderPk: primaryKey(
      table.menuItemId,
      table.modifierGroupId
    ),
  }),
);

export const modifiers = pgTable('modifiers', {
  id: uuid('id').defaultRandom().primaryKey(),
  name: text('name')
});

export const modifierGroupModifiers = pgTable(
  'modifier_group_modifiers',
  {
    modifierGroupId: uuid('modifier_group_id')
      .notNull()
      .references(() => modifierGroups.id),
    modifierId: uuid('modifier_id')
      .notNull()
      .references(() => modifiers.id)
  },
  (table) => ({
    modifierGroupIdModifierIdOrderPk: primaryKey(
      table.modifierGroupId,
      table.modifierId
    ),
  }),
);

Basically, a whole bunch of nested stuff. Ideally, I could like this to come out to be

{
  "id": "...",
  "name": "...",
  "modifierGroups": [{
    "id": "...",
    "name": "...",
    "modifiers": [{
      "id": "...",
      "name": "..."
    },
    {
      "id": "...",
      "name": "..."
    }]
  },
  {
    "id": "...",
    "name": "...",
    "modifiers": [{
      "id": "...",
      "name": "..."
    },
    {
      "id": "...",
      "name": "..."
    }]
  }
  ]
}

{
  "id": "...",
  "name": "...",
  "modifierGroups": [{
    "id": "...",
    "name": "...",
    "modifiers": [{
      "id": "...",
      "name": "..."
    },
    {
      "id": "...",
      "name": "..."
    }]
  },
  {
    "id": "...",
    "name": "...",
    "modifiers": [{
      "id": "...",
      "name": "..."
    },
    {
      "id": "...",
      "name": "..."
    }]
  }
  ]
}

I was able to get it so that each menu item has a modifierGroups list, but I'm not sure where to go from there to get modifiers in each modifierGroups item. Currently, I have

const items = await this.drizzleService.db
      .select({
        ...getTableColumns(menuItems),
        modifierGroups: jsonAggBuildObject({
          ...getTableColumns(modifierGroups),
        }),
      })
      .from(menuItems)
      .leftJoin(
        menuItemModifierGroups,
        eq(menuItems.id, menuItemModifierGroups.menuItemId),
      )
      .leftJoin(
        modifierGroups,
        eq(menuItemModifierGroups.modifierGroupId, modifierGroups.id),
      )
      .groupBy(menuItems.id);

const items = await this.drizzleService.db
      .select({
        ...getTableColumns(menuItems),
        modifierGroups: jsonAggBuildObject({
          ...getTableColumns(modifierGroups),
        }),
      })
      .from(menuItems)
      .leftJoin(
        menuItemModifierGroups,
        eq(menuItems.id, menuItemModifierGroups.menuItemId),
      )
      .leftJoin(
        modifierGroups,
        eq(menuItemModifierGroups.modifierGroupId, modifierGroups.id),
      )
      .groupBy(menuItems.id);

Which gives me

[
      {
        "id": "317078bd-6265-4156-986c-085bdf297765",
        "name": "SmashBurger",
        "modifierGroups": [
          {
            "id": "41fe9f04-c772-4348-b7b1-185e57e9a38b",
            "name": "Toppings"
          },
          {
            "id": "41fe9f04-c772-4348-b7b1-185e57e9a38b",
            "name": "Toppings"
          },
          {
            "id": "0fd458f3-128a-4787-8253-4d288efcf7c4",
            "name": "Double Up"
          },
          {
            "id": "0fd458f3-128a-4787-8253-4d288efcf7c4",
            "name": "Double Up"
          }
        ]
      }
    ]

[
      {
        "id": "317078bd-6265-4156-986c-085bdf297765",
        "name": "SmashBurger",
        "modifierGroups": [
          {
            "id": "41fe9f04-c772-4348-b7b1-185e57e9a38b",
            "name": "Toppings"
          },
          {
            "id": "41fe9f04-c772-4348-b7b1-185e57e9a38b",
            "name": "Toppings"
          },
          {
            "id": "0fd458f3-128a-4787-8253-4d288efcf7c4",
            "name": "Double Up"
          },
          {
            "id": "0fd458f3-128a-4787-8253-4d288efcf7c4",
            "name": "Double Up"
          }
        ]
      }
    ]

volks•2y ago

@Noahh My usual approach for these complicated things is to first write it out in raw SQL and then translate that to Drizzle. Its much easier to reason with

jacksn•2y ago

I think this would be best implemented by getting the modifierGroups in a subquery

Gaming

Programming

How to reproduce a Prisma `include` statement for arrays of related entities without SQL?