Rails sorting associations with Ransack

Question

first time poster. I am trying to sort a table of users using the Ransack gem and Kaminari for pagination. When I use name, id, etc. sorting works but when I try an association with posts_count, sorting breaks and won't work. Note: in the view, 'u.posts.count' work correctly. I have tried custom scopes in the users model, and creating custom objects for the search params but nothing seems to work. I think I am having trouble either in the default scope or the @search object not having the data. Need help!

Here are some relevant snippets:

models/user.rb

  has_many :posts, :dependent => :destroy

models/post.rb

  belongs_to :user 

  default_scope :order => 'post.created_at DESC'

controllers/users_controller.rb

  def index
    @title = "User Index"
    @search = User.search(params[:q]) # Ransack
    @total_users = User.all.count
    # .per(10) is the per page for pagination (Kaminari).
    @users = @search.result.order("updated_at DESC").page(params[:page]).per(10) #This displays the users that are in the search criteria, paginated.
  end

views/users/index.html.erb

  ..
  <%= sort_link @search, :posts_count, "No. of Posts" %> #Sort links at column headers
  .. 
  <% @users.each do |u| %> #Display everything in the table
    <%= u.posts.count %>
  <% end %>

score 5 · Answer 1 · answered Aug 23 '12 at 07:55

5

You can add a scope to your User model:

def self.with_posts
  joins(:posts).group('posts.id').select('users.*, count(posts.id) as posts_count')
end

and use it like this:

@search = User.with_posts.search(params[:q]) # Ransack

then, you can treat posts_count like any other attribute.

answered Aug 23 '12 at 07:55

Mauricio Pasquier Juan

2,265
3
27
39

The problem with your solution that it will return only users that has 1 or more posts... what about all the users that doesn't have posts. Also tried using the posts_count with matches and equals and it didn't work... – Liron Sep 09 '12 at 11:07
If you use an OUTER JOIN instead of INNER JOIN (rails default), it will grab all users, not only those with posts. – Renan Dec 10 '12 at 13:28
1

Right, I think you can do that with `includes(:posts)` instead of `joins(:posts)` – Mauricio Pasquier Juan Dec 10 '12 at 14:13
1

You would have to combine `joins` with `includes` though, to force a join. `includes` can lead to two queries being made – CMW Nov 12 '13 at 14:25
I have [this](http://stackoverflow.com/questions/41984939/ransack-sort-on-count-of-habtm-or-hmt-associated-records) somewhat related question with a bounty. Would you like to take a crack at it? – BrunoF Feb 07 '17 at 18:08

score 2 · Answer 2 · answered Jan 09 '16 at 04:29

I found a solution:

Controller:

def index
    sql = "users.*, (select count(posts.id) from posts\
    where posts.user_id = users.id) as count"
    @search = User.select(sql).search(params[:q])

    if params[:q] && params[:q][:s].include?('count')
      @users = @search.result.order(params[:q][:s])
    else
      @users = @search.result
    end
    .......
end

View:

<th><%= sort_link @search, :count, "posts count" %></th>

Rails sorting associations with Ransack

2 Answers2

Linked