title: Ruby on Rails / ActiveRecord: How Can I (Elegantly) Retrieve Data from Multiple Tables?
body: <p>It's rather trivial to retrieve data from multiple tables that are related through foreign keys using raw SQL. I can do, for example:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT title, domestic_sales
  FROM movies
    JOIN boxoffice
      ON movies.id = boxoffice.movie_id;
  </code></pre>
  
  <p>This would give me a table with two colums: <code>title</code> and <code>domestic_sales</code>, where the data in the first column comes from the table <code>movies</code> and the data in the second column comes from the table <code>boxoffice</code>.</p>
  
  <p>How can I do this in Rails using Ruby code? I can, of course, get the same result if I use raw SQL. So, I could do the following:</p>
  
  <pre class="lang-rb prettyprint-override"><code>ActiveRecord::Base.connection.execute(&lt;&lt;-SQL)
  SELECT title, domestic_sales
  FROM movies
    JOIN boxoffice
      ON movies.id = boxoffice.movie_id;
  SQL
  </code></pre>
  
  <p>This would give me a <code>PG::Result</code> object with the data I want. But this is super inelegant. I would like to be able to get this information without using raw SQL.</p>
  
  <p>So, this is the first thing that comes to mind is:</p>
  
  <pre class="lang-rb prettyprint-override"><code>Movie.select(:name, :domestic_sales).joins(:box_office)
  </code></pre>
  
  <p>The problem, however, is that the aforementioned line of code returns a bunch of <code>Movie</code> objects. Since the <code>Movie</code> class doesn't have the <code>domestic_sales</code> attribute, I don't get access to that information.</p>
  
  <p>The next thing I thought was to use a loop. So, I could do something like:</p>
  
  <pre class="lang-rb prettyprint-override"><code>Movie.joins(:box_office).to_a.map do |m|
      {name: m.name, rating: m.box_office.domestic_sales}
  end
  </code></pre>
  
  <p>This gives me exactly the data I want. But it costs n + 1 SQL queries, which is not good. I should be able to get this with just one query...</p>
  
  <p>So: How can I retrieve the data I want without using raw SQL and without using loops that cost multiple queries?</p>