querying on where association in sequelize?

Question

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%');

I wrote in sequelize code is

hp_country.findAll({
    where: {
        '$hp_state.state_status$': 1
    },
    include: [
        {model: hp_state}
    ]
})

The error it's producing is:

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'

Answer 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.

Answer 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

                                                    }
                                                    ]
                                            }
                                            ]


                                    }]

                            }]

                    }]
            }
        ]
    }
]

})