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.

Trajan

I'm not sure you can ranksack sort with scopes. I've always done it with Arel wrappers. – Tetchy

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?

Tetchy

Awesome, do you mind accepting it as the answer please? :) – Tetchy

Sure! In case you are interested, I also have this question you may know the answer to. – Trajan

Answer 2

there is some patch that could be helpful for you to use scope Visit https://gist.github.com/ledermann/4135215

Prytaneum

Thanks for your answer. That patch was written 4 years ago. Ransack currently supports scopes. The thing is, so far I have been unable to write a scope that works as described in this question. – Trajan