Simha Chalam Simha Chalam - 3 months ago 23
MySQL Question

Combined WHERE clause for all INCLUDEs

i want to perform this query in sequelize models:

SELECT Cou.country_id,cou.country_name, Sta.state_id, Sta.state_name, Dis.district_id,
Dis.district_name,Cit.city_id, Cit.city_name,Loc.location_id,Loc.location_name,Sub_Loc.sub_location_id,Sub_Loc.sub_location_name,Prop.property_id,Prop.property_name,Prop.hp_builders_id,
Bud.builders_id,Bud.builders_name
FROM hp_country Cou
INNER JOIN hp_state Sta ON Cou.country_id = Sta.hp_country_id
INNER JOIN hp_district Dis ON Sta.state_id = Dis.hp_state_id
INNER JOIN hp_city Cit ON Dis.district_id = Cit.hp_district_id
INNER JOIN hp_location Loc ON Cit.city_id = Loc.hp_city_id
INNER JOIN hp_sub_location Sub_Loc ON Loc.location_id = Sub_Loc.hp_location_id
INNER JOIN hp_property Prop ON Sub_Loc.sub_location_id=Prop.hp_sub_location_id
LEFT JOIN hp_builders Bud ON Prop.hp_builders_id=Bud.builders_id
where (Cou.country_status=1 AND Sta.state_status=1 AND Cou.country_id=1)
AND (Dis.district_name LIKE '%ky%' OR Cit.city_name LIKE '%ky%' OR Loc.location_name LIKE '%ky%' OR Sub_Loc.sub_location_name LIKE '%ky%' OR Prop.property_name LIKE '%ky%')


I tried to write in this manner but i did not achieved my target query :
Included more nested models because of all are inner joins and i tried with required is true in include box.how to do [OR] condition for columns which is in different tables

hp_country.findAll({
attributes: ['country_id', 'country_name'],
where: {
country_status: 1,
country_id: 1
},
include: [{
model: hp_state,
attributes: ['state_id', 'state_name'],
where: {
state_status: 1,
},
include:[{
model: hp_districts,
attributes: ['district_id', 'district_name'],
where: {
district_status: 1
},
include:[{
model: hp_city,
attributes: ['city_id', 'city_name'],
where: {
city_status: 1,
city_name :{
$like: '%ma%'
}
},
include:[{
model: hp_location,
attributes: ['location_id', 'location_name'],
where: {
location_status: 1,
location_name :{
$like: '%ma%'
}
},
include:[{
model: hp_sub_location,
attributes: ['sub_location_id', 'sub_location_name'],
where: {
sub_location_status: 1,
sub_location_name :{
$like: '%ma%'
}
}
}]
}]

}]
}]
}]
})

Answer

I tried to fix your query, but didn't test it. If you face any problem just notify me:

hp_country.findAll({
        attributes: ['country_id', 'country_name'],
        where: {
            //main AND condition
            $and: [
                //first joint condition
                {
                    $and: [
                        { country_status: 1 },
                        { country_id: 1 },
                        Sequelize.literal("Sta.state_status = 1")   //I put 'state_status' here because it was a joint condition to be true
                    ],
                },
                //second joint condition
                {
                    $or: [
                        Sequelize.literal("Dis.district_name LIKE '%ky%'"),
                        Sequelize.literal("Cit.city_name LIKE '%ky%'"),
                        Sequelize.literal("Loc.location_name LIKE '%ky%' "),
                        Sequelize.literal("Sub_Loc.sub_location_name LIKE '%ky%'"),
                        Sequelize.literal("Prop.property_name LIKE '%ky%'")
                    ]
                }
            ]
        },
        include: [
            {
                model: hp_state,
                attributes: ['state_id', 'state_name']
            },
            {
                model: hp_districts,
                attributes: ['district_id', 'district_name']
            },
            {
                model: hp_city,
                attributes: ['city_id', 'city_name']
            },
            {
                model: hp_location,
                attributes: ['location_id', 'location_name']
            },
            {
                model: hp_sub_location,
                attributes: ['sub_location_id', 'sub_location_name']
            }
        ]
    })