CODEWITHSUNDEEP
ruby-on-railsrubyrails-activerecord
user3092118
user3092118

Reputation: 371

ordering results by frequency in Active Record

I am new to Rails and Active Record queries. I want to order my query results by frequency.

As an SQL query it be:

SELECT author FROM books GROUP BY author ORDER BY COUNT(*) DESC LIMIT 10;

(Which I found here and was very helpful in framing this question.)

What I have right now will do it alphabetically:

<p>The authors are: <% @books.select(:author).distinct.order(:author).each do |book| %>
  <br><%= book.author %> <%= @books.where(author: book.author).count %>
<% end %></p>

Which will get me, e.g.:

Asimov, Isaac 2

Dick, Philip K. 8

Pynchon, Thomas 3

etc. But I want to order the results by frequency (Dick, Pynchon, Asimov), not alphabetically.

I want to leave the author name and number of books as separate outputs if possible (want to be able to switch it to a table), although I'm sure there's a better way of getting the frequency in the output (instead of @books.where(author: book.author).count ), too. But the main issue is getting the initial ordering right.

Any help is greatly appreciated.

Upvotes: 2

Views: 1507

Answers (2)

jvnill
jvnill

Reputation: 29599

Using the sample query below and the generated sql

Task.group(:project_id).limit(10).count(:id)
SELECT COUNT(`tasks`.`id`) AS count_id, project_id AS project_id FROM `tasks` GROUP BY project_id LIMIT 10
Output: {18=>1206, 22=>82, 23=>25, 25=>77, 29=>1, 35=>1, 42=>30, 44=>1, 45=>17, 53=>12}

You can see that by convention, rails uses count_<column name> for the count. You can use this convention to order by that count.

Task.group(:project_id).limit(10).order('count_id DESC').count(:id)
SELECT COUNT(`tasks`.`id`) AS count_id, project_id AS project_id FROM `tasks` GROUP BY project_id ORDER BY count_id DESC LIMIT 10
Output: {145=>1949, 105=>1535, 18=>1206, 141=>738, 23804=>667, 9892=>524, 196=>478, 26969=>338, 24119=>330, 28047=>300}

A better way to do what you want to achieve is to prepare the variables in the controller and access them in the view

# controller
@counts = Book.group(:author).order('count_id DESC').count(:id)

# view
<% @counts.each do |author, count| %>
  <%= author %> <%= count %>
<% end %>

@counts above contains a hash where the keys are the author, and the values are the counts of the books by that author.

Upvotes: 2

usha
usha

Reputation: 29349

Try this

Book.group("author").order("count_author desc").count("author")

So your view will look like

<% @books.group("author").order("count_author desc").count("author").each do |author, count| %>
  <br><%= author %> <%= count %>
<% end %></p>

Upvotes: 4

Related Questions