CODEWITHSUNDEEP
mysqlsqlruby-on-railsruby
maxedison
maxedison

Reputation: 17573

Using a has_many relationship resulting in an additional MySQL query?

I'm going through the Lynda.com course "Ruby on Rails 4 Essential Training". It uses the creation of a simple CMS to teach the framework. In this CMS, users can create Subjects and Pages. Each Subject has many Pages. When clicking a link to view the pages that belong to a particular subject, it passes the subject id as a parameter to the Pages controller. The pages controller then does the following to determine which pages to show:

@subject = Subject.find(params[:subject_id])
@pages = @subject.pages

This simple, makes sense, and results in very understandable code. However, it results in an extra DB call by first loading the Subject, and then finding its pages. In the console I see:

  Subject Load (0.3ms)  SELECT  `subjects`.* FROM `subjects` WHERE `subjects`.`id` = 3 LIMIT 1
  Page Load (0.3ms)  SELECT `pages`.* FROM `pages` WHERE `pages`.`subject_id` = 3  ORDER BY pages.position ASC

It seems to me that the more efficient approach would be to write something like: 

@pages = Page.where(subject_id: params[:subject_id])

Indeed, I see only a single SQL query when I do that. Is it considered best practice to use the first approach, despite the fact that it results in 2 queries? Or is that just being used for learning purposes, and ultimately my approach would be considered the better of the two?

Upvotes: 0

Views: 59

Answers (2)

Martin
Martin

Reputation: 7723

It depends first whether you need both the @subject and the @pages object. You are right that your proposal is more performant, now this may or may not make a real difference for your user - you'll need profiling to be sure.

Finally, it is possible to make a slight change to the tutorial code to make it as performant using 'includes':

@subject = Subject.includes(:pages).find(params[:subject_id])
@pages = @subject.pages

This will load everything with a single query.

Upvotes: 1

archit gupta
archit gupta

Reputation: 1034

If you dont want your @subject or basically the subject record, there is not need to use former, you can directly access pages with what you have written in your later approach.

Upvotes: 0

Related Questions