CODEWITHSUNDEEP
mysqlsqlruby-on-railsactiverecordgroup-by
vikas95prasad
vikas95prasad

Reputation: 1332

How to add limit in group by query in rails?

I want to fetch top 10 voters for a comment and add group by ?

Sample response: 

1 => [1,2,3,4,5,6,7,8,9,10],
2 => [1,2,3,4]}

It should not exceed 10 voters.

  create_table "votes", force: :cascade do |t|
    t.integer  "user_id",      limit: 4,   null: false
    t.integer  "votable_id",   limit: 4,   null: false
    t.string   "votable_type", limit: 191, null: false
    t.integer  "weight",       limit: 4
  end

  create_table "comments", force: :cascade do |t|
    t.text     "message",          limit: 16777215,   null: false
    t.string   "type",             limit: 255
    t.integer  "commentable_id",   limit: 4
    t.string   "commentable_type", limit: 191
    t.integer  "user_id",          limit: 4,          null: false
  end

This is my query which returns all the voters. Instead I need to return top 10 voters for each comment.

Vote.where(votable_id: @comments_ids, votable_type: 'Comment').select(:votable_id, :user_id).group_by(&:votable_id)

Upvotes: 3

Views: 2074

Answers (4)

Ezenwa
Ezenwa

Reputation: 80

Can't think of an active record or SQL way of doing this. But below is a pure Ruby solution: For ruby 2.4 and above, you can use Hash#transform_values on the group_by hash result like so (continuing from your query):

votes = Vote.where(votable_id: @comments_ids, votable_type: 
'Comment').select(:votable_id, :user_id).group_by(&:votable_id)

top_voters = votes.transform_values do |val|
  voters = val.map(&:user_id)
  freq = voters.reduce(Hash.new(0)) {|h, v| h[v] += 1; h }
  sorted_votes = voters.uniq.sort_by {|elem| -freq[elem] }
  sorted_votes.take(10)
end

Upvotes: -1

demir
demir

Reputation: 4709

If the data is not too big, you can do it like this:

Vote.where(votable_id: @comments_ids, votable_type: 'Comment')
    .select(:votable_id, :user_id)
    .group_by(&:votable_id)
    .transform_values { |v| v.take(10) }

Upvotes: 1

Int'l Man Of Coding Mystery
Int'l Man Of Coding Mystery

Reputation: 1722

table = Vote.arel_table

Vote.where(votable_id: @comments_ids, votable_type: 'Comment')
    .select(:votable_id, :user_id)
    .group(:votable_id, :user_id, :id)
    .order('table[:votable_id].count.desc')
    .limit(10)

This should give you a list of the top ten votes. Using vanilla ruby with the #group_by will take a very long time if the collection is large. Using Arel will avoid any breaking changes in Rails 6.0 where raw sql will not be permitted in queries. I would previously use .order('COUNT(votable_id) DESC') but that throws errors and will be disallowed in Rails 6

Upvotes: 0

Ranzit
Ranzit

Reputation: 1347

Something like this ?

Vote.group(:user_id).limit(10)

Upvotes: 2

Related Questions