Drizzle TeamDT
Drizzle Team•13mo ago
Tim

Can't seem to $count in multi relation query

I trying to get the count of "clicks" from a table 2 relations deep.
Querying for posts by a creators id, getting each posts link for a given guild id, and counting a clicks table tracking clicks for a given link.

I can't for the life of me find a nice way to do this in drizzle any assistance would be helpful!

Here's the current prisma implementation.
const posts = await prisma.post.findMany({
  include: {
    links: {
      include: { _count: { select: { clicks: true } } },
      where: { guild_id },
    },
  },
  where: { creator_id: creator.id },
  orderBy: { created_at: 'desc' },
  take: 15,
});

and relevant output
{
  "posts": [
    {
      "links": [
        {
          "id": "hywn4k45s149me2fw16qvwgw",
          "post_id": "7433873212326300959",
          "destination_id": "1177914756225585253",
          "status": null,
          "created_at": "2024-11-05T19:06:43.188Z",
          "guild_id": "313591755180081153",
          "_count": {
            "clicks": 42
          }
        }
      ]
    }
  ]
}


This is what I assumed would work in drizzle based on https://orm.drizzle.team/docs/select#count but getting a really weird column posts_links.link_id does not exist error.
It's like it's not using shareClicks from import { shareClicks } from '@drizzle/schema'; but instead referencing something else.
const posts = await db.query.posts.findMany({
  with: {
    links: {
      extras: {
        clicks: db
          .$count(shareClicks, eq(shareClicks.link_id, shareLinks.id))
          .as('clicks'),
      },
      where: (l, { eq }) => eq(l.guild_id, guild_id),
    },
  },
  where: (p, { eq }) => eq(p.creator_id, creator.id),
  orderBy: (p, { desc }) => desc(p.created_at),
  limit: 15,
});
Drizzle ORM - Select
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
Was this page helpful?