Get instances where count of relationship is zero with Sequelize

user_2931914 0
Viewed 1896

Below is the syntax to count the relationship on a model via Sequelize

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

What do I require to make this code return only Files that has zero tags associated to them?

Edit

As per answer provided by @Soham, it does achieve the intended result but cannot be paginated. Generated query is:

SELECT `File`.`id`,
`File`.`originalId`,
`File`.`signature`,
`File`.`referrer_url`,
`File`.`preview_url`,
`File`.`preview_extension`,
`File`.`original_url`,
`File`.`original_extension`,
`File`.`provider`,
`File`.`previewedAt`,
`File`.`viewedAt`,
`File`.`blacklistedAt`,
`File`.`queuedAt`,
`File`.`startedAt`,
`File`.`downloadedAt`,
`File`.`createdAt`,
`File`.`updatedAt`,
COUNT(`tags`.`id`) AS `tagCount`,
`tags->FileTags`.`createdAt` AS `tags.FileTags.createdAt`,
`tags->FileTags`.`updatedAt` AS `tags.FileTags.updatedAt`,
`tags->FileTags`.`fileId` AS `tags.FileTags.fileId`,
`tags->FileTags`.`tagId` AS `tags.FileTags.tagId` 
FROM `Files` AS `File` 
LEFT OUTER JOIN `FileTags` AS `tags->FileTags` 
ON `File`.`id` = `tags->FileTags`.`fileId` 
LEFT OUTER JOIN `Tags` AS `tags` 
ON `tags`.`id` = `tags->FileTags`.`tagId` 
GROUP BY `File`.`id` 
HAVING `tagCount` = 0 
ORDER BY `tagCount` DESC;

When I add offset and limit to the query as per below

    return super.findAll({
      offset: 0,
      limit: 24,
      attributes: {
        include: [
          [Sequelize.literal('COUNT(`tags`.`id`)'), 'tagCount']
        ]
      },
      include: [
        {
          model: db.Tag,
          as: 'tags',
          attributes: [],
          duplicate: false
        }
      ],
      group: 'File.id',
      order: [
        [Sequelize.literal('`tagCount`'), 'DESC']
      ],
      having: { tagCount: 0 }
    })

and this outputs the following

SELECT `File`.*,
 `tags->FileTags`.`createdAt` AS `tags.FileTags.createdAt`,
 `tags->FileTags`.`updatedAt` AS `tags.FileTags.updatedAt`,
 `tags->FileTags`.`fileId` AS `tags.FileTags.fileId`,
 `tags->FileTags`.`tagId` AS `tags.FileTags.tagId` 
 FROM (SELECT `File`.`id`,
 `File`.`originalId`,
 `File`.`signature`,
 `File`.`referrer_url`,
 `File`.`preview_url`,
 `File`.`preview_extension`,
 `File`.`original_url`,
 `File`.`original_extension`,
 `File`.`provider`,
 `File`.`previewedAt`,
 `File`.`viewedAt`,
 `File`.`blacklistedAt`,
 `File`.`queuedAt`,
 `File`.`startedAt`,
 `File`.`downloadedAt`,
 `File`.`createdAt`,
 `File`.`updatedAt`,
 COUNT(`tags`.`id`) AS `tagCount` 
 FROM `Files` AS `File` 
 GROUP BY `File`.`id` 
 HAVING `tagCount` = 0 
 ORDER BY `tagCount` DESC LIMIT 0,
 24) AS `File` 
 LEFT OUTER JOIN `FileTags` AS `tags->FileTags` 
 ON `File`.`id` = `tags->FileTags`.`fileId` 
 LEFT OUTER JOIN `Tags` AS `tags` 
 ON `tags`.`id` = `tags->FileTags`.`tagId` 
 ORDER BY `tagCount` DESC;
sequelize.js javascript
2 Answers

To filter records having tag count zero you need to use having clause as follows - 

const files = await db.File.findAll({
  attributes: {
    include: [
      [Sequelize.fn('COUNT', 'Tags.id'), 'tagCount']
    ]
  },
  include: [
    {
      model: db.Tag,
      as: 'tags',
      attributes: [],
      duplicate: false
    }
  ],
  group: 'File.id',
  order: [
    [Sequelize.literal('`tagCount`'), 'DESC']
  ],
  having: { tagCount : 0 },
  subQuery: false
})
user_40688170

i cant add a comment on an answer, so please see my answer as a comment on the upvoted one.

Sequelize.fn('COUNT', 'Tags.id')

should be

Sequelize.fn('COUNT', Sequelize.col('Tags.id'))

if 'Tags.id' is not wrapped with Sequelize.col() the generated sql looks like: COUNT('Tags.id').

working example:

const files = await db.File.findAll({
    attributes: {
    include: [
      [Sequelize.fn('COUNT', Sequelize.col('Tags.id')), 'tagCount']
    ]
  },
  include: [
    {
      model: db.Tag,
      as: 'tags',
      attributes: [],
      duplicate: false
    }
  ],
  group: 'File.id',
  order: [
    [Sequelize.literal('`tagCount`'), 'DESC']
  ],
  having: { tagCount : 0 },
  subQuery: false
})
user_183524640
Related