How to implement a sortable column on associated record's count

[masa-ekohe]

trafficstars

Is there any way to do this? I can't implement the counter cache because the where clause is used when retrieving a count.

I saw similar issues but no answer was given: https://github.com/jbox-web/ajax-datatables-rails/issues/200

Any feedback or example is appreciated.

[MoKuH]

Hi @masa-ekohe

I was able to make it works First I had to add all tables into a select Vehicle.joins([:brand,:seller]).select("brands.*, sellers.*, vehicles.*").references(:brands,:sellers).distinct

Then I created a new method which I use to count the records :
def get_raw_records_count Vehicle.joins([:brand,:seller]).references(:brand,:seller).distinct end

then I monkey patched these 2 following methods to use my new get_raw_records_count method :

def records_total_count get_raw_records_count.count(:all) end

def records_filtered_count filter_records(get_raw_records_count).count(:all) end

Definitely not perfect, but it's all I have !

[masa-ekohe]

Hi @MoKuH,

Thank you so much for a solution. Will definitely try that later!

My solution is super ugly but I think I will just put it here as it might help others in the future:

class Comment
    has_many :reports, class_name: 'CommentReport'
end

class CommentReport
  belongs_to :comment
end

class CommentDatatable < AjaxDatatablesRails::ActiveRecord

  def get_raw_records
    Comment.left_joins(:reports).group('comments.id')
  end

  # https://github.com/jbox-web/ajax-datatables-rails/issues/293
  def records_total_count
    fetch_records.unscope(:group).count(:all)
  end

  # https://github.com/jbox-web/ajax-datatables-rails/issues/293
  def records_filtered_count
    filter_records(fetch_records).unscope(:group).count(:all)
  end

  # https://github.com/jbox-web/ajax-datatables-rails/issues/228
  def sort_records(records)
    sort_by = datatable.orders.inject([]) do |queries, order|
      column = order.column

      if column && column.orderable?
        if column.sort_query.include? 'comment_reports.count'
          if order.query(column.sort_query).include? 'ASC'
            queries << 'COUNT(distinct(comment_reports.id)) ASC'
          else
            queries << 'COUNT(distinct(comment_reports.id)) DESC'
          end
        else
          queries << order.query(column.sort_query)
        end
      end

      queries
    end

    records.order(Arel.sql(sort_by.join(', ')))
  end
end

[arlenmendes]

I did it this way:

def get_raw_records
    PlayList.joins(:podcasts).select('play_lists.*, count(podcasts.id) as count_podcasts').group('play_lists.id')
  end

And added count_podcasts in sortable column. I hope its helps.