Rails - Feb 2011 - Active Record Query

A course has many lessons and a lesson has many topics.
A topic can have a recipe or vice versa, a recipe has many topics
(optional).

I need to come up with the most efficient way of querying the database
to find those topics belonging to a course (via lessons of course) which
have a recipe (in other words recipe_id is not null for this topic
record).

Simple but challenging :)

Thanks for your time.

Bharat

-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On Sun, Feb 20, 2011 at 12:58 AM, Bharat Ruparel
<lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>wrote:
> A course has many lessons and a lesson has many topics.
> A topic can have a recipe or vice versa, a recipe has many topics
> (optional).
>
> I need to come up with the most efficient way of querying the database
> to find those topics belonging to a course (via lessons of course) which
> have a recipe (in other words recipe_id is not null for this topic
> record).
>
> What is most efficient, depends on whether you want to display only the
topics or you also want to display data from the course model, say
course''s
name etc.

As a hint, look for the difference between include and joins while doing
queries using associations.
Then, come up with your query, post it here and then ask if it is the best
way to do it, depending on your situation.
> Simple but challenging :)
>
> Thanks for your time.
>
> Bharat
>
> --
> Posted via http://www.ruby-forum.com/.
>
> --
> You received this message because you are subscribed to the Google Groups
> "Ruby on Rails: Talk" group.
> To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> To unsubscribe from this group, send email to
>
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> For more options, visit this group at
> http://groups.google.com/group/rubyonrails-talk?hl=en.
>
>
-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Many associations for reading the data from large tables will always kill 
the performance of the application...

If there are too many joins between tables I do prefer flat table read only 
data table rather on the transactional database..

Simple SQL Query for the same...

select *
from courses c, lessons l, topics t
where c.id = l.class_id 
AND l.id = t.topic_id
AND t.recipe_id IS NOT NULL


-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.