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.

joshng · Answer 1 · 2010-06-05T07:05:18.057

28

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)

edited Jun 05 '10 at 07:05

answered Jun 12 '09 at 06:04

joshng

1,530
1
14
16

1

cool... first my answer was selected, then I find I have a -1 and I've been DEselected. and now the author of the selected answer says mine is better..? heh, ouch :-} – joshng Jun 12 '09 at 06:27
well. I said yours was better when I saw it the first time, not after. I think your one-liner is superior. I gave you a +1 for your answer. I think you need to modify your answer to replace model.find_by_sql with Question.find_all_by_sql, then it will serve the author's purpose. – Ryan Oberoi Jun 12 '09 at 06:31
1

thanks, ryan. I'm not bitter, it's just my REPUTATION at stake... :-D – joshng Jun 12 '09 at 06:37

Ryan Oberoi · Accepted Answer · 2009-06-12T06:18:45.690

21

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)"])

edited Jun 12 '09 at 06:18

answered Jun 12 '09 at 06:05

Ryan Oberoi

13,817
2
24
23

can this be given in the options_from_collection_for_select in the select_tag? – Nave Jun 12 '09 at 06:12
1

I just edited it to change it from find to all. Select the version you like. I guess something made you change your mind. I like the other solution as well. Let us know why you selected my answer over the other one. – Ryan Oberoi Jun 12 '09 at 06:20

score 3 · Answer 3 · answered Mar 07 '12 at 12:13

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)

answered Mar 07 '12 at 12:13

Vikko

1,396
10
23

Rails SQL Query with find

3 Answers3

Linked