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 %>

Kareem

Answer 1

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.

Lieutenant

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... – Undecided

If you use an OUTER JOIN instead of INNER JOIN (rails default), it will grab all users, not only those with posts. – Dungdungan

Right, I think you can do that with includes(:posts) instead of joins(:posts) – Lieutenant

You would have to combine joins with includes though, to force a join. includes can lead to two queries being made – Interlocutor

I have this somewhat related question with a bounty. Would you like to take a crack at it? – Breakneck

Answer 2

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>

Giggle