ruby on rails - Ransack sort on count of HABTM or HMT associated records

Question

I have a HABTM relationship between the Theme and Quote models. The themes index view displays the count of quotes associated with each theme. I'd like to add a Ransack sort_link on that column, so the themes can be sorted by their count of associated quotes.

I have done this successfully with has_many associations using a counter cache column, but Rails does not support counter cache columns for HABTM associations.

So far, I've got a scope that adds a virtual attribute called quotes_count (by performing a single query, avoiding N+1) to the Theme model:

scope :with_quotes_count, -> do
  joins('LEFT OUTER JOIN quotes_themes on quotes_themes.theme_id = themes.id')
    .select('themes.*, COUNT(quotes_themes.quote_id) as quotes_count')
    .group('themes.id')
end

Seems like I have to convert the above scope into a "Ransacker" using ARel but so far all my attempts have failed.

I'm using Rails 4.2.2, ARel 6.0.4 and PostgreSQL 9.5.4.

Any help will be greatly appreciated.

Answer 1

Given you have your entities queried with the above scope, for example your index query always has:

# controller
@search = Theme.ransack(params[:q])
@themes = @search.result(distinct: true).with_quotes_count

Have a try of:

# model
ransacker :quotes_count_sort do
    Arel.sql('quotes_count')
end

And use the name of the sort in sort_link?