CODEWITHSUNDEEP
ruby-on-railscontrollerassociations
old_no_7uk
old_no_7uk

Reputation: 389

rails how to find with no associated records

I know that this will be an easy one but I'm having real issues working it out.

I have users that can have_many results. I'm trying to work out how to return users that don't yet have any results to the @starters object(from the controller).

@users = @event.entires

@starters = @users.where("results = ?", 0)

could anyone explain how i would check if a user has no results?

Upvotes: 5

Views: 3658

Answers (4)

Raindal
Raindal

Reputation: 3237

Best solution (as MrYoshiji commented)

@starters = @users.includes(:results).where(results: { id: nil })

This will execute the same query as the one in my second solution.

Other SQL solution

You could use a LEFT OUTER JOIN. This way, you will always have all the results from the "left" table (users) but you will also have matching records for the "right" table (results) eventhough there are non, which will leave you with empty fields that you can check.

@starters = @users.joins("LEFT OUTER JOIN results ON results.user_id = users.id").where("results.user_id IS NULL")

In your case, replace users with the name of your "user" model.

Other Ruby solution

@starters = @users.select { |u| !!u.results }

Here !! will force conversion to a boolean, if there are no results, u.results will return [] (empty array). And !![] equals true.

Upvotes: 13

Drew
Drew

Reputation: 2663

This should get all of the users that do not have any results:

@starters = ActiveRecord::Base.execute("select * from users where id not in (select user_id from results)")

Another way to do this would be the following:

User.where("id not in ?", "(select user_id from results)")

Upvotes: 0

cortex
cortex

Reputation: 5206

If your @users is an Array, try:

@starters = @users.select { |u| u.results.empty? }

Upvotes: 0

Kirk
Kirk

Reputation: 513

Try left joining and finding which one is null

@users.joins("LEFT OUTER JOIN user ON user.id = result.user_id").where("result.id IS NULL")

Upvotes: 0

Related Questions