Блог

У меня есть 3 таблицы с именами:

 //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))
 

Я хотел бы отфильтровать restaurant_branches по restaurant_type_id, используя параметры запроса в моем restaurant_branches.findAll(); действие в контроллере, как показано ниже.

 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;
 

до сих пор я делаю это, и я получаю все филиалы ресторана, если получу запрос на этот URL:

{{URL}}/restaurant_branches?restaurantType=2

вместо этого я хотел бы получить все филиалы ресторанов, чьи рестораны относятся к типу ресторанов с идентификатором 2

Мы высоко ценим любую помощь или рекомендации.

Вы включили where условие в include опцию в качестве type опоры, поэтому оно работает не так, как ожидалось. Вам просто нужно указать where либо свое условие, либо пустой объект:

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

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