views:

85

answers:

3

Hi all

I really suck at Rails' finders besides the most obvious. I always resort to SQL when things get more advanced than

Model.find(:all, :conditions => ['field>? and field<? and id in (select id from table)', 1,2])

I have this method:

def self.get_first_validation_answer(id)
a=find_by_sql("
  select answers.*, answers_registrations.answer_text
  from answers_registrations left join answers on answers_registrations.answer_id=answers.id
  where 
    (answers_registrations.question_id in (select id from questions where validation_question=true)) 
    and
    (sale_registration_id=#{id})
  limit 1
").first

a.answer_text || a.text if a
end

Can someone create a find method that gets me what I want?

Regards,

Jacob

A: 

Just use binarylogic's Searchlogic gem if that satisfies your need. Here you go: http://github.com/binarylogic/searchlogic

Shripad K
Thanks for your reply. I know that there are gems and plugins that can help but for some reason I like to use the stock stuff where possible - so I would like the original question answered.
jriff
+4  A: 
class AnswersRegistration < ActiveRecord::Base
  has_many :answers
end

id = 123
the_reg = AnswersRegistration.first(
  :joins => :answers, 
  :conditions => '(question_id in (select id from questions where validation_question = true)) and (sale_registration_id = ?)', id)

(untested)

Jonathan Julian
Thanks - that is as Rails-like as it gets? I have no problem doing it that way - I just thought that one could to it almost without writing any real SQL at all. You just do ":joins" - how does Rails know that it should be a left join?
jriff
By default, `:joins` uses an inner join. To do a left join, you have to specify some sql: `:joins => "left join answers on answers_registrations.answer_id=answers.id"`
Jonathan Julian
A: 

Sometimes AR will choke on complicated nested conditions, but in theory you should be able to do this:

AnswersRegistration.first(:conditions => { :question => { :validation_question => true },
                                           :sale_registration_id => id },
                          :include => :answer) 
mckeed