Overlap query on N:M association with Sequelize using MySQL dialect?

[Thomas Stjernegaard Jeppesen]

Given the following Sequelize models:

models.Post.belongsToMany(models.Tag, {
    through: models.PostTag,
    foreignKey: 'post_id',
    as: 'tags'
});

models.Tag.belongsToMany(models.Post, {
    through: models.PostTag,
    foreignKey: 'tag_id'
});

I am able to query for all posts tagged with the tag having tag_id = 2 like this:

models.Post.findAll({
    include: [{
        model: models.Tag,
        as: 'tags',
        where: {
            tag_id: 2
        }
    }]
})

Now, I would like to query for all posts that have been tagged with all of the following tags (tag_id): 2, 5, 17, 87 (or any other set of tags). I am using MySQL as DB dialect.

In SQL this can be accomplished with a series of inner joins, but is it possible with Sequelize?

[Mick Hansen]

Not currently possible natively in Sequelize.
How would you express it with SQL? Maybe we can work it into your query while still retaining most of the Sequelize code.

--

Mick Hansen

@mhansendev
mhansen.io

[Thomas Stjernegaard Jeppesen]

I would build a sequence of inner joins like this (MySQL dialect) :

select p.* from (Posts p INNER JOIN PostTags pt ON p._id=pt.post_id INNER JOIN Tags t ON pt.tag_id=t._id AND t._id=1)
INNER JOIN (Posts p1 INNER JOIN PostTags pt1 ON p1._id=pt1.post_id INNER JOIN Tags t1 ON pt1.tag_id=t1._id AND t1._id=3)  ON p._id = p1._id
INNER JOIN (Posts p2 INNER JOIN PostTags pt2 ON p2._id=pt2.post_id INNER JOIN Tags t2 ON pt2.tag_id=t2._id AND t2._id=6)  ON p._id = p2._id;

One join pr tag, i.e. the above example gives all posts that have been tagged with tags 1, 3, 6.

Working this into Sequelize would give you subsets based on multiple predicates from a N:M association.  Would be a real killer feature in your already very powerful framework!

[Mick Hansen]

I definitely see the merit of this feature.
Unsure how to design it generically so and actually making it work in all the cases we generally handle.

[Thomas Stjernegaard Jeppesen]

It would basically be a matter of duplicating an association "on the fly" of possible. I can do a primitive hack if I create several identical associations, but give them a unique name:

for (var i = 0; i < 10; i++) {

models.Post.belongsToMany(models.Tag, {

through: models.PostTag,

foreignKey: 'post_id',

as: 'tags' + i

});

models.Tag.belongsToMany(models.Post, {

through: models.PostTag,

foreignKey: 'tag_id',

as: 'tags' + i

});

}

Then I can do:

models.Post.findAll({

include: [{

model: models.Tag,

as: 'tags0',

where: {

_id: 2

}

}, {

model: models.Tag,

as: 'tags1',

where: {

_id: 5

}

}, {

model: models.Tag,

as: 'tags2',

where: {

_id: 17

}

}]

})

This actually build the join sequence and allows to query for a set of up to 10 tags (fixed number set in the loop). If I try to include the same association multiple times (using 'tags' instead of 'tags0', 'tags1', 'tags2') I get an "ER_NONUNIQ_TABLE" error. Maybe Sequelize could duplicate the association and perform this query rather than throwing the error? I have not read much of the Sequelize code base so this might just be a shot in dark ....

[Mick Hansen]

The technical solution is probably not too tricky.

First we need to design a query API though.