CODEWITHSUNDEEP
sqlruby-on-railsactiverecord
IanWhalen
IanWhalen

Reputation: 821

Querying for rows without matching ID in associated table

I have a very standard app backed by an SQL database with a User model, a Problem model, and a CompletedProblem model acting as a join table between the two.

I'm trying to create a method that returns all problems not solved by a particular user. I have run into a wall, however, and I would appreciate pointers on what my method should look like.

Below are the models as well as my latest (incorrect) pass at creating this method.

class User < ActiveRecord::Base
  has_many :completed_problems
  has_many :problems, :through => :completed_problems

  def unsolved_problems
    Problem.includes({:wall => :gym}, :completed_problems).
      where('completed_problems.user_id != ? OR completed_problems.user_id IS NULL)', self.id)
  end
end

class Problem < ActiveRecord::Base
  has_many :completed_problems
  has_many :users, :through => :completed_problems
end

class CompletedProblem < ActiveRecord::Base
  belongs_to :user
  belongs_to :problem
end

(For the curious: this method does work so long as there is only one user marking problems as solved. As soon as you add a second, each user starts to return only those problems that have been solved by other users, instead of those not solved by herself.)

Upvotes: 1

Views: 54

Answers (2)

Matthew
Matthew

Reputation: 13332

I think something like this will do it:

Problem.where(["id NOT IN (?)", self.problems.all.map(&:id)])

Upvotes: 0

IanWhalen
IanWhalen

Reputation: 821

Via a friend:

select * from problems where id not in (select problem_id from completed_problems where user_id = USER_ID))

Although I'd still be interested in hearing if there's a way in ActiveRecord to do this.

Upvotes: 1

Related Questions