How can I sort with ransack a column that contains count of the associated model

Question

I have two models: InStock and Variant:

class InStock < ApplicationRecord
  has_many :variants
end

and :

class Variant < ApplicationRecord
  belongs_to :in_stock
end

On the in_stocks index page, I have a table with a column: <%= in_stock.variants.count %> I want to use ransack gem to have a sorting column. For doing this, I have this code in the controller:

def index
  @q = InStock.ransack(params[:q])
  @in_stocks = @q.result.includes(:variants)
end

and in the view:

<th><%= sort_link(@q, :variants_count, t('admin.pages.in_stocks.variants'), default_order: :desc) %></th>

But the sorting doesn't work. It doesn't give me any error, just nothing happens. Is there any way to make ransack work with such kind of data? Thanks ahead.

Answer 1

Add this to InStock class

ransacker :variants_count do
  query = '(SELECT COUNT(variants.in_stock_id) FROM variants where variants.in_stock_id = in_stocks.id GROUP BY variants.in_stock_id)'
  Arel.sql(query)
end

Answer 2

You should use Rails counter_cache. Which is what is recommended in this Ransack issue

Which, in your case, adds a variants_count column to the in_stocks table and a counter_cache: true to your belongs_to relationship in Variant, e.g.:

class Variant < ApplicationRecord
  belongs_to :in_stock,
  counter_cache: true
end

Using this method, your sort link doesn't even need to change and remains:

<th>
  <%= sort_link(@q, :variants_count, t('admin.pages.in_stocks.variants'), default_order: :desc) %>
</th>