Drizzle Team•15mo ago

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.

1 Reply

SylphritzOP•15mo ago

Update: this is really weird, it just keeps returning 2 for all categories regardless of how many posts I have in each of them... Ah, I see. It seems the query is confused because both tables have a column named "id" so I have to hardcode it like so:

sql`(SELECT count(*) from posts WHERE category_id = categories.id)`

sql`(SELECT count(*) from posts WHERE category_id = categories.id)`

Is there a solution that achieve the same result without hardcoding it like this? EDIT: Never mind, I'm dumb. I can just do this:

sql`(SELECT count(*) from ${posts} WHERE ${posts.categoryId} = ${categories}.${categories.id})`

sql`(SELECT count(*) from ${posts} WHERE ${posts.categoryId} = ${categories}.${categories.id})`

Gaming

Programming

SQLite query returns an incorrect row count in extras