Count of one-to-many relation in query

Migrating from Prisma - I had something like the following:

export async function getAllOrganizations() {
  return db.organization.findMany({
    include: {
      _count: {
        select: { individuals: true },
      },
    },
  });
}

export async function getAllOrganizations() {
  return db.organization.findMany({
    include: {
      _count: {
        select: { individuals: true },
      },
    },
  });
}

This effectively fetched a count of all the individuals underneath an organization (a one-to-many relationship). How can I do something similar in Drizzle? I took a look at passing in extras to the query, but it noted that aggregations weren't a possibility in extras currently. Am I missing something?

1 Reply

AlcaponeYou•17mo ago

I'm curious if this will be supported in the future too. Oh wait it's already supported, the docs just aren't updated. eg:

someRelationalQuery: {
   columns: {...},
   with: {
      something: true
   },
   extras: {
      count: sql`(SELECT count(*) from relation_table)`.as('count')
   }
}

someRelationalQuery: {
   columns: {...},
   with: {
      something: true
   },
   extras: {
      count: sql`(SELECT count(*) from relation_table)`.as('count')
   }
}

Note you have to wrap the query in parenthesis.

Gaming

Programming

Count of one-to-many relation in query