Sorting using ransack, through association and aggregation method

Question

I have such connections in models

class User < ApplicationRecord
  has_many :support_user_orders, dependent: :destroy
end

class SupportUserOrder < ApplicationRecord
  belongs_to :user
end

I have a table that shows some information.And I need to be able to sort by 'Charged Amount' as I do it by id, but it doesn't work if I do it like with id.

def index
  user_supports = TestNameQuery.call(period: params[:period])
  @ransack = user_supports.ransack(params[:q])
  @user_supports = @ransack.result
end

<tr>
  <th><%= sort_link @ransack, :id %></th>
  <th class="text-nowrap">Charged Amount</th>
</tr>

<tr class="bg-white">
  <td><%= link_to user_support.id, system_user_path(user_support) %></td>
  <td><%= user_support.support_user_orders.sum(:charged_amount) %></td>
</tr>

i tried like this but it doesn't work

@user_supports = @ransack.result(distinct: true)

<th><%= sort_link @ransack, :charged_amount_sum, 'Charged Amount' %></th>

Answer 1

This work for me.

ransacker :total_charged_amount do
    Arel.sql("(SELECT SUM(charged_amount) FROM support_user_orders WHERE support_user_orders.user_id = users.id)")
end