CODEWITHSUNDEEP
ruby-on-railsrails-activerecord
Chris Hecker
Chris Hecker

Reputation: 83

How to get rails to do the correct single SQL join query for this iteration?

I have basically no experience with Rails, and I'm trying to help a friend who is learning how to program, but I have a lot of experience with SQL, and I'm struggling to get Rails to do the right thing for a simple query with a join.

I have a two tables, drawings and drawing_types. drawings has a foreign key pointing into drawing_types, which has a type_description column. This is obviously the most bare bones relation you can imagine. In SQL, I'd do this to get all the drawings with their correct type_descriptions:

SELECT d.*, dt.type_description FROM drawings as d
INNER JOIN drawing_types as dt ON dt.id = d.drawing_type_id;

This is a single efficient SQL query that returns exactly what I want: a list of drawings that also have their text type description.

I cannot, for the life of me, get rails to generate and use a single query like this. The best I can do is get it to do two queries, which clearly indicate it's doing the "join" in code, rather than letting the db do it. Sometimes it likes to do N+1 queries!?!

Here is the code for the model:

class Drawing < ActiveRecord::Base
  attr_accessible :image, :drawing_type_id
  belongs_to :drawing_type
end

class DrawingType < ActiveRecord::Base
  attr_accessible :type_description
  has_many :drawings
end

Here is the relevant controller code:

@drawings = Drawing.includes(:drawing_type)

And here is the view:

<% @drawings.each do |drawing| %>
  <tr>
    <td><b><%= drawing.drawing_type.type_description %></b></td>

This generates two SQL queries, one on drawings.*, and the other to get all the descriptions, which are then clearly being looked up in code. If I change the includes to a joins it does 1+M+N SQL queries (M = number of drawing types, N = number of drawings)!?!?!?!

I can get it to emit the correct SQL join to the log by using this (from here):

@drawings = Drawing.select("drawings.*, drawing_types.type_description").
     joins("INNER JOIN drawing_types ON drawing_types.id = drawings.drawing_type_id")

but, rails only seems to use the result as an array of drawings, ignoring the type_description column in the result, so it does an additional M+N queries!

All of these variations "work" in the sense that the page renders correctly for all of them, but they are all the wrong way of doing this simple thing, relative to the single correct SQL join that should be executed.

There simply has to be a simple way to do this correctly since it's the most basic of relations you might find in a totally boring website schema, but I have searched high and low, including reading lots of answers here and all the docs, and have not been able to figure out how to do this.

Thanks, Chris

Upvotes: 2

Views: 2589

Answers (2)

Marc O&#39;Morain
Marc O&#39;Morain

Reputation: 3759

I had the same problem recently in work, and found that you can do this using .joins() and .select(). I found the best way to prototype was to try different things in the rails console until you get the query that you want.

Controller:

@drawings = Drawing.joins(:drawing_type).select([:name, :description]).all

This will generate:

Drawing Load (0.4ms)  SELECT name, description FROM `drawings`
  INNER JOIN `drawing_types` ON `drawing_types`.`id` = `drawings`.`drawing_type_id`

In my example I have a field 'description' on DrawingType, rather than 'type_description', but this should be the only difference.

And the corresponding code in in the View:

<% @drawings.each do |drawing| %>
  <tr>
    <%= content_tag, :td, drawing.name %>
    <%= content_tag, :td, drawing.description %>
  </tr>
<% end %>

Upvotes: 2

Joshua Sierles
Joshua Sierles

Reputation: 31

This behavior is intentional, as in many cases using two queries will run quicker than one. See this answer for a good explanation of the reasoning: How do I avoid multiple queries with :include in Rails?

That said, you can force a single query by specifying a where condition on the relation, but it's not recommended.

Upvotes: 0

Related Questions