CODEWITHSUNDEEP
ruby-on-railsactiverecord
randombits
randombits

Reputation: 48490

Ruby on Rails ActiveRecord query using a join

I have a User model and the user has a relationship which is has_many pets. I want to be able to write an ActiveRecord query where I can select all users with a pet that doesn't have a pet.name of "fluffy"

What's the most efficient way to write this using ActiveRecord? Using straight SQL it would look something such as the following:

select id from users INNER JOIN pets ON u.id = pets.user_id WHERE pets.name != "fluffy"

Upvotes: 56

Views: 106163

Answers (3)

Albin
Albin

Reputation: 3032

In rails 4 you can make this even more clear:

User.joins(:pets).where.not(pets: { name: 'fluffy' })

Upvotes: 77

gertas
gertas

Reputation: 17145

The cleanest way without SQL injection vulnerability is using query parameters:

User.joins(:pets).where("pets.name != ?", "fluffy")

Some database drivers will utilize prepared statements for above to reuse database query plan. In such case the database doesn't have to analyze query again when only param value varies.

Upvotes: 26

Amokrane Chentir
Amokrane Chentir

Reputation: 30405

This should work: 

User.joins(:pets).where("pets.name != 'fluffy'")

Also you might want to read the following part (on joins) on the official RoR guidelines.

Upvotes: 66

Related Questions