MIdhun Krishna MIdhun Krishna - 1 year ago 85
SQL Question

SQL selecting users with specific type of cars

Customer has_many cars

each Car belongs to ManufacturingPlant

ManufacturingPlant has an attribute called :country

How to find Customer whose car is not manufactured in India?

Customer.joins(:cars).where.not(cars: {manufacturing_plant_id: ManufacturingPlant.find_by_country("india").id})

In this case, if a user has a car made in China and in India, the user should not be shown up in the result since he has a car manufactured in an Indian plant. But with the above query it does.

Also, how can we write a query that doesnt use any of array operations eg IN ?

Answer Source

Hey you can try this way:

For more info refer somewhat similar my post

Customer.joins({cars: [:manufacturing_plant]}).group("cars.customer_id").having("SUM(CASE WHEN manufacturing_plant.country = 'india' THEN 1 
        ELSE 0 
        END) = 0")
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download