How do I fetch a table with relation table count?

[henokyehulu]

Lets say I have two tables

1. users
2. posts

I want to fetch a user and number of posts created by this particular user. I know this request can be made very easily in Prisma by using "_count: relation_table". But I can't seem to find a clean way to do this on Drizzle. Please help me guys 🙏🙏🙏

[fkozlicki]

Drizzle ORM does not provide such similar to Prisma solution to retrieve count of rows of a relation table (AFAIK). You can do it using select:

import { count, eq } from 'drizzle-orm';
import { countries, cities } from './schema';
// Count cities in each country
await db
  .select({
    country: countries.name,
    citiesCount: count(cities.id),
  })
  .from(countries)
  .leftJoin(cities, eq(countries.id, cities.countryId))
  .groupBy(countries.id)
  .orderBy(countries.name);

You can find it here, in the very last example