Search code examples
ruby-on-railsrubyarelransack

Rails, Arel, Ransack: sort_by sum of relation

class Document
  has_many :lines
end

class Line
  belongs_to :document
end

sort_by @q, :total

Hello, I need to sort documents by the sum of their lines (amount column in lines).

I thought about a ransacker but how to write it properly?

ransacker :total do
  parent ???
end
Solution

  • You might want to consider computing the total number of lines after_create and after_destroy of any Line object and storing this in a line_count column on documents. This will make querying significantly faster, and lays the data out easy to see. Look into Rails counter caching too, which implements this without you having to think about all the callbacks yourself.

    However you can also do this with a ransacker by using COUNT and GROUP BY. Here's a quick one:

      ransacker :line_count do
    query = "(SELECT COUNT(lines.id) FROM lines WHERE lines.document_id = documents.id GROUP BY lines.document_id)"
    Arel.sql(query)
  end