CODEWITHSUNDEEP
ruby-on-railsjoinassociations
tehfailsafe
tehfailsafe

Reputation: 3451

rails order by association through another association?

As a simple example, let's say a bookstore has books which have one author. The books has many sales through orders. Authors can have many books.

I am looking for a way to list the authors ordered by sales. Since the sales are associated with books, not authors, how can I accomplish this?

I would guess something like: Author.order("sales.count").joins(:orders => :sales)

but that returns a column can't be found error.

I have been able to connect them by defining it in the Author model. The following displays the correct count for sales, but it does ping the database for each and every author... bad. I'd much rather eager load them, but I can't seem to get it to work properly since it will not list any authors who happen to have 0 sales if I remove the self.id and assign the join to @authors.

    class Author < ActiveRecord::Base

      def sales_count
        Author.where(id: self.id).joins(:orders => :sales).count
      end
    end

And more specifically, how can I order them by the count result so I can list the most popular authors first?

Upvotes: 0

Views: 365

Answers (1)

PinnyM
PinnyM

Reputation: 35531

Firstly, let's have all associations available on the Author class itself to keep the query code simple.

class Author < AR::Base
  has_many :books
  has_many :orders, :through => :books
  has_many :sales, :through => :orders
end

The simplest approach would be for you to use group with count, which gets you a hash in the form {author-id: count}:

author_counts = Author.joins(:sales).group("authors.id").count
=> {1 => 3, 2 => 5, ... }

You can now sort your authors and lookup the count using the author_counts hash (authors with no sales will return nil):

<% Author.all.sort_by{|a| author_counts[a.id] || 0}.reverse.each do |author| %>
  <%= author.name %>: <%= author_counts[author.id] || 0 %>
<% end %>

UPDATE

An alternative approach would be to use the ar_outer_joins gem that allows you get around the limitations of using includes to generate a LEFT JOIN:

authors = Author.outer_joins(:sales).
                 group(Author.column_names.map{|c| "authors.#{c}").
                 select("authors.*, COUNT(sales.id) as sales_count").
                 order("COUNT(sales.id) DESC")

Now your view can just look like this:

<% authors.each do |author| %>
  <%= author.name %>: <%= author.sales_count %>
<% end %>

This example demonstrates how useful a LEFT JOIN can be where you can't (or specifically don't want to) eager load the other associations. I have no idea why outer_joins isn't included in ActiveRecord by default.

Upvotes: 2

Related Questions