CODEWITHSUNDEEP
node.jssequelize.js
Talal Najam
Talal Najam

Reputation: 230

Sequelize - how to filter by association (belongsToMany)?

I have a model lets say Post and Country. I want to filter and get Posts where country id is e.g. 1 or where country ids must be 1 and 2 in same post, how can I achieve this using Sequelize? The way I am trying below is giving me something not what I'm expecting

Many Thanks

Post.js

class Post extends Sequelize.Model {}

Post.init(
    {
        id: {
            type: Sequelize.INTEGER,
            primaryKey: true,
        },
        text: {
            type: Sequelize.STRING,
        }
    }
);

module.exports = Post;

Country.js

const Sequelize = require('sequelize');
const sequelize = require('../config/db');

class Country extends Sequelize.Model {}
Country.init({
    id: {
        type: Sequelize.INTEGER,
        autoIncrement: true,
        primaryKey: true
    },
    name: {
        type: Sequelize.STRING,
        unique: true
    }
}, {sequelize, modelName: 'country'});

module.exports = Country;

App.js

Post.belongsToMany(Country, {through: "PostCountry", foreignKey: 'id'});

what i'm trying

 Post.findAndCountAll({
        order: [[Country, "name", "asc"]],
        include: [
            {
                model: Country,
                through: { where: { countryId: [2] } }
            }
        ],
        distinct: true,
        offset: offset,
        limit: limit
    })

sample output

{
    "page": 1,
    "totalPages": 1,
    "totalResults": 1,
    "results": [
        {
            "id": 123,
            "text": "hello, world",
            "countries": [
                {
                    "id": 2,
                    "name": "Australia",
                },
                {
                    "id": 1,
                    "name": "New Zealand",
                },
                {
                    "id": 3,
                    "name": "Singapore",
                }
            ]
        }
    ]
}

Upvotes: 1

Views: 2968

Answers (1)

KenOn10
KenOn10

Reputation: 1968

For one thing, it looks like you've mixed up through (which shouldn't be necessary in this query) and where (which is necessary). Try this:

Post.findAndCountAll({
        order: [[Country, "name", "asc"]],
        include: [
            {
                model: Country,
                where: { countryId : {[Op.in]: [2]}}
            }
        ],
        distinct: true,
        offset: offset,
        limit: limit
    })

HTH

Addendum ----

To show ALL the countries, you'll need TWO joins - once to identify Posts with the some particular country, then another to identify all of their associated countries... as mentioned in this post. FWIW, I have not tried this with findAndCountAll, but it should work...

Upvotes: 2

Related Questions