如何在expressjs api中使用sequelize筛选3个表连接

Question

我有三个表命名为:

//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. findall()中使用查询参数通过restaurant_type_id过滤restaurant_branches;

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

我想要得到的是所有餐厅所属的餐厅分支都属于id为2的餐厅类型

任何帮助或指导都是非常感谢的。

Answer 1

您在include选项中包含where条件作为typeprop，这就是为什么它不像预期的那样工作。您只需要将where指示为您的条件或作为空对象:

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

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

]