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

Thanks for your time.

Bharat

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.

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