title: Using Sequelize to count the number of rows (where id = 'x') in an associated model
body: <p>I'm trying to build a message board to teach myself JavaScript. I'm working with:</p>
  
  <ul>
  <li>nodejs</li>
  <li>angularjs</li>
  <li>express</li>
  <li>mysql</li>
  </ul>
  
  <p>I have a view <code>/board</code> that uses the following route (and corresponding sqlize query) to fetch data for displaying the "board index":</p>
  
  <pre><code>router.get('/api/board/fetch', function(req, res) {
      if(req.session.key) {
        dbconn.threads.findAll({ 
          order: 'id DESC',
          include: [{
            model: dbconn.users,
            attributes: ['id', 'username', 'first_name']
          }]
        }).then(function(threads) {
          return res.send({ success: true, threads: threads }); // return all threads found in table
  
        });
      } else {
        return res.send({ success: false, msg: 'Error: Posts' })
      }
  
    });
  </code></pre>
  
  <p>The above example simply fetched the threads with some associated model data to display a summary index. Now, in order to also show the current "post count" within a specific thread, I modified the sqlize to:</p>
  
  <pre><code>router.get('/api/board/fetch', function(req, res) {
      if(req.session.key) {
        dbconn.threads.findAll({ 
          order: 'threads.id DESC',
          group: ['threads.id', 'replies.id'],
          raw: true,
          include: [{
            model: dbconn.users,
            attributes: ['id', 'username', 'first_name'],
          }, {
            model: dbconn.replies,
            attributes: [[sequelize.fn('COUNT', 'id'), 'replyCount']],
          }]
        }).then(function(threads) {
          return res.send({ success: true, threads: threads }); // return all threads found in table
  
        });
      } else {
        return res.send({ success: false, msg: 'Error: Posts' })
      }
  
    });
  </code></pre>
  
  <p>I pieced this together using <a href="https://stackoverflow.com/questions/37817808/counting-associated-entries-with-sequelize">this SO question</a> and <a href="https://github.com/sequelize/sequelize/issues/222" rel="nofollow noreferrer">this GH issue</a>, but rather than having output of:</p>
  
  <blockquote>
    <p>Thread Title  |  # Replies</p>
  </blockquote>
  
  <p>It lists the same data 5 times with a "# Replies" of 0 for all of them. What I believe is happening:</p>
  
  <ul>
  <li>I am not accurately counting all <code>replies.id</code> where <code>threadId</code> = 'x'</li>
  <li>I have not correctly implemented the sequelize.fn("COUNT") ... syntax</li>
  </ul>
  
  <p>For better context (hopefully), I have the following in my controller:</p>
  
  <pre><code>board.fetch().then(function(data) {
        $scope.thread = data
        console.log("data obj -&gt; ", data)
      })
  </code></pre>
  
  <p>Which uses the following from my service.js:</p>
  
  <pre><code>fetch : function() {
        return $http.get('/api/board/fetch').then(function(response) {
          return response.data
        })
      },
  </code></pre>
  
  <p>In the browser, the object understandably has five objects in a <code>threads</code> array, all containing the same data (makes sense from the sqlize query) and <code>replies.replyCount: 1</code>.</p>
  
  <p>I apologize for the terrible code, naming conventions, and if this is answered elsewhere. I've spent a while today trying to figure this out but have been limited in my lack of understanding to search for the right question.</p>
  
  <p>I can provide the model definitions if those are necessary; the post just felt very long already.</p>
  
  <p><strong>Update:</strong>
  Modified code per <a href="https://stackoverflow.com/users/3324509/shivam">Shivam</a>'s observation and it now works as I need it to. Updated code below:</p>
  
  <pre><code>dbconn.threads.findAll({ 
          order: 'threads.id DESC',
          raw: true,
          attributes: {
            include: ['title', 'body', 'slug', 'createdAt', 'userId', [sequelize.fn('COUNT', 'replies.id'), 'replyCount']]
          }, 
          include: [{
            model: dbconn.users,
          }, {
            model: dbconn.replies, attributes: [],
          }],
          group: ['threads.id', 'replies.threadId'],
        }).then(function(threads) {
          return res.send({ success: true, threads: threads }); // return all threads found in table
  
        });
      } else {
        return res.send({ success: false, msg: 'Error: Posts' })
      }
  </code></pre>