Sylphritz
Sylphritz
Explore posts from servers
CDCloudflare DevelopersDTDrizzle Team
PostsComments
DTDrizzle Team
Created by Sylphritz on 9/29/2023 in #help
SQLite query returns an incorrect row count in extras
The query below doesn't seem to return the correct count for the number of Posts under a Category. It just counts the whole Posts table instead for some reason. 
db.query.categories.findMany({
    offset,
    limit: perPage,
    where: eq(categories.userId, userId),
    extras: (category, { sql }) => {
      return {
        postCount:
          sql`(SELECT count(*) from posts WHERE category_id = ${category.id})`.as(
            'post_count'
          ),
      }
    },
  })
db.query.categories.findMany({
    offset,
    limit: perPage,
    where: eq(categories.userId, userId),
    extras: (category, { sql }) => {
      return {
        postCount:
          sql`(SELECT count(*) from posts WHERE category_id = ${category.id})`.as(
            'post_count'
          ),
      }
    },
  })
I have 1 post in a category but the count returned from this query is the number of total rows in the Posts table instead and not the count filtered by a category ID.
3 replies