How to Sort Authors by Their Book Count with Activerecord

How to sort authors by their book count with ActiveRecord?

As Kevin has suggested, counter_cache is the easiest option, definitely what I would use.

class Author < ActiveRecord::Base
  has_many :books, :counter_cache => true
end

class Book < ActiveRecord::Base
  belongs_to :author
end

And if you are using Rails 2.3 and you would like this to be the default ordering you could use the new default_scope method:

class Author < ActiveRecord::Base
  has_many :books, :counter_cache => true

  default_scope :order => "books_count DESC"
end

books_count is the field that performs the counter caching behaviour, and there is probably a better way than using it directly in the default scope, but it gives you the idea and will get the job done.

EDIT:

In response to the comment asking if counter_cache will work if a non rails app alters the data, well it can, but not in the default way as Rails increments and decrements the counter at save time. What you could do is write your own implementation in an after_save callback.

class Author < ActiveRecord::Base
  has_many :books

  after_save :update_counter_cache

  private
    def update_counter_cache
      update_attribute(:books_count, self.books.length) unless self.books.length == self.books_count
    end
end

Now you don't have a counter_cache installed, but if you name the field in the database books_count as per the counter_cache convention then when you look up:

@Author = Author.find(1)
puts @author.books.size

It will still use the counter cached number instead of performing a database lookup. Of course this will only work when the rails app updates the table, so if another app does something then your numbers may be out of sync until the rails application comes back an has to save. The only way around this that I can think of is a cron job to sync numbers if your rails app doesn't do lookup up things often enough to make it not matter.

rails order by association through another association?

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.

Rails ActiveRecord sort by count of join table associations

Try the following:

@resources = Resouce.select("resources.*, COUNT(votes.id) vote_count")
                    .joins(:votes)
                    .where(language_id: "ruby")
                    .group("resources.id")
                    .order("vote_count DESC")

@resources.each { |r| puts "#{r.whatever}  #{r.vote_count}" }

To include resources with 0 votes, use an outer join. If the example below doesn't work as is you'll have to alter the joins statement to join across the correct relations.

@resources = Resource.select("resources.*, COUNT(votes.id) vote_count")
                     .joins("LEFT OUTER JOIN votes ON votes.votable_id = resources.id AND votes.votable_type = 'Resource'")
                     .where(language_id: "ruby")
                     .group("resources.id")
                     .order("vote_count DESC")

Order by count using pluck for associated table ruby

You can read SO question about ORDER BY the IN value list. Only available option, without join and without creating additional Postgres functions, is use order like this:

ORDER BY  id=1 DESC, id=3 DESC, id=2 DESC, id=4 DESC

You may build such a query in Rails:

ids = Book.group(:author_id).order("COUNT(*) DESC").pluck(:author_id)

order_by = ids.map { |id| "id=#{id} DESC" }.join(", ")

Author.order(order_by)

This will work. Though in case when Author table contains a lot of records better to use a counter_cache column (as recommended by @tegon) for performance reasons.

Rails selecting objects with conditions on associations

You can do it with sql:

 Author.where(:active => true).joins(:books).group("author_id HAVING count(books.id) > 0")

How can I implement sorting with Active Model Serializer?

In my experience AMS doesn't really concern itself with sorting. This seems like something you'd do before serializing. Perhaps in the controller or better yet in the model.

UPDATE

@CalebSayre if your sort parameter is just a simple list of fields to sort by you could probably get away with something as simple as this.

render json: Author.order(params[:sort]), serializer: AuthorSerializer

If no sort params are passed in it'll just skip the order clause.

Rails order Post by def in model

You should try counter cache.
You can read more about it from the following links -

How to sort authors by their book count with ActiveRecord?

http://hiteshrawal.blogspot.com/2011/12/rails-counter-cache.html

http://railscasts.com/episodes/23-counter-cache-column

Counter cache only works inside rails. If you updated from outside application you might have to do some work around.

---

Related Topics