checks on associated models from top model where query

Question

I have a model Booking, which is having hasMany relation with hotels, and hotel is having one to one relation with supppliers. What i need is, get all booking where supplier_id = 33333. I am trying this

BOOKINGS.findAll({
        where: {
            'hotels.supplier.supplier_id' : '32',                
        },
        include: [
            {
            model: HOTELS,
            include: [
                {
                    model: SUPPLIERS,
            ],

            }
        ],
        limit : 30,
        offset: 0

    })

It throws error like hotels.supplier... column not found.. I tried all things because on docs of sequelze it only gives solution to add check which adds where inside the include which i can't use as it adds sub queries. I don't want to add where check alongwith supplier model inside the include array, because it adds sub queries, so If i am having 1000 bookings then for all bookings it will add sub query which crashes my apis.

I need a solutions like this query in Sequelize.

Select col1,col2,col3 from BOOKINGS let join HOTELS on BOOKINGS.booking_id = HOTELS.booking_id, inner join SUPPLIERS on BOOKINGS.supplier_id = SUPPLIERS.supplier_id

Hussain Nawaz Lalee Hussain Nawaz Lalee · Accepted Answer · 2019-11-05T21:15:10

Adding a where in the include object will not add a sub query. It will just add a where clause to the JOIN which is being applied to the supplier model. It will not crash your API in anyway. You can test it out on your local machine plenty of times to make sure.

BOOKINGS.findAll({
   include: [
      {
         model: HOTELS,
         include: [
            {
               model: SUPPLIERS,
               where: { supplier_id: 32 }
            }
         ]
      }
   ],
   limit: 30,
   offset: 0
})

If you still want to use the query on the top level you can use sequelize.where+ sequelize.literal but you will need to use the table aliases that sequelize assigns. e.g this alias for supplier table will not work hotels.supplier.supplier_id. Sequelize assings table aliases like in the example I have shown below:

BOOKINGS.findAll({
    where: sequelize.where(sequelize.literal("`hotels->suppliers`.supplier_id = 32")),
    include: [
        {
            model: HOTELS,
            include: [SUPPLIERS]
        }
    ],
    limit: 30,
    offset: 0
})

checks on associated models from top model where query

1 Answers