Rails - Jun 2005 - Complex Query - ActiveRecord

Hello,
I''m need to do one query like:
SELECT answers.* FROM answers, surveys, questions
WHERE survey.id = x AND answer.question_id = question.id AND survey.id = 
question.survey_id

Can I do this using ActiveRecord ??
Considering that :
 -Survey has many Questions,
 -Questions has many Surveys and Belongs to Survey and
 -Answers Belongs to Question
*I need find Answers of one Survey ID, can I do with activerecord? how?*
thank you!

-- 

Pedro C. Valentini
pedro-p14LI7ZcAE/pVLaUnt/cCQC/G2K4zDHf@public.gmane.org
+55 (21) 8708-8035



_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

On Jun 23, 2005, at 3:50 PM, Pedro Valentini wrote:
> Hello,
> I''m need to do one query like:
> SELECT answers.* FROM answers, surveys, questions
> WHERE survey.id = x AND answer.question_id = question.id AND  
> survey.id = question.survey_id
>
> Can I do this using ActiveRecord ??
> Considering that :
>  -Survey has many Questions,
>  -Questions has many Surveys and Belongs to Survey and
>  -Answers Belongs to Question
> I need find Answers of one Survey ID, can I do with activerecord? how?
> thank you!
One way:

   answers = Answer.find_by_sql(<<-SQL)
     SELECT answers.*
       FROM answers, surveys, questions
      WHERE survey.id = #{survey_id}
        AND answer.question_id = question.id
        AND survey.id = question.survey_id
   SQL

If it is something you do often, you''ll probably want to put it in a  
method of Answer:

   class Answer < ActiveRecord::Base
     def self.find_by_survey_id(survey_id)
       ...
     end
   end

which lets you do:

   answers = Answer.find_by_survey_id(x)

Hope that helps. Just keep in mind that one of AR''s design  
philosophies is: not only is SQL is NOT bad (unlike many other ORM  
frameworks), it is often the best tool for the job. To that end, AR   
makes it very easy to drop down into SQL when necessary.

Jamis

For as far as I understand, you actually don''t need the survey table in
the query (you have a survey_id in the questions table), and

SELECT answers.* FROM answers LEFT JOIN questions ON answers.question_id 
= questions.id WHERE questions.survey_id = x

should give you the answers you are looking for. This can be achieved 
with the following call:

Answer.find(:all,
             :conditions => ["questions.survey_id = ?", x],
             :include => [:questions])

See http://api.rubyonrails.com/classes/ActiveRecord/Base.html#M000650

for the gory details about this call. Also you can check in the files in 
the log directory how rails actually queries the database for more 
understanding...

The find_by_sql call mentioned in the post above is also a very good way 
of quickly solving this problem :-)

Best regards,
Bas



Pedro Valentini wrote:
> Hello, I''m need to do one query like: S ELECT answers.* FROM
answers,
> surveys, questions WHERE survey.id = x AND answer.question_id >
question.id AND survey.id = question.survey_id
> 
> Can I do this using ActiveRecord ?? Considering that : -Survey has
> many Questions, -Questions has many Surveys and Belongs to Survey and
>  -Answers Belongs to Question *I need find Answers of one Survey ID,
> can I do with activerecord? how?* thank you!
> 
> --
> 
> Pedro C. Valentini pedro-p14LI7ZcAE/pVLaUnt/cCQC/G2K4zDHf@public.gmane.org
> <mailto:pedro-p14LI7ZcAE/pVLaUnt/cCQC/G2K4zDHf@public.gmane.org> +55
(21) 8708-8035
> 
> 
> ------------------------------------------------------------------------
> 
> 
> _______________________________________________ Rails mailing list 
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org 
> http://lists.rubyonrails.org/mailman/listinfo/rails

>    class Answer < ActiveRecord::Base
>      def self.find_by_survey_id(survey_id)
>        ...
>      end
>    end
> 
> which lets you do:
> 
>    answers = Answer.find_by_survey_id(x)
Adding to jamis'' answer, I''d suggest adding a method on Survey
called
list_answers like this

class Survey < AR::Base
  def list_answers
    Answer.find_by_survey_id(id)
  end
end
 
Encapsulating the finders this way lets you add caching, logging, etc.
at a later date without having to do a lot of grep+sed.
> Hope that helps. Just keep in mind that one of AR''s design
> philosophies is: not only is SQL is NOT bad (unlike many other ORM
> frameworks), it is often the best tool for the job. To that end, AR
> makes it very easy to drop down into SQL when necessary.
Amen to that.
 
> Jamis
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
> 

-- 
Cheers

Koz