Wrong count query for findAndCountAll with include

[pola88]

Hi guys, I updated to the version 3.30.2 and when I try to do findAndCountAll with include, the count query is wrong, example:

Product.findAndCountAll({ include: [ { model: Feature, as: 'features' }]});

I get:

SELECT count("Product"."id") AS "count" FROM "products" AS "Product" LEFT OUTER JOIN ("products_features" AS "features.products_features" INNER JOIN "features" AS "features" ON "features"."id" = "features.products_features"."feature_id") ON "Product"."id" = "features.products_features"."product_id";

expected:

SELECT count(*) AS "count" FROM "products" AS "Product";

if I have a product with more than 2 features, it will be counted twice

Sequelize: 3.30.2
dialect: pg

[ggrimbert]

If you specify an include, it will be passed to the count query, that's how it works.

If you don't want the JOIN, don't pass the include in the findAndCountAll

[kingjerod]

Pass distinct: true into the query and it will work how you want it to. It will still have the included data.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment 🙂

[arvinhubert]

Wrong count with where clause

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment 🙂

[magmel48]

Personally I think LEFT JOIN's should not be here as well. Count of rows cannot be changed if we use this type of JOIN or just omit it, but result query will be faster then.

[dalepo]

I think making this into a separate query would be a better approach since you might not want to use distinct in your query, it can lead into unwanted performance issues.

[mltsy]

For posterity: Using a separate query here seems like the right thing to do IF you're concerned about performance. with findAndCountAll, Sequelize is already performing two queries (a SELECT count(...) and a SELECT table.x, ...). The counting issue exists because the "includes" are joined to both of these queries. By using distinct: true the count query turns into SELECT count(DISTINCT(table.id)) as "count" but still joins all the included tables, and then collapses them by table.id - not the most efficient way to do that. It would be great if there were a way to "not include the includes" for the count query, but I don't know of a way to do that. However, you can emulate findAndCountAll, including its async nature, with something like this:

async function findAndCountApples(appleType)
  const where = { apply_type: appleType }
  const asyncRows = appleModel.findAll({
    where,
    include: ...,
    offset: ...
    limit: ...
  })
  const asyncCount = model.count({where})
  return Promise.all([asyncRows, asyncCount]).then(([rows, count]) => ({rows, count}))
}

Of course, if the full query with all joins is not an expensive/slow query, using distinct:true works just fine!