Method to count associations?

[Fauntleroy]

Is there currently any way to use .count() with associations? I'm constructing an API endpoint and I'd like to be able to return a total items count while doing a .getAssociations() call with a limit.

[sdepold]

Hmm you could try to add attributes: ["", "count()"]. Otherwise you
could exec a count on that associatef model with a where clause and
the id of the relevant instance.

Am 23.07.2012 um 01:04 schrieb Timothy Kempf
reply@reply.github.com:

Is there currently any way to use .count() with associations? I'm constructing an API endpoint and I'd like to be able to return a total items count while doing a .getAssociations() call with a limit.

---

Reply to this email directly or view it on GitHub:
https://github.com/sdepold/sequelize/issues/222

[sdepold]

Obviously thats a hack. I got your point. :)

Am 23.07.2012 um 01:04 schrieb Timothy Kempf
reply@reply.github.com:

Is there currently any way to use .count() with associations? I'm constructing an API endpoint and I'd like to be able to return a total items count while doing a .getAssociations() call with a limit.

---

Reply to this email directly or view it on GitHub:
https://github.com/sdepold/sequelize/issues/222

[Prinzhorn]

+1

Now that you will be able to do include: ['Other'] in 1.6, it would be great to just get the count of all associated Others without actually fetching them. Use cases include blogpost comments, where you display "This thingy has 123 comments" without actually needing the comments themselves.

[lemon-tree]

This is something I can also see myself using quite frequently (even more so with the new association inclusion) and I'm not sure if it is achievable currently using a raw query with a join. There are a number of questions on StackOverflow that provide sample query structure for doing a join in conjunction with a count. Starting from the link below, the related section has links to most other solutions (with varying structure):

http://stackoverflow.com/questions/3141463/inner-join-with-count-on-three-tables?rq=1

Currently I'm achieving a similar effect with the query chainer, but doing multiple SQL queries where one could work seems excessive.

Ta

[sdepold]

yeah, I also noticed, that smth like this is missing :) will add this … dunno when, but I will add it ^^

Sascha Depold
Gesendet mit Sparrow (http://www.sparrowmailapp.com/?sig)

Am Mittwoch, 28. November 2012 um 01:56 schrieb lemon-tree:

This is something I can also see myself using quite frequently (even more so with the new association inclusion) and I'm not sure if it is achievable currently using a raw query with a join. There are a number of questions on StackOverflow that provide sample query structure for doing a join in conjunction with a count. Starting from the link below, the related section has links to most other solutions (with varying structure):
http://stackoverflow.com/questions/3141463/inner-join-with-count-on-three-tables?rq=1
Currently I'm achieving a similar effect with the query chainer, but doing multiple SQL queries where one could work seems excessive.
Ta

—
Reply to this email directly or view it on GitHub (https://github.com/sdepold/sequelize/issues/222#issuecomment-10785144).

[anami]

Hello,

I fully understand that this issue will be dealt with but I'm having trouble using the association 'hack' presented in this thread.. I was wondering if anyone can shed a light on this issue.

Although the SQL without the ticks is correct but Sequelize adds the ticks and renders the SQL to be invalid.

Is there any way to remove the ticks from the SQL that Sequelize generates?

Output

Executing: SELECT count(*) FROM Points WHERE Points.RouteId=2;

node_modules/sqlite3/lib/trace.js:28
throw err;
^
Error: SQLITE_ERROR: no such column: count(_)
--> in Database#all('SELECT count(_)FROMPointsWHEREPoints.RouteId=2;', [Function])
at module.exports.Query.run.database.serialize.executeSql
(node_modules/sequelize/lib/dialects/sqlite/query.js:35:52)
at Statement.module.exports.Query.run
(node_modules/sequelize/lib/dialects/sqlite/query.js:54:13)

-- Full file paths have been redacted --

*** Github markdown doesn't seem to display ticks in those SQL fields..

Code

r.getPoints(attributes: ["count(*)"]).success(points){ .. code .. }

[sdepold]

Dunno if this was already presented, but it would be possible to just get all the associations and use the length of its respective array. (Attention: hack hack hack)

(I assume that r is for route)

Route.findAll/find({ include: 'points' }).success(function(routes) {
  routes.forEach(function(route) {
    console.log(route.points.length)
  })
})

If you already have a route you could also do this:

Point.count({ where: { routeId: r.id }).success(function(count) {
  console.log(count)
})

[Lisio]

This issue is very important. I.e. there is no way to get the list of users with count of their posts. In Yii (php framework) I can do this with one line of code:
$users = User::model()->findAll(array('with' => 'postsCount'));

Using Sequelize I can only write:
User.findAll({include: ['Post']}).success(function(users) {
// Here we have doubled user records for each post and full content of all posts when we don't need it absolutely.
});

Sure I can change the business logic of application and include counter in user table or write the SQL query by myself, but this is not the good usage of ORM.

[mickhansen]

User.findAll({
  attributes: ['User.*', 'Post.*', [sequelize.fn('COUNT', 'Post.id'), 'PostCount']],
  include: [Post]
}

Should fix this, else let me know

[omdathetkan]

Dammmmm sequelize can do anything! Awesome!

[mickhansen]

@jorisa Haha thanks, we're getting there :)

[omdathetkan]

Actually, wouldn't this be more graceful to be able to do:

User.findAll({
    include: [{
        model: Post,
        attributes: [[models.sequelize.fn('COUNT', 'id'), 'items']]
    }]
})

If you're counting the associated rows you're usually not interested in the other attributes, you also don't need to know the actual table name.

I made this work by fudging around a bit (by no means tested or any good, but worked for my example above):

attributes  = include.attributes.map(function(attr) {
  return self.quoteIdentifier(as) + "." + self.quoteIdentifier(attr) + " AS " + self.quoteIdentifier(as + "." + attr)
})

with

// includeIgnoreAttributes is used by aggregate functions
if (options.includeIgnoreAttributes !== false) {
// Escape attributes
attributes = include.attributes.map(function(attr){
  var addTable = true

  if (attr instanceof Utils.literal) {
    return attr.toString(this)
  }

  if (attr instanceof Utils.fn || attr instanceof Utils.col) {
    return self.quote(attr)
  }

  if(Array.isArray(attr) && attr.length == 2) {
    if (attr[0] instanceof Utils.fn || attr[0] instanceof Utils.col) {
      attr[0].args = attr[0].args.map(function(attr) {
        return self.quoteIdentifier(as) + "." + self.quoteIdentifier(attr)
      })
      attr[0] = self.quote(attr[0])
      addTable = false
    }
    attr = [attr[0], self.quoteIdentifier(attr[1])].join(' as ')
  } else {
    attr = attr.indexOf(Utils.TICK_CHAR) < 0 && attr.indexOf('"') < 0 ? self.quoteIdentifiers(attr) : attr
  }

  if (options.include && attr.indexOf('.') === -1 && addTable) {
    attr = self.quoteIdentifier(options.table) + '.' + attr
  }

  return attr
})

[omdathetkan]

I'd also like to point out that Mick's solution will not do exactly what you expect.

User.findAll({
  attributes: ['User.*', 'Post.*', [sequelize.fn('COUNT', 'Post.id'), 'PostCount']],
  include: [Post]
}

Will be escaped incorrectly to COUNT('Post.id'), instead of COUNT(Post.id) (making it count the User row as well as NULL values of the Post table).

The correct query is:

User.findAll({
  attributes: ['User.*', 'Post.*', [sequelize.fn('COUNT', sequelize.col('Post.id')), 'PostCount']],
  include: [Post]
}

[mickhansen]

Right you are @jorisa

[gdw2]

I had to add group: [sequelize.col('User.id')] for this to work. Otherwise, it would only return a single row.

This tipped me off: http://stackoverflow.com/a/23007406/389812

[corbanb]

Just stumbled on this thread and when trying to do this in 2.0.5

        var query = {
                where: { 
                    region: region,
                    createdAt: { between: [start, end] }
                },
                include: [
                    { model: abe.db.Vote }
                ],
                attributes: ['Item.*', 'Vote.*', [abe.db.sequelize.fn('COUNT', abe.db.sequelize.col('Vote.id')), 'VoteCount']],

            };

        abe.db.Item.findAll(query)

I get the error:

SequelizeDatabaseError: ERROR:  missing FROM-clause entry for table "Vote"
LINE 1: SELECT "Item"."id", "Item".*, "Vote".*, COUNT("Vote"."id")...

Did something change? @mickhansen or @gdw2

[mickhansen]

@corbanb Can you post the full SQL?

[corbanb]

@mickhansen this is what i got from the logs.

SELECT "Item"."id", "Item".*, "Vote".*, COUNT("Vote"."id") AS "VoteCount", "Votes"."id" AS "Votes.id", "Votes"."type" AS "Votes.type", "Votes"."scope" AS "Votes.scope", "Votes"."region" AS "Votes.region", "Votes"."createdAt" AS "Votes.createdAt", "Votes"."updatedAt" AS "Votes.updatedAt", "Votes"."ItemId" AS "Votes.ItemId", "Votes"."UserId" AS "Votes.UserId", "Votes"."CompetitorId" AS "Votes.CompetitorId" 
FROM "Items" AS "Item" 
LEFT OUTER JOIN "Votes" AS "Votes" ON "Item"."id" = "Votes"."ItemId" 
WHERE "Item"."region" = 'east' 
AND "Item"."createdAt" BETWEEN '2015-05-27T15:30:00+00:00' AND '2015-05-27T18:00:00+00:00';

[corbanb]

@mickhansen did you have any thoughts on this. sorry to bother. I am just not sure how this would not be generating the correct SQL based on the examples.

[mickhansen]

You don't need 'Vote.*' i think, you should use attributes on the include instead if you need to filter.

[corbanb]

Thanks @mickhansen that is an interesting way to achieve this goal.

I have tried this a few ways now such as:

var query = {
                where: { 
                    region: region,
                    createdAt: { between: [start, end] }
                },
                include: [
                    { 
                        model: abe.db.Vote, 
                        attributes: [[abe.db.sequelize.fn('COUNT', abe.db.sequelize.col('Votes.id')), 'VoteCount']]  
                    }
                ]

            };

abe.db.Item.findAll(query)

with the error of:

SequelizeDatabaseError: ERROR:  column "Item.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT "Item"."id", "Item"."region", "Item"."timezone", "...

And by trying:

var query = {
                where: { 
                    region: region,
                    createdAt: { between: [start, end] }
                },
                include: [
                    { model: abe.db.Vote  }
                ],
                attributes: ['Item.*', [abe.db.sequelize.fn('COUNT', abe.db.sequelize.col('Votes.id')), 'VoteCount']],

            };
abe.db.Item.findAll(query)

with the error of:

SequelizeDatabaseError: ERROR:  column "Item.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT "Item"."id", "Item"."id", COUNT("Votes"."id") AS "V...

Is this how you are suggesting this kind of thing be done?

[mickhansen]

Item.* might not work either, but you're doing an aggregate so you have to abide by the PG aggregate rules :)

[corbanb]

@mickhansen , Thanks again. I am very unsure as to what you are referring to here. Is there anymore info you can provide?

[corbanb]

I was able to get a little closer I believe with:

var query = {
                where: { 
                    region: region,
                    createdAt: { between: [start, end] }
                },
                include: [
                    { model: abe.db.Vote }
                ],
                logging: abe.logs.info,
                attributes: [
                                'Item.*',
                                [abe.db.sequelize.fn('COUNT', abe.db.sequelize.col('ItemId')), 'VoteCount']
                            ],
                 group: 'Item.id'
            };
abe.db.Item.findAll(query)

SELECT "Item"."id", "Item".*, COUNT("ItemId") AS "VoteCount", "Votes"."id" AS "Votes.id", "Votes"."type" AS "Votes.type", "Votes"."scope" AS "Votes.scope", "Votes"."region" AS "Votes.region", "Votes"."createdAt" AS "Votes.createdAt", "Votes"."updatedAt" AS "Votes.updatedAt", "Votes"."ItemId" AS "Votes.ItemId", "Votes"."UserId" AS "Votes.UserId", "Votes"."CompetitorId" AS "Votes.CompetitorId" 
FROM "Items" AS "Item" 
LEFT OUTER JOIN "Votes" AS "Votes" ON "Item"."id" = "Votes"."ItemId" 
WHERE "Item"."region" = 'east' 
AND "Item"."createdAt" BETWEEN '2015-06-02T15:30:00+00:00' AND '2015-06-02T18:00:00+00:00' GROUP BY Item.id;

But still can't get around the error of:

SequelizeDatabaseError: ERROR:  missing FROM-clause entry for table "item"
LINE 1: ...00+00:00' AND '2015-06-02T18:00:00+00:00' GROUP BY Item.id;

[mickhansen]

You'll want group: '"Item"."id"' since you have cased tables. You can probably just do group: ['id'] though.

[corbanb]

Awesome @mickhansen . That got things close but its not actually counting the votes. Its just counting the single vote as seen here:

var query = {
                where: { 
                    region: region,
                    createdAt: { between: [start, end] }
                },
                include: [
                    { 
                        model: abe.db.Vote,
                        attributes: [
                                [abe.db.sequelize.fn('COUNT',  abe.db.sequelize.col('Votes.ItemId')), 'VoteCount']
                            ],
                    }
                ],
                group: ['"Item"."id"', '"Votes"."id"', '"Votes"."ItemId"'],
                logging: abe.logs.info
            };
abe.db.Item.findAll(query)

Output:

"Votes": [
   {
      "VoteCount": "1"
   },
   {
      "VoteCount": "1"
   },
   {
      "VoteCount": "1"
   }
]
},

I moved it out to the main attributes also but it still only gets a single vote and shows 3 objects under it. Whic is right I should get 3 votes but the count is not correct.

[chiefGui]

Oh, I feel more confident now that I know Postgres do cares about errors, at least more than MySQL, lol.

Thank you for the explanations, @janmeier—it was very helpful!

[juhaelee]

I'm trying to execute similar code to @chiefGui using the following query:

Comment.findAll({
  where: {deletedAt: null},
  group: ['Comment.id'],
  attributes: [
    'id',
    [
      sequelize.fn('COUNT', sequelize.col('votes.CommentId')), 'votes'
    ]
  ],
  include: [{
    model: Vote,
    attributes: []
  }],
   limit 100,
   raw: true
});

The query I'm getting, however, is:

SELECT "Comment".* FROM (
   SELECT "Comment"."id", COUNT("votes"."CommentId"), "Comment"."count" FROM "Comments" AS "Comment" 
   WHERE ("Comment"."deletedAt" IS NULL) 
   GROUP BY "Comment"."id" 
   ORDER BY "Comment"."createdAt" DESC LIMIT 100
) AS "Comment"
LEFT OUTER JOIN "Votes" AS "Votes" ON "Comment"."id" = "Votes"."CommentId" 
ORDER BY "Comment"."createdAt" DESC;

As you can see, the GROUP BY clause as well as the COUNT are in the inner SELECT when they should be on the outer since include.attributes = []. I'm on sequelize 3.23.2 and postgres 9.4.5.

@mickhansen is there a known bug in sequelize or am I doing something wrong here?

[mickhansen]

@juhaelee You can try adding duplicating: false on the include since it won't generate any rows (we don't currently infer this)

[juhaelee]

awesome it worked! thanks @mickhansen

[AlexNasonov]

Had to create new issue: #6328

[OKNoah]

Is this documented anywhere? Really struggling to adapt people's examples from this thread into my own project.

[felixfbecker]

There are  countAssociations()  methods, see the associations API docs

[OKNoah]

@felixfbecker Sorry, I mean is there documentation for the fn('count') and group, etc.

[felixfbecker]

Sequelize.fn() is documented. Not sure what you mean by group.

[laurenskling]

I would love to see a getAndCountAssociations as there is findAndCountAll
http://docs.sequelizejs.com/en/latest/docs/models-usage/#findandcountall-search-for-multiple-elements-in-the-database-returns-both-data-and-total-count

[felixfbecker]

@laurenskling please open a new issue.

[laurenskling]

@felixfbecker , will do. To me it seemed the original question was the same as mine.

[OKNoah]

@laurenskling Can you link to it?

[OKNoah]

@felixfbecker See the comments above where people are discussing group. I don't understand it either which is why I'd like documentation.

[laurenskling]

@OKNoah #7045

[paffare]

Hi!

I'm having trouble with this counting solution. The code:

models.Comment.findAll({
  where: whereOptions,
  attributes: ['id', 'text', [Sequelize.fn('COUNT',` Sequelize.col('votes.up')), [Sequelize.fn('COUNT', Sequelize.col('votes.down')), 'downVotes']],
}).then(() => {
  console.log(comments);
});

Works just find, but in my case i include some more models not related to the counting, I'm just receiving one object instead of all of them. Like this:

models.Comment.findAll({
  where: whereOptions,
  attributes: ['id', 'text', [Sequelize.fn('COUNT',` Sequelize.col('votes.up'))], [Sequelize.fn('COUNT', Sequelize.col('votes.down')), 'downVotes']],
  include: [Report]
}).then(() => {
  console.log(comments);
});

I'll only get the first Report entry even though there are several associated with the Comment. If I remove the counting attributes, all my Report entries are included.

Does anyone recognize this behavior? I can't see what is causing this.

[juliocanares]

@paffare you need group by comment id add to your findAll query the following group: ['id']

[mytharcher]

After read the whole thread and tried many times, my query as below works:

TagModel.getAll({
  visible: true
}, {
  attributes: {
    include: [
      [fn('COUNT', 'Product.id'), 'number']
    ]
  },
  include: [
    // can't add the where condition below:
    // SequelizeDatabaseError: missing FROM-clause entry for table 'Product';
    // {
    //   association: 'products',
    //   attributes: [],
    //   where: {
    //     enabled: true
    //   }
    // },
    'icon'
  ],
  // when using associated model count,
  // must use group by clause together.
  // when using include for more association,
  // also must use specific model key for
  // grouping.
  group: ['ProductTag.id',/* 'Product.id',*/ 'icon.id']
})

But still not sure how to add deep where conditions for count.

[AlekseyPanch]

Tried to count associations with following code:

  const jobs = await Job.findAll({
    where,
    attributes: ['id', [sequelize.fn('COUNT', sequelize.col('applicants.id')), 'applicantsCount']],
    include: [
      {
        model: Naics,
        as: 'naics',
        attributes: ['description']
      },
      { model: Applicant, as: 'applicants', attributes: [] }
    ],
    group: ['Job.id', 'naics.description', 'applicants.id'],
    row: true
  })

And got the error column "id" must appear in the GROUP BY clause or be used in an aggregate function

And generates the following SQL:

'SELECT "Job"."id", COUNT("applicants"."id") AS "applicantsCount", "naics"."id" AS "naics.id", "naics"."description" AS "naics.description" FROM "Job" AS "Job" LEFT OUTER JOIN "Naics" AS "naics" ON "Job"."naicsId" = "naics"."id" LEFT OUTER JOIN "Applicant" AS "applicants" ON "Job"."id" = "applicants"."jobId" WHERE "Job"."status" = \'active\' GROUP BY "Job"."id", "naics"."description", "applicants"."id";'

Tried to play with group field, but it didn't affect to result at all

[sasajib]

I am new to sequelize, found a solution from
Stackoverflow Post

User.findAll({
    attributes: [
        'User.username',
        [sequelize.literal('(SELECT COUNT(*) FROM Posts WHERE Posts.userId = User.id)'), 'PostCount']
    ],
    order: [[sequelize.literal('PostCount'), 'DESC']]
});

[lpedromachado]

I'd also like to point out that Mick's solution will not do exactly what you expect.

User.findAll({
  attributes: ['User.*', 'Post.*', [sequelize.fn('COUNT', 'Post.id'), 'PostCount']],
  include: [Post]
}

Will be escaped incorrectly to COUNT('Post.id'), instead of COUNT(Post.id) (making it count the User row as well as NULL values of the Post table).

The correct query is:

User.findAll({
  attributes: ['User.*', 'Post.*', [sequelize.fn('COUNT', sequelize.col('Post.id')), 'PostCount']],
  include: [Post]
}

What if I already have a User model instance and want to get the number of its associated posts? What is the best way? Do I have to find the instance again by its primary key in order to aggregate and count its posts?

[harpcio]

I have a lot of problems with PostreSQL vs MySQL compatibility.
This is working for both. Maybe will help someone.

let ordersCountSQL = sequelize.dialect.QueryGenerator.selectQuery('orders', {
    attributes: [
        [sequelize.fn('COUNT', sequelize.col('orders.id')), 'orders_count']
    ],
    where: {
        customer_id: {
            [Op.eq]: sequelize.col('Customer.id')
        }
    }
}).slice(0, -1);

let totalPurchaseSQL = sequelize.dialect.QueryGenerator.selectQuery('orders', {
    attributes: [
        [sequelize.fn('SUM', sequelize.col('orders.total')), 'total_purchase']
    ],
    where: {
        customer_id: {
            [Op.eq]: sequelize.col('Customer.id')
        }
    }
}).slice(0, -1);

const customers = await Customer.findAndCountAll({
    attributes: {
        include: [
            [sequelize.literal('(' + ordersCountSQL + ')'), 'orders_count'],
            [sequelize.literal('(' + totalPurchaseSQL + ')'), 'total_purchase'],
        ],
    },
    include: [
        {
            association: 'orders',
            model: Order,
            attributes: [],
        },
    ],
    subQuery: false,
    group: ['Customer.id']
});

[mfauzaan]

Somehow many of the options does not work for me

Here is my solution which works on sequelize v5.18.4 (postgres)

const item = await Item.findAll({
      attributes: ['id','title', [Item.literal('(SELECT COUNT(*) FROM item_requests WHERE item_requests.item_id = "Item"."id")'), 'requestsCount']],
        order: [['id', 'ASC']]
      })

Note: issue caused by string quote.

[manoellribeiro]

Somehow many of the options does not work for me

Here is my solution which works on sequelize v5.18.4 (postgres)

const item = await Item.findAll({
      attributes: ['id','title', [Item.literal('(SELECT COUNT(*) FROM item_requests WHERE item_requests.item_id = "Item"."id")'), 'requestsCount']],
        order: [['id', 'ASC']]
      })

Note: issue caused by string quote

None of the solutions related here work for me, but this one helped me and I achieved what I wanted. Thank You @mfauzaan

[rahathossain690]

attributes: [
'User.username',
[sequelize.literal('(SELECT COUNT(*) FROM Posts WHERE Posts.userId = User.id)'), 'PostCount']
],

For me this worked instead, just a little tweak

attributes: [
'User.username',
[sequelize.literal('(SELECT COUNT(*) FROM "Posts" WHERE "Posts"."userId" = User.id)'), 'PostCount']
]