Rails, Ruby, How to Count and Sort, Display Top Results

rails, ruby, how to count and sort, display top results

First query the database like this:

@posts_per_user_count = Post.joins(:user).group(:user).order('count_all DESC').limit(10).count

This is going to result in a hash, which contains arrays with your result. Then in your view you can iterate over it like this:

<% @posts_per_user_count.each do |item| %>
    <%= item[0].username %>: <%= item[1].to_s %><br>
<% end %>

Please also see the answer from anonymousxxx. He is using the counter. This is more efficient than query the database in the way I do.

How to sort results by count and return in order of most to least using active record in Rails

Taken from the example I linked in the comments above.

Searches.find(:all, :select => '*, count(*) AS count, phrase', :group => 'phrase', :order => 'count DESC')

Although I just tried this on my own sqlite db and it worked fine (rails 3)

Searches.count(:all, :group => 'phrase', :order => 'count(*) DESC')

How to sort ActiveRecord results by associated model count?

Hey in mysql you can directly used like

@cafe_by_posts = Cafe.joins(:posts).group("posts.cafe_id").order('count(posts.cafe_id) desc')

You can directly select Top 2 using limit

 @cafe_by_posts = Cafe.joins(:posts).group("posts.cafe_id").order('count(posts.cafe_id) desc').limit(2)

Ruby on Rails: How to limit the results of sorting active record results

you can simply add .first(5) or .last(5).reverse

<%= @top5 = User.all.sort{|a,b| a.questions.where(ques_num: 2).count <=> b.questions.where(ques_num: 2).count}.reverse.first(5) %>

or

<%= @top5 = User.all.sort{|a,b| a.questions.where(ques_num: 2).count <=> b.questions.where(ques_num: 2).count}.last(5).reverse %>

Rails order by results count of has_many association

If you expect to use this query frequently, I suggest you to use built-in counter_cache

# Job Model
class Job < ActiveRecord::Base
  belongs_to :company, counter_cache: true
  # ...
end

# add a migration
add_column :company, :jobs_count, :integer, default: 0

# Company model
class Company < ActiveRecord::Base
  scope :featured, order('jobs_count DESC')
  # ...
end

and then use it like

@featured_company = Company.featured

How to rank an Object using views and date of creation and like count?

First off: if you have a lot of objects/records you'll probably want to be doing the ordering in the database so that you can properly filter them out. This will have a couple advantages in most cases:

1) You can limit the number of items you fetch after sorting them (meaning you don't have to load as many into memory)

2) If you're using indexes properly, it will be very fast

Ordering by multiple rows is straight forward, and explained in the answers to this question: Ruby on Rails: how do I sort with two columns using ActiveRecord?

As far as the actual sorting goes, if you'd like to sort the array in memory the code you gave should work:

a.sort_by{|x| [x.rank, -x.created_at]}

Could you explain what the problem is? This seems like it sorts by the rank, and then by the time, as you specified. Where you looking to sort by another means (such as some score made up by the rank and how recent the item is)?

Edit:

Alright, so by your comment you wish to make a score that mixes rank with how recent the item is. This will be a tricky subject, for several reasons. First off, exactly how you model/score the results will be up to you, and you'll have to tweak it accordingly (so maybe it will be some fraction of Time.now - item.created_at multiplied by the rank, or something. It will basically be something you'll have to play with).

On second thought, it might be better to just use the unix-style timestamp, rather than Time.now - item.created_at, because, unless somebody severely messes with your security, the higher the number, the more recent it will be. (Epoch issues notwithstanding). Plus this might make other things easier later on.

For example, maybe it would be something like:

rank*(item.created_at - 1300000000)/60000000,
which for the current time would result in 0.7453 * rank, and for an item made a day from now would result in: 0.7467 * rank (so a bit higher). Maybe your solution would be something more complex. It's really up to you to figure out an equation that works for your situation.

The other issue is it might make your SQL queries and indexes a bit more complex (though you can index a table based on multiple columns with math (i.e. your scoring system) included; here's an example: http://use-the-index-luke.com/sql/where-clause/obfuscation/math; this may seem a bit trickier for you because how recent it is will constantly be changing, so maybe it should just be a function of the timestamp itself (which increases with time, so recent items will inherently have higher values)).

Basically, your first step will be figuring out exactly what sort of mathematical model best suits your needs. Experiment a bit. After that you'll just have to get things efficiently implemented up in Rails (correct database queries, indexes, etc.)

Ransack sort on .count result of an has_many relation, rails 4

I found out using a counter cache field and sort the field with ransack

Ordering records by number of occurrences in the database

So, doing it with SQL as I pointed out in a comment would be rather complicated and requires good knowledge of SQL. But with a little time in rails c I found a way to do it in Rails!
The result of this query will be Locations not Geochecks, but I don't think it will be a problem for you.

Location.joins(:geochecks).order('COUNT(geochecks.id) DESC').group('location.id')

---

Related Topics