sequelize: findAndCountAll with `GROUP BY` clause causes count to be an array of objects

just use “result.count.length” instead “result.count” when you use group by in findAndCountAll.

Thanks for getting back to me, @sushantdhiman however that isn’t exactly my problem. My problem is the value of count from findAndCountAll. If we use your example, and pass that into findAndCountAll, this would be the result we’d get:

{
  count: [
    { count: "..." },
    { count: "..." },
    { count: "..." },
    { count: "..." },
    ...
    { count: "..." },
  ],
  rows: [{
    ...
    dataValues: {
      count: "...",
      dept_id: "..."
    }
  }, ...]
}

The objects from rows isn’t the problem, it’s exactly what I expect it to be. The problem is with count. I expected that to be an integer representing the total number of rows. Looking at the source (https://github.com/sequelize/sequelize/blob/master/lib/model.js#L1653-L1665), it seems like count is expected to be integer too. However, it’s not. It’s an array of objects.

This kind of looks like a bug to me.

I am having the same issue as well, findAndCountAll doesn’t ignore the group by for the sub-queries.

const Item = sequelize.define('item', {
    id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    name: {
        type: DataTypes.STRING,
        allowNull: false,
    },
    price: {
        type: DataTypes.INTEGER,
        default: 10
    }
});

const User = sequelize.define('user', {
    id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    firstname: {
        type: DataTypes.STRING,
        allowNull: false,
    },
    lastname: {
        type: DataTypes.STRING,
        allowNull: false,
    },
});

User.findAndCountAll({
    distinct: true,
    offset: 0,
    order: `id LIMIT 25`,
    where:  {
        city: 'paris'
    },
    attributes: [
        'firstname', 'lastname', 'id',
        [sequelize.fn('SUM', sequelize.col('items.price')), 'totalItemsWorth'],
        [sequelize.fn('COUNT', sequelize.fn('DISTINCT', sequelize.col('items.id'))), 'itemsCount'],
    ],
    include: [{
        model: sequelize.models.items,
        attributes: [],
        required: false
    }],
    group: ['user.id'],
});

expected result:

{ count: 40, rows: [users] }

actual result:

{
count: [{ count: 1 }, { count: 1 }, ...],
rows: [...]
}

Did someone find a solution for this or has this been fixed ?

Thanks,

the same issue solved ONLY by distinct: true (thanks, piraveen)

User.findAndCountAll({
    distinct: true,
...

result.count.length will not improve you performance as query will fetch all the records from table. And suppose if your result set has 10 lac records. I faced the same situation so instead of using findAndCountAll, I called two saperate function findAll and count with below option (remove group and set attribute to undefined in count function). Below is just an example. My actual query has multiple joins and search filter.

let count = await model.count({ attributes: undefined, order: [ [‘createdAt’, ‘DESC’] ] });

let rows = await model.findAll({
  attributes:['id', 'createdAt'],
  order: [
    ['createdAt', 'DESC']
  ],
  group: [`id`],
  limit: 10,
  offset: 0,
  distinct: true,
  nest: true,
  raw: true
}); 

After studying different database engine that we currently support, It seems we can effectively remove group from mysql and sqlite and it will work.

But we cant do same for the postgres and mssql. They don’t support selecting any additional parameters other than what involved in aggregation ( more details ) . There is no cross dialect solution available.

Sequelize will not support group with findAndCountAll ( group actually don’t make much sense with findAndCountAll either)

@davidvuong You can either use code from PR #6152 in your fork to have it work for mysql and sqlite. For other database I would suggest writing different queries for count and findfAll and combine with Promise.all

Just use “result.count.length/count.length” instead “result.count/count” when you use Group by in findAndCountAll function.

I also get this, but in my case I do want the Group BY since I have a HAVING clause to filter even more.

It is almost like when a GROUP by is being applied that the count query should be wrapped with a SUM query around it?

What you are doing?

I’m trying to get the number of rows returned in using findAndCountAll with a GROUP BY clause however, instead of a single integer, I get an array of { count: x } objects.

const attributes = ['feed_id', [this.store.fn('COUNT', this.store.col('feed_id')), 'count']];
const where = { createdAt: { $gte: start, $lte: end } };
const group = ['feed_id'];
const order = [['count', 'DESC']];
const query = { where, attributes, order, group };

model.findAndCountAll(query).then(data => {
  console.log(data.count);
  console.log(data.rows);
});

What do you expect to happen?

I expected a single integer in count and an array of objects in row.

What is actually happening?

{
  count: [
    { count: '11' },
    { count: '20' },
    { count: '17' },
    { count: '17' },
    { count: '11' },
    ...
    { count: '12' },
    { count: '10' },
    { count: '18' } ],
  rows: [{
    dataValues: [Object],
    _previousDataValues: [Object],
    _changed: {},
    '$modelOptions': [Object],
    '$options': [Object],
    hasPrimaryKeys: true,
    __eagerlyLoadedAssociations: [],
    isNewRecord: false
  }, ...]
}

I got an array of count objects.

For me, the workaround was to stop using findAndCountAll and make an explicit model.count transaction before the findAll. I then removed the GROUP BY clause and replaced the COUNT attribute with COUNT(DISTINCT field).

SELECT COUNT(feed_id) AS "count" FROM "history" AS "history" WHERE ("history"."created_at" >= '2016-04-22 07:24:26.038 +00:00' AND "history"."created_at" <= '2016-06-21 07:24:26.040 +00:00') GROUP BY "feed_id";

SELECT COUNT(DISTINCT feed_id) AS "count" FROM "history" AS "history" WHERE ("history"."created_at" >= '2016-04-22 07:24:26.038 +00:00' AND "history"."created_at" <= '2016-06-21 07:24:26.040 +00:00')

Is this something sequelize can do automatically when it sees a GROUP BY clause?

Dialect: postgres Database version: 9.5.2 Sequelize version: 3.23.0

You can use the count.length it’s the same as count

Hi @DrTerr, don’t worry about it 😃 You are welcome to open a PR even if your work in unfinished, there is no problem in having a PR which is in progress, and if you happen to not have time to finish it, then someone else can continue the work 😃 For example, if you start the work and pause it for a while, maybe I or another maintainer or any contributor indeed can finish it up 😃

Hi @papb, I just need time, I have been quite busy. Though a question, do I need to add unit test to test added functionality? Or can you or someone else help with it?

Yes, adding an option such as “wrapSum” should be relatively straight forward. I am right now using Sequelize 4, but I don’t think those core parts are much different from 5. As it is mostly about updating a code path of count function to instead of using an “aggregate” it would generate use similar query to “findAll” but instead it would omit same parameters as count operation. Minor issue I see is that “selectQuery” from “query-generator.js” would need to get an option to omit “;” from the end, since it seems redundant having to remove it for wrapping a query. I am afraid I don’t have much time for PR, but Ill have a look at what is required. Having this feature native to Sequelize would be immensely useful for me and other people. A documented example also would be helpful for others who would stumble upon similar issue and case as I am. I think I will have a few spare days next week to contribute a “wrapSum”, but I can provide my current work around for you and others to investigate. It is made for Sequelize 4, as I am currently hooked in deeply in to Sequelize objects for introspection.

I see, thanks! I don’t have time to look further into this right now though, glad that you found a workaround. If you want to submit a PR in which you add a new feature like a wrapSum: true or something, we could look into it I guess!

just use “result.count.length” instead “result.count” when you use group by in findAndCountAll.

My previous comment was not the perfect solution but I solved my situations on that way. If you still have any issue then you need to check up your query first. If you quite sure that your query has no problem then you need to use this “subquery: false;” I solved all my querying issue with this. I hope you guys take this medicine and release from the querying pain. I leave my code below.

models.items.findAndCountAll({
    subQuery: false,
    where: whereStatement,
    include: [{
        model: models.store_items,
        where: whereStatementStoreItems,
        required: true
    }],
    order: orderStatement,
    group: ['items.id']
}).then(function(result) {
    resolve(result);
}).catch(function (err) {
    log.error(err);
    reject(err);
});

just use “result.count.length” instead “result.count” when you use group by in findAndCountAll.

it worked for me

The documentation is not the best. Also the interface should be updated to reflect the possible return types.

   findAndCountAll<TCustomAttributes>(options?: FindOptions<TAttributes & TCustomAttributes>): Promise<{ rows: TInstance[], count: number }>;

should be

   findAndCountAll<TCustomAttributes>(options?: FindOptions<TAttributes & TCustomAttributes>): Promise<{ rows: TInstance[], count: number | Array<{count: number}> }>;

Also, if you don’t support group by with findAndCountAll, it should throw an error. Happy to send a PR if you’re interested.