Drizzle Team•2mo ago

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,
});

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
          }
        }
      ]
    }
  ]
}

{
  "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,
});

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,
});