Drizzle Team•7mo ago

Fetching many to many along with some aggregation using select

I am trying to fetch many to many relation with drizzle select(). I need to use it because I also want to aggregate a value in nested relation ship Here is the schema

  const results = await db
    .select({
    })
    .from(BatchModel)
    .fullJoin(BatchToCustomer, eq(BatchModel.id, BatchToCustomer.batch_id))
    .fullJoin(CustomerModel, eq(BatchToCustomer.customer_id, CustomerModel.id))
    .groupBy(BatchModel.id)
    .all();

  const results = await db
    .select({
    })
    .from(BatchModel)
    .fullJoin(BatchToCustomer, eq(BatchModel.id, BatchToCustomer.batch_id))
    .fullJoin(CustomerModel, eq(BatchToCustomer.customer_id, CustomerModel.id))
    .groupBy(BatchModel.id)
    .all();

Tried several things but could not wrap my head around how to return something like below

[
  {
    name: "batch name",
    customers: [
      {
        name: "customer name",
        total: "item's total",
        items: [
          {
            name: "",
            price: "",
          },
        ],
      },
    ],
  },
];

[
  {
    name: "batch name",
    customers: [
      {
        name: "customer name",
        total: "item's total",
        items: [
          {
            name: "",
            price: "",
          },
        ],
      },
    ],
  },
];

client.ts

11 Replies

Torbaz•7mo ago

Have a look into this https://orm.drizzle.team/docs/rqb

Drizzle ORM - Query

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

Torbaz•7mo ago

If you otherwise want to just use the select functions, you will have to manually group things I believe

0xcafebabed•7mo ago

@Torbaz when using relational queries can we filter the top level table with fields from child table

Sillvva•7mo ago

Firstly, it would be easier to create your own posts rather than hijacking someone else's with an unrelated question. To answer your question, though. Not directly. Use the exists filter https://orm.drizzle.team/docs/operators#exists Example:

const dms = await q.dungeonMasters.findMany({
    with: {
        logs: {
            with: {
                character: {
                    columns: {
                        id: true,
                        name: true,
                        userId: true
                    }
                }
            }
        }
    },
    where: (dms, { or, eq, and, exists }) =>
        and(
            id
                ? and(or(eq(dms.owner, user.id), eq(dms.uid, user.id)), eq(dms.id, id))
                : or(eq(dms.owner, user.id), eq(dms.uid, user.id)),
            //Include the DM only if they have logs with associated characters
            exists(
                db
                    .select({ id: logs.id })
                    .from(logs)
                    .innerJoin(characters, eq(logs.characterId, characters.id))
                    .where(and(eq(logs.dungeonMasterId, dms.id)))
            )
        )
});

const dms = await q.dungeonMasters.findMany({
    with: {
        logs: {
            with: {
                character: {
                    columns: {
                        id: true,
                        name: true,
                        userId: true
                    }
                }
            }
        }
    },
    where: (dms, { or, eq, and, exists }) =>
        and(
            id
                ? and(or(eq(dms.owner, user.id), eq(dms.uid, user.id)), eq(dms.id, id))
                : or(eq(dms.owner, user.id), eq(dms.uid, user.id)),
            //Include the DM only if they have logs with associated characters
            exists(
                db
                    .select({ id: logs.id })
                    .from(logs)
                    .innerJoin(characters, eq(logs.characterId, characters.id))
                    .where(and(eq(logs.dungeonMasterId, dms.id)))
            )
        )
});

Drizzle ORM - Filters

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

Sillvva•7mo ago

As Torbaz pointed out, you can use the relational query builder, but you can't just cut out the intermediate table (BatchToCustomer) from the nested return object that way. To do that, would require constructing your own queries with the SQLite JSON aggregation functions. https://www.sqlite.org/json1.html

freddie•7mo ago

you can use it like:

const results = await db
    .select({
      name: BatchModel.name,
      customer: CustomerModel
    })
    .from(BatchModel)
    .fullJoin(BatchToCustomer, eq(BatchModel.id, BatchToCustomer.batch_id))
    .fullJoin(CustomerModel, eq(BatchToCustomer.customer_id, CustomerModel.id))
    .groupBy(BatchModel.id)
    .all();

const aggregated = aggregate({rows: results, id: "name", fields: {customers: "customer"}})

const results = await db
    .select({
      name: BatchModel.name,
      customer: CustomerModel
    })
    .from(BatchModel)
    .fullJoin(BatchToCustomer, eq(BatchModel.id, BatchToCustomer.batch_id))
    .fullJoin(CustomerModel, eq(BatchToCustomer.customer_id, CustomerModel.id))
    .groupBy(BatchModel.id)
    .all();

const aggregated = aggregate({rows: results, id: "name", fields: {customers: "customer"}})

lmk if that works for you! im doing some improvements on this function already, might release that as a little drizzle toolkit library later ok so I made a more reliable verson of that function but it uses lodash’s get

freddie•7mo ago

well discord wont let me paste the code here, so here it goes: https://tsplay.dev/wE2LgN

TS Playground - An online editor for exploring TypeScript and JavaS...

The Playground lets you write TypeScript or JavaScript online in a safe and sharable way.

Marcus - noxify•6mo ago

As alternative to lodash.get => https://youmightnotneed.com/lodash#get

You Might Not Need Lodash

A collection of "You might not need ${something}" resources

Marcus - noxify•6mo ago

or something else - found this thread while testing many 2 many relations and currently testing your provided solution. Question: Whats the reason for the fullJoin instead of leftJoin ?

  /**
   * database response is something like:
   * [
   *   { user: { id: 1, name: 'user1' }, group: { id: 1, name: 'group1' } },
   *   { user: { id: 1, name: 'user1' }, group: { id: 2, name: 'group2' } },
   *   { user: { id: 2, name: 'user2' }, group: { id: 1, name: 'group1' } }
   * ]
   * 
   * references:
   * - https://orm.drizzle.team/docs/joins#left-join
   * - https://orm.drizzle.team/docs/rqb#many-to-many
   */
  const rows = await db
    .select({
      user: getTableColumns(users),
      group: getTableColumns(groups),
    })
    .from(users)
    .leftJoin(usersToGroups, eq(usersToGroups.userId, users.id))
    .leftJoin(groups, eq(usersToGroups.groupId, groups.id));

  /**
   * reduce result is something like:
   * {
   *   '1': { user: { id: 1, name: 'user1' }, groups: [ [Object], [Object] ] },
   *   '2': { user: { id: 2, name: 'user2' }, groups: [ [Object] ] }
   * }
   * 
   * references:
   * - https://orm.drizzle.team/docs/joins#aggregating-results
   */
  const result = rows.reduce<Record<number, { user: User; groups: Group[] }>>(
    (acc, row) => {
      const user = row.user;
      const group = row.group;
      if (!acc[user!.id]) {
        acc[user!.id] = { user: user!, groups: [] };
      }
      if (group) {
        acc[user!.id].groups.push(group);
      }
      return acc;
    },
    {}
  );

  /**
   * transformed response is something like:
   * [
   *   { id: 1, name: 'user1', groups: [ [Object], [Object] ] },
   *   { id: 2, name: 'user2', groups: [ [Object] ] }
   * ]
   */
  const transformedResponse = Object.values(result).map((ele) => ({
    ...ele.user,
    groups: ele.groups,
  }));

  /**
   * database response is something like:
   * [
   *   { user: { id: 1, name: 'user1' }, group: { id: 1, name: 'group1' } },
   *   { user: { id: 1, name: 'user1' }, group: { id: 2, name: 'group2' } },
   *   { user: { id: 2, name: 'user2' }, group: { id: 1, name: 'group1' } }
   * ]
   * 
   * references:
   * - https://orm.drizzle.team/docs/joins#left-join
   * - https://orm.drizzle.team/docs/rqb#many-to-many
   */
  const rows = await db
    .select({
      user: getTableColumns(users),
      group: getTableColumns(groups),
    })
    .from(users)
    .leftJoin(usersToGroups, eq(usersToGroups.userId, users.id))
    .leftJoin(groups, eq(usersToGroups.groupId, groups.id));

  /**
   * reduce result is something like:
   * {
   *   '1': { user: { id: 1, name: 'user1' }, groups: [ [Object], [Object] ] },
   *   '2': { user: { id: 2, name: 'user2' }, groups: [ [Object] ] }
   * }
   * 
   * references:
   * - https://orm.drizzle.team/docs/joins#aggregating-results
   */
  const result = rows.reduce<Record<number, { user: User; groups: Group[] }>>(
    (acc, row) => {
      const user = row.user;
      const group = row.group;
      if (!acc[user!.id]) {
        acc[user!.id] = { user: user!, groups: [] };
      }
      if (group) {
        acc[user!.id].groups.push(group);
      }
      return acc;
    },
    {}
  );

  /**
   * transformed response is something like:
   * [
   *   { id: 1, name: 'user1', groups: [ [Object], [Object] ] },
   *   { id: 2, name: 'user2', groups: [ [Object] ] }
   * ]
   */
  const transformedResponse = Object.values(result).map((ele) => ({
    ...ele.user,
    groups: ele.groups,
  }));

Checked the snippet from freddie after writing the snippet and it seems that his solution is more generic and reusable.

Marcus - noxify•6mo ago

Thanks Freddie for adding it to your repo ❤️ https://github.com/fredericoo/drizzle-toolbelt

GitHub

GitHub - fredericoo/drizzle-toolbelt

Contribute to fredericoo/drizzle-toolbelt development by creating an account on GitHub.

freddie•6mo ago

btw lodash’s get is well tested and gets bundled with tree shaking so i chose over rolling my own recursive

Gaming

Programming

Fetching many to many along with some aggregation using select