Ruby on Rails / ActiveRecord: How Can I (Elegantly) Retrieve Data from Multiple Tables?

user_13520336 0
Viewed 40

It's rather trivial to retrieve data from multiple tables that are related through foreign keys using raw SQL. I can do, for example:

SELECT title, domestic_sales
FROM movies
  JOIN boxoffice
    ON movies.id = boxoffice.movie_id;

This would give me a table with two colums: title and domestic_sales, where the data in the first column comes from the table movies and the data in the second column comes from the table boxoffice.

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:

ActiveRecord::Base.connection.execute(<<-SQL)
SELECT title, domestic_sales
FROM movies
  JOIN boxoffice
    ON movies.id = boxoffice.movie_id;
SQL

This would give me a PG::Result 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.

So, this is the first thing that comes to mind is:

Movie.select(:name, :domestic_sales).joins(:box_office)

The problem, however, is that the aforementioned line of code returns a bunch of Movie objects. Since the Movie class doesn't have the domestic_sales attribute, I don't get access to that information.

The next thing I thought was to use a loop. So, I could do something like:

Movie.joins(:box_office).to_a.map do |m|
    {name: m.name, rating: m.box_office.domestic_sales}
end

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...

So: How can I retrieve the data I want without using raw SQL and without using loops that cost multiple queries?

ruby-on-rails ruby
2 Answers
SELECT title, domestic_sales
FROM movies
  JOIN boxoffice
    ON movies.id = boxoffice.movie_id;

translated to ActiveRecord would look like this

Movie
  .select(:title, :domestice_sales)
  .joins("boxoffice ON movies.id = boxoffice.movie_id")

When you have proper associations defined in your models you would would be able to write:

Movie
  .select(:title, :domestice_sales)
  .joins(:boxoffices)

And when you do not need an instance of ActiveRecord and would be fine with a nested array, you can even write:

Movie
  .joins(:boxoffices)
  .pluck(:title, :domestice_sales)
user_24833130

Try this way.

Movie.joins(:box_office).pluck(:title, :domestic_sales)

user_103848790
Related