How to use 'nested_model.count' for a searchable column

[masa-ekohe]

I have models Resource and ResourceReport. The relation is Resource 1 : n ResourceReports.

Now in my Datatables, I want to have a sortable column report_count which is a number of Reports related to a Resource but if I click a sort button on the column, I get the error below. How could I solve this?

ActiveRecord::StatementInvalid (PG::GroupingError: ERROR:  column "resources.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...LECT  DISTINCT resource_reports.count AS alias_0, "public"."...
                                                             ^
: SELECT  DISTINCT resource_reports.count AS alias_0, "public"."resources"."id" FROM "public"."resources" LEFT OUTER JOIN "resource_reports" ON "resource_reports"."resource_id" = "public"."resources"."id" ORDER BY resource_reports.count ASC LIMIT $2 OFFSET $3):

app/datatables/resource_datatable.rb:17:in `map'
app/datatables/resource_datatable.rb:17:in `data'

Here is the code:

class ResourceReport < ApplicationRecord
  belongs_to :resource
end

class Resource
  has_many :reports, class_name: 'ResourceReport'
end

# app/datatables/resource_datatable.rb
class ResourceDatatable < AjaxDatatablesRails::ActiveRecord
  def initialize(params, opts = {})
    @view = opts[:view_context]
    super
  end

  def view_columns
    @view_columns ||= {
      id: { source: 'Resource.id', searchable: false },
      title: { source: 'Resource.name' },
      report_count: { source: 'ResourceReport.count', searchable: false },
      created_at: { source: 'Resource.created_at', searchable: false },
    }
  end

  def data
    records.map do |record|
      {
          id: record.id,
          title: record.name,
          report_count: record.reports.count,
          created_at: record.created_at,
          DT_RowId: record.id
      }
    end
  end

  def get_raw_records
    Resource.includes(:reports).references(:reports)
  end
end