javascript - Counting relationship in sequelize returnings 1 instead of 0

Question

I have the following test case for sequelize where I am attempting to return the count of a relationship on a model.

test('Tag with file count', async () => {
      const tagOne = await db.Tag.create({
        label: 'One'
      })

      const tagTwo = await db.Tag.create({
        label: 'Two'
      })

      const tagThree = await db.Tag.create({
        label: 'Three'
      })

      const tagFour = await db.Tag.create({
        label: 'Four'
      })

      const tagFive = await db.Tag.create({
        label: 'Five'
      })

      const fileOne = await db.File.create()

      const fileTwo = await db.File.create()

      await fileOne.setTags([tagOne, tagTwo, tagFour])

      await fileTwo.setTags([tagOne, tagTwo, tagThree])

      const output = await db.Tag.findAll({
        attributes: {
          include: [
            [Sequelize.fn('COUNT', 'Files.id'), 'fileCount']
          ]
        },
        include: [
          {
            model: db.File,
            as: 'files',
            attributes: [],
            duplicate: false
          }
        ],
        group: 'Tag.id',
        order: [
          [Sequelize.literal('`fileCount`'), 'DESC']
        ]
      })

      expect(output.length).toBe(5)
      expect(output.find(tag => tag.label === tagOne.label).dataValues.fileCount).toBe(2)
      expect(output.find(tag => tag.label === tagTwo.label).dataValues.fileCount).toBe(2)
      expect(output.find(tag => tag.label === tagThree.label).dataValues.fileCount).toBe(1)
      expect(output.find(tag => tag.label === tagFour.label).dataValues.fileCount).toBe(1)
      expect(output.find(tag => tag.label === tagFive.label).dataValues.fileCount).toBe(0)
    })

For tagFive, I expected the column fileCount to be 0 but it returns 1. For others it seems to return the correct file count.

Is there an option missing in findAll or a bug?

Edit

I get the following query generated

Executing (default): SELECT `Tag`.`id`, 
`Tag`.`label`, 
`Tag`.`slug`, 
`Tag`.`createdAt`, 
`Tag`.`updatedAt`, COUNT('Files.id') AS `fileCount`, 
`files->FileTags`.`createdAt` AS `files.FileTags.createdAt`, 
`files->FileTags`.`updatedAt` AS `files.FileTags.updatedAt`, 
`files->FileTags`.`FileId` AS `files.FileTags.FileId`, 
`files->FileTags`.`TagId` AS `files.FileTags.TagId` 
FROM `Tags` AS `Tag` 
LEFT OUTER JOIN `FileTags` AS `files->FileTags` 
ON `Tag`.`id` = `files->FileTags`.`TagId` 
LEFT OUTER JOIN `Files` AS `files` 
ON `files`.`id` = `files->FileTags`.`FileId` 
GROUP BY `Tag`.`id` ORDER BY `fileCount` DESC;

Though there's so many alias and I am not comfortable with joins.

标签： javascriptsequelize.js

Answer 1

i made a few changes into something that would work in my database with a similar structure... This did return me some 0 count rows, however this is postgres i am running it on, and you are clearly using something different

SELECT tag.id, 
COUNT(story.id) AS fileCount
FROM tag 
LEFT OUTER JOIN story_tag
ON tag.name = story_tag.tag 
LEFT OUTER JOIN story 
ON story.id = story_tag.story_id 
GROUP BY tag.id ORDER BY fileCount DESC;