Querying On Where Association In 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.co

Solution 1:

Your Sequelize code should look like:

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,
            state_name: {
                $like:'%ta%'
            }
        }
    }]
});

To select only some attributes, you can use the attributes option. where clause should be moved inside the include statement because the condition you use relates to the hp_state model.

Solution 2:

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

                                                    }
                                                    ]
                                            }
                                            ]


                                    }]

                            }]

                    }]
            }
        ]
    }
]

})