CODEWITHSUNDEEP
sqlruby-on-railsrubyactiverecordruby-on-rails-4
mecampbellsoup
mecampbellsoup

Reputation: 1471

Rails: get parent records having ZERO has_many associations

Here's my associations:

class User < ActiveRecord::Base
  has_many :tickets
end

class Ticket < ActiveRecord::Base
  belongs_to :user
end

Pretty straightforward. I want a scope on the User model which returns all users having exactly ZERO associated tickets.

Presently I'm using Ruby to do it for the most part like so:

def self.never_flown
  results = []
  find_each do |user|
    results << user if user.tickets.count == 0
  end
  results
end

... which sucks because this isn't a good use-case for Ruby. But I'm struggling to figure out how to do this using SQL.

Can someone let me know a good, clean SQL solution to this?!

Upvotes: 1

Views: 209

Answers (3)

Sebastian Sogamoso
Sebastian Sogamoso

Reputation: 353

You can also do something like this:

User.joins("LEFT OUTER JOIN orders ON orders.user_id = users.id WHERE orders.user_id IS NULL")

Although @oliver-roset's approach is a bit simpler and easier to understand for anyone reading your code later.

Upvotes: 1

showaltb
showaltb

Reputation: 998

A SQL subquery could be used, which might be more efficient than using includes():

User.where('id not in (select user_id from tickets)')

Upvotes: 1

Olivier Rosset
Olivier Rosset

Reputation: 153

Maybe something like: User.includes(:tickets).where(tickets: {user_id: nil})

in a scope it would be like :without_tickets, -> { includes(:tickets).where(tickets: {user_id: nil}) }

Upvotes: 1

Related Questions