Order by count using pluck for associated table ruby

Question

Currently, my code looks like this:

Author.joins(:books).group("authors.id").order("count (authors.id) desc")

Which does an order by how many books the author has written. However, books is a very large database table. So, to speed things up without having to use a .joins, how would one write something that would work with a .pluck statement to get the books.id, such as:

Author.order("count (id: Book.uniq.pluck(:author_id)) desc")

(this code brings up a syntax error) Is this mainly an issue of how it has been written and formatted? Or, is there a better way to refactor this type of statement?

If the books table is too big, I suggest you use a [`counter_cache` column](http://guides.rubyonrails.org/association_basics.html#belongs-to-association-reference). This way you could easily order by books_count without having to look in the books table. — tegon, Apr 06 '17 at 19:31
I would think pluck would be worse than the join here since it will force a correlated subquery. The database will have to perform a select for each author. I agree with @tegon that a counter_cache would be the way to go. — rockusbacchus, Apr 06 '17 at 21:31

score 0 · Accepted Answer · edited May 23 '17 at 12:10

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.

Order by count using pluck for associated table ruby

1 Answers1