I have the following JS code:

db.models
    .Vobject
    .findAndCountAll({
    where: [{type: 1 }],
    include: [{model: db.models.Tag, where: {name: ['iron', 'tefal']}}],
    limit: 10
})

The problem is that sequelize generates the following query:

select object where type = 2 limit 10 after find tags iron or tefal for this object

For example:

SELECT attributes
FROM   (SELECT `vobject`.*
        FROM   `vobject`
        WHERE  ( `vobject`.`type` = 1 )
        LIMIT  10) AS `vobject`
       INNER JOIN `vobject_tag_to_vobject` AS
                  `vobject_tags.vobject_tag_to_vobject`
               ON `vobject`.`uuid` =
       `vobject_tags.vobject_tag_to_vobject`.`vobject_uuid`
       INNER JOIN `vobject_tag` AS `vobject_tags`
               ON `vobject_tags`.`id` =
`vobject_tags.vobject_tag_to_vobject`.`tag_id`
                  AND `vobject_tags`.`name` IN ( 'iron', 'tefal' );

But I need the generated SQL query to look like this:

select object where type = 2 and tags in (iron,tefal) limit 10

For example:

SELECT attributes
FROM   `vobject`
       INNER JOIN `vobject_tag_to_vobject` AS
                  `vobject_tags.vobject_tag_to_vobject`
               ON `vobject`.`uuid` =
       `vobject_tags.vobject_tag_to_vobject`.`vobject_uuid`
       INNER JOIN `vobject_tag` AS `vobject_tags`
               ON `vobject_tags`.`id` =
`vobject_tags.vobject_tag_to_vobject`.`tag_id`
                  AND `vobject_tags`.`name` IN ( 'iron', 'tefal' )
WHERE  ( `vobject`.`type` = 1 )
LIMIT  10;

This happens because of the subquery requirement check in QueryGenerator.selectQuery

, subQuery = limit && (options.hasIncludeWhere || options.hasIncludeRequired || options.hasMultiAssociation )

After, I created the option.filterByInclude and added it to the condition, everything started to work fine :)

, subQuery = limit && !option.filterByInclude (options.hasIncludeWhere || options.hasIncludeRequired || options.hasMultiAssociation )

Now it returns objects with either 'iron' or 'tefal' tag.

The next step is to find object with both 'iron' and 'tefal' tags. I modified my query as follows:

• added group by object uuid clause
• added having where objects >= 2 clause

db.models
    .Vobject
    .findAndCountAll({
    where: [{type: 1 }],
        include: [{model: db.models.Tag, where: {name: ['iron', 'tefal']}}],
        limit: 10,
        filterByInclude: true,
        group: [{raw: '`vobject_tags.vobject_tag_to_vobject`.`vobject_uuid`'}],
        group: ['`vobject`.`uuid`'],
        having: ['COUNT(?) >= ?', '`vobject`.`uuid`', 2]
})

SELECT attributes...
FROM   `vobject`
       INNER JOIN `vobject_tag_to_vobject` AS
                  `vobject_tags.vobject_tag_to_vobject`
               ON `vobject`.`uuid` =
       `vobject_tags.vobject_tag_to_vobject`.`vobject_uuid`
       INNER JOIN `vobject_tag` AS `vobject_tags`
               ON `vobject_tags`.`id` =
`vobject_tags.vobject_tag_to_vobject`.`tag_id`
                  AND `vobject_tags`.`name` IN ( 'iron', 'tefal' )
WHERE  ( `vobject`.`type` = 1 )
GROUP  BY `vobject`.`uuid`
HAVING Count('`vobject`.`uuid`') >= 2
LIMIT  10;

Hurray, it works! But the count is broken now, it has a value of '2'. This query returns 35 rows (the previous query returns the same number of rows) and each row has count equal to '2'.

This happens because of the GROUP BY clause:

SELECT Count(`vobject`.`uuid`) AS `count`
FROM   `vobject`
       INNER JOIN `vobject_tag_to_vobject` AS
                  `vobject_tags.vobject_tag_to_vobject`
               ON `vobject`.`uuid` =
       `vobject_tags.vobject_tag_to_vobject`.`vobject_uuid`
       INNER JOIN `vobject_tag` AS `vobject_tags`
               ON `vobject_tags`.`id` =
`vobject_tags.vobject_tag_to_vobject`.`tag_id`
                  AND `vobject_tags`.`name` IN ( 'iron', 'tefal' )
WHERE  ( `vobject`.`type` = 1 )
GROUP  BY `vobject`.`uuid`
HAVING Count('`vobject`.`uuid`') >= 2;

To make this work we need to move the main query into subquery:

SELECT COUNT(*) as count FROM (
    SELECT `vobject`.`uuid` AS `count`
    FROM   `vobject`
           INNER JOIN `vobject_tag_to_vobject` AS
                      `vobject_tags.vobject_tag_to_vobject`
                   ON `vobject`.`uuid` =
           `vobject_tags.vobject_tag_to_vobject`.`vobject_uuid`
           INNER JOIN `vobject_tag` AS `vobject_tags`
                   ON `vobject_tags`.`id` =
    `vobject_tags.vobject_tag_to_vobject`.`tag_id`
                      AND `vobject_tags`.`name` IN ( 'iron', 'tefal' )
    WHERE  ( `vobject`.`type` = 1 )
    GROUP  BY `vobject`.`uuid`
    HAVING Count('`vobject`.`uuid`') >= 2
) as tmp;

Modify DAOFactory.prototype.count

options.attributes = [
    [this.sequelize.fn('COUNT', col), 'count']
]
// change to 
options.count = true;
options.attributes = [col]

Modify QueryGenerator.selectQuery

// add before the end of query
if(options.count) {
    query = 'SELECT COUNT(*) as count FROM (' + query + ') as tmp';
} 

query += ";";

Now everything works as it should :)