CODEWITHSUNDEEP
ruby-on-railsactiverecord
Hassen
Hassen

Reputation: 7654

ActiveRecord Rails: Get records using a condition in the relation

Here is an example: a User has many Cars and a Car belongs to a User.

I would like to extract all cars information, but not the cars of some users (this is because I would like to remove me and some colleagues to create correct stats).

I tried this, but without success:

Car.where("car.user.name != 'john'")

Any idea? Do you have a general rule about getting records with conditions in the relation?

Thanks.

Upvotes: 0

Views: 726

Answers (3)

Bachan Smruty
Bachan Smruty

Reputation: 5734

Have a try with

Car.joins([:user]).where("users.name != 'John'")

If you need all the cars information with user information like users.name, then you can have

Car.joins([:user]).select('cars.*, users.name AS user_name').where("users.name != 'John'")

The above does the thing with a single sql query

SELECT cars.*, users.name AS user_name FROM `cars` INNER JOIN `users` ON `users`.`id` = `cars`.`user_id` WHERE (users.name != 'John')

Upvotes: 0

Jean-Théo
Jean-Théo

Reputation: 442

Car.includes(:users).where("users.name NOT IN ('bill','peter','joe') OR cars.user_id IS NULL")

It will return all cars that don't have a user name that is Bill, Peter or Joe, and also cars that don't belong to a user !

Upvotes: 0

BroiSatse
BroiSatse

Reputation: 44685

Try the follwing:

Car.where('user_id != ?', User.find_by_<name?>('john').id

If you have rails 4:

Car.where.not(user_id: User.find_by(name: 'john')).id

UPDATE:

The solution above will work because you have a foreign key you can query against. More general solution is to perform left join with association table and filter those results. THe following will work regardless of association type (including has_many :through and has_and_belongs_to_many):

Car.includes(:user).where('users.name != ?', 'john')

# rails 4

Car.includes(:user).where.not(users: { name: 'john'})

Upvotes: 2

Related Questions