How to filter through 3 table joins using sequelize in expressjs api

Question

I have 3 tables named:

//restaurants
columns ( id, name, restaurant_type_id(FK)
//restaurant_branches 
columns ( id, name, restaurant_id(FK)
//restaurant_types
columns ( id, restaurant_type_name('italian', 'french'...etc))

I would like to filter restaurant_branches by restaurant_type_id using query params in my restaurant_branches.findAll(); action in the controller as the following.

const findAll = async (req, res) => {
    let RestaurantTypeId= req.query.restaurantType ? parseInt(req.query.restaurantType): null ;
    var type = RestaurantTypeId ? {where:{ restaurantTypeId: RestaurantTypeId }} : null ;
    console.log(RestaurantTypeId); 
    
    await RestaurantBranch.findAll({
        order: [
            ['id', 'ASC']
        ],

        include: 
        [
             {
                  model: Restaurant, 
                  type,
                  include: [{
                     model: RestaurantType,
                 }
                 ]
             }
                
            ]
    }).then((restaurantBranches) => {
        return res.status(200).send({
            message: "restaurant branches returned", 
            data: restaurantBranches
        })
    })
    .catch((error) => {res.status(500).send(error.message);});

}
module.exports = {
    findAll
}

//Sequelize Associations
db.RestaurantType.hasMany(db.Restaurant);
db.Restaurant.belongsTo(db.RestaurantType);
// Restaurant / Restaurant Branches
db.RestaurantBranch.belongsTo(db.Restaurant);
db.Restaurant.hasMany(db.RestaurantBranch);

Sequelize log: 
Executing (default): SELECT "restaurant_branches"."id", "restaurant_branches"."name", "restaurant_branches"."description", "restaurant_branches"."email", "restaurant_branches"."phoneNumber", "restaurant_branches"."address", "restaurant_branches"."country_code", "restaurant_branches"."image", "restaurant_branches"."latitude", "restaurant_branches"."longitude", "restaurant_branches"."workingHours", "restaurant_branches"."workingDays", "restaurant_branches"."offDays", "restaurant_branches"."locationAddress", "restaurant_branches"."locationCity", "restaurant_branches"."status", "restaurant_branches"."hasParking", "restaurant_branches"."instruction", "restaurant_branches"."isActive", "restaurant_branches"."isDeleted", "restaurant_branches"."createdAt", "restaurant_branches"."updatedAt", "restaurant_branches"."restaurantId", "restaurant_branches"."cityId", "restaurant_branches"."districtId", "city"."id" AS "city.id", "city"."name" AS "city.name", "city"."code" AS "city.code", "city"."status" AS "city.status", "city"."isDeleted" AS "city.isDeleted", "city"."createdAt" AS "city.createdAt", "city"."updatedAt" AS "city.updatedAt", "city"."countryId" AS "city.countryId", "district"."id" AS "district.id", "district"."name" AS "district.name", "district"."isDeleted" AS "district.isDeleted", "district"."createdAt" AS "district.createdAt", "district"."updatedAt" AS "district.updatedAt", "district"."cityId" AS "district.cityId", "restaurant"."id" AS "restaurant.id", "restaurant"."name" AS "restaurant.name", "restaurant"."aboutUs" AS "restaurant.aboutUs", "restaurant"."phoneNumber" AS "restaurant.phoneNumber", "restaurant"."address" AS "restaurant.address", "restaurant"."latitude" AS "restaurant.latitude", "restaurant"."longitude" AS "restaurant.longitude", "restaurant"."image" AS "restaurant.image", "restaurant"."countryCode" AS "restaurant.countryCode", "restaurant"."restaurantRegisterDocument" AS "restaurant.restaurantRegisterDocument", "restaurant"."isDeleted" AS "restaurant.isDeleted", "restaurant"."createdAt" AS "restaurant.createdAt", "restaurant"."updatedAt" AS "restaurant.updatedAt", "restaurant"."restaurantTypeId" AS "restaurant.restaurantTypeId", "restaurant"."categoryId" AS "restaurant.categoryId", "restaurant"."userId" AS "restaurant.userId", "restaurant->restaurant_type"."id" AS "restaurant.restaurant_type.id", "restaurant->restaurant_type"."name" AS "restaurant.restaurant_type.name", "restaurant->restaurant_type"."photo" AS "restaurant.restaurant_type.photo", "restaurant->restaurant_type"."createdAt" AS "restaurant.restaurant_type.createdAt", "restaurant->restaurant_type"."updatedAt" AS "restaurant.restaurant_type.updatedAt" FROM "restaurant_branches" AS "restaurant_branches" LEFT OUTER JOIN "cities" AS "city" ON "restaurant_branches"."cityId" = "city"."id" LEFT OUTER JOIN "districts" AS "district" ON "restaurant_branches"."districtId" = "district"."id" LEFT OUTER JOIN "restaurants" AS "restaurant" ON "restaurant_branches"."restaurantId" = "restaurant"."id" LEFT OUTER JOIN "restaurant_types" AS "restaurant->restaurant_type" ON "restaurant"."restaurantTypeId" = "restaurant->restaurant_type"."id" ORDER BY "restaurant_branches"."id" ASC;

so far I'm doing this, and I get All the restaurant branches if I GET request to this URL:

{{URL}}/restaurant_branches?restaurantType=2

what I'd like to be getting instead is all the restaurant branches whom their restaurants belong to the restaurant type with id 2

Any help or guidance is highly appreciated.

Answer 1

You included where condition in include option as a type prop that's why it does not work as expected. You just need to indicate where either with your condition or as an empty object:

var where = RestaurantTypeId ? { restaurantTypeId: RestaurantTypeId } : {} ;
    

include: 
        [
             {
                  model: Restaurant, 
                  where,
                  include: [{
                     model: RestaurantType,
                 }
                 ]
             }
                
            ]