Drizzle Team•3mo ago

Counting many-to-many items

Hello, any suggestions how should I query for item and count the numbers of times it is referenced via many-to-many table?

const items = await db.select({
  ...getTableColumns(A),
  countB: // count of B items on A
  countC: // count of C items on A
})
  .from(A)
  .groupBy(A.id)

const items = await db.select({
  ...getTableColumns(A),
  countB: // count of B items on A
  countC: // count of C items on A
})
  .from(A)
  .groupBy(A.id)

Given these tables:

export const A = mysqlTable(
  "A", {
    id: int("id").primaryKey().autoincrement(),
    name: varchar("name", { length: 191 }).notNull(),
  }
)

export const B = mysqlTable(
  "B", {
    id: int("id").primaryKey().autoincrement(),
    name: varchar("name", { length: 191 }).notNull(),
  }
)

export const C = mysqlTable(
  "C", {
    id: int("id").primaryKey().autoincrement(),
    name: varchar("name", { length: 191 }).notNull(),
  }
)

export const AB = mysqlTable(
  "AB", {
    A: int("A").notNull().references(() => A.id),
    B: int("B").notNull().references(() => B.id),
  }
)

export const AC = mysqlTable(
  "AC", {
    A: int("A").notNull().references(() => A.id),
    C: int("C").notNull().references(() => C.id),
  }
)

export const A = mysqlTable(
  "A", {
    id: int("id").primaryKey().autoincrement(),
    name: varchar("name", { length: 191 }).notNull(),
  }
)

export const B = mysqlTable(
  "B", {
    id: int("id").primaryKey().autoincrement(),
    name: varchar("name", { length: 191 }).notNull(),
  }
)

export const C = mysqlTable(
  "C", {
    id: int("id").primaryKey().autoincrement(),
    name: varchar("name", { length: 191 }).notNull(),
  }
)

export const AB = mysqlTable(
  "AB", {
    A: int("A").notNull().references(() => A.id),
    B: int("B").notNull().references(() => B.id),
  }
)

export const AC = mysqlTable(
  "AC", {
    A: int("A").notNull().references(() => A.id),
    C: int("C").notNull().references(() => C.id),
  }
)

Solution:

I ended up doing this: ```js const items = await db .select({ ...getTableColumns(A),...

Jump to solution

2 Replies

Varna•3mo ago

I'm migrating from this Prisma query:

  return prisma.A.findMany({
    include: {
      _count: {
        select: {
          B: true,
          C: true
        }
      }
    }
  })

  return prisma.A.findMany({
    include: {
      _count: {
        select: {
          B: true,
          C: true
        }
      }
    }
  })

Solution

Varna•3mo ago

I ended up doing this:

  const items = await db
    .select({
      ...getTableColumns(A),
      B: count(AB.B),
      C: count(AC.C)
    })
    .from(A)
    .orderBy(A.name)
    .leftJoin(AB, eq(AB.A, A.id))
    .leftJoin(AC, eq(AC.A, C.id))
    .groupBy(A.id)

  const items = await db
    .select({
      ...getTableColumns(A),
      B: count(AB.B),
      C: count(AC.C)
    })
    .from(A)
    .orderBy(A.name)
    .leftJoin(AB, eq(AB.A, A.id))
    .leftJoin(AC, eq(AC.A, C.id))
    .groupBy(A.id)

Gaming

Programming

Counting many-to-many items