Rails SQL Query with find

Question

I want this SQL query to be written in rails controller using find:

select id,name from questions
where id not in (select question_id from levels_questions where level_id=15)

How will I do this? I am using Rails framework and MySQL. Thanks in advance.

Answer 1

Question.find_all_by_sql('select id,name from questions where id not in (select question_id from levels_questions where level_id=15)')

This is admittedly non-ActiveRecord-ish, but I find that complicated queries such as this tend to be LESS clear/efficient when using the AR macros. If you already have the SQL constructed, you might as well use it.

Some suggestions: encapsulate this find call in a method INSIDE the Question class to hide the details from the controller/view, and consider other SQL constructions that may be more efficient (eg, an OUTER JOIN where levels_questions.question_id is null)

Answer 2

Simple way:

ids = LevelsQuestion.all(:select => "question_id", 
        :conditions => "level_id = 15").collect(&:question_id)
Question.all(:select => "id, name", :conditions => ["id not in (?)", ids])

One shot:

Question.all(:select => "id, name",
:conditions => ["id not in (select question_id from levels_questions where level_id=15)"])

Answer 3

And the rails 3 way:

ids = LevelsQuestion.select(:question_id).where(:level_id => 15).collect(&:question_id)
Question.select(:id, :name).where("id not in (?)", ids)