在 sequelize 中查询 where 关联?
where 子句用于内连接的 sequelize.我的查询是
where clause use in sequelize in inner joins.
My query is
SELECT Cou.country_id,cou.country_name, Sta.state_id, Sta.state_name
FROM hp_country Cou
INNER JOIN hp_state Sta ON Cou.country_id = Sta.hp_country_id
WHERE (Cou.country_status=1 AND Sta.state_status=1 AND Cou.country_id=1)
AND (Sta.state_name LIKE '%ta%');
我在sequelize中写的代码是
I wrote in sequelize code is
hp_country.findAll({
where: {
'$hp_state.state_status$': 1
},
include: [
{model: hp_state}
]
})
它产生的错误是:
SELECT `hp_country`.`country_id`, `hp_country`.`country_name`, `hp_country`.`country_status`, `hp_country`.`created_date`, `hp_country`.`update_date` FROM `hp_country` AS `hp_country` WHERE `hp_state`.`state_status` = 1;
Unhandled rejection SequelizeDatabaseError: ER_BAD_FIELD_ERROR: Unknown column 'hp_state.state_status' in 'where clause'
推荐答案
hp_country.findAll({
where: {
//main AND condition
$and: [
//first joint condition
{
$and: [
{ country_status: 1 },
{ country_id: country_id },
Sequelize.literal("hp_states.state_status = 1"),
Sequelize.literal("`hp_states.hp_districts`.`district_status`=1"),
Sequelize.literal("`hp_states.hp_districts.hp_cities`.`city_status`=1"),
Sequelize.literal("`hp_states.hp_districts.hp_cities.hp_locations`.`location_status`=1"),
Sequelize.literal("`hp_states.hp_districts.hp_cities.hp_locations.hp_sub_locations`.`sub_location_status`=1"),
Sequelize.literal("`hp_states.hp_districts.hp_cities`.`city_name` LIKE '%"+city+"%'")
]
},
{
$or: [
Sequelize.literal("`hp_states.hp_districts.hp_cities.hp_locations`.`location_name` LIKE '%"+query+"%'"),
Sequelize.literal("`hp_states.hp_districts.hp_cities.hp_locations.hp_sub_locations`.`sub_location_name` LIKE '%"+query+"%'"),
Sequelize.literal("`hp_states.hp_districts.hp_cities.hp_locations.hp_sub_locations.hp_property`.`property_name` LIKE '%"+query+"%'"),
Sequelize.literal("`hp_states.hp_districts.hp_cities.hp_locations.hp_sub_locations.hp_property.hp_builder`.`builders_name` LIKE '%"+query+"%'")
]
}
]
},
attributes: ['country_id', 'country_name'],
required:true,
include: [
{
model: hp_state,
attributes: ['state_id', 'state_name'],
required:true,
include: [
{
model: hp_district,
attributes: ['district_id', 'district_name'],
required:true,
include: [
{
model: hp_city,
attributes: ['city_id', 'city_name'],
required:true,
include: [
{
model: hp_location,
attributes: ['location_id', 'location_name'],
required:true,
include: [
{
model: hp_sub_location,
attributes: ['sub_location_id', 'sub_location_name'],
required:true,
include: [
{
model: hp_property,
attributes: ['property_id', 'property_name'],
required: true,
include: [
{
model:hp_builders,
attributes: ['builders_id', 'builders_name'],
required: true
}
]
}
]
}]
}]
}]
}
]
}
]
})
相关文章