Active Record Query

Rails
1

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 :slight_smile:

Thanks for your time.

Bharat

2

On Sun, Feb 20, 2011 at 12:58 AM, Bharat R.
[email protected]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.

3

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