Rails - ActiveAdmin create relationship count column sortable

Question

I am working on ActiveAdmin and I am trying to display a column name Active Jobs with a count

There are three models involved in getting this count

 1. Staffroom 
 2. StaffroomPage
 3. Job



class StaffroomPage < ActiveRecord::Base
  belongs_to :staffroom

class Staffroom < ActiveRecord::Base
  has_one :staffroom_page
  has_many :jobs

class Job < ActiveRecord::Base
  belongs_to :staffroom

Now inside the admin/staffroom_pages.rb I am able to display the column and the count

column :active_jobs, sortable: 'active_jobs' do |staffroom_page|
  staffroom_page.staffroom.jobs.where(:state=> 'active').count
end

However I am not able to sort the data via active_jobs and I see the following error

PG::UndefinedColumn: ERROR: column "active_jobs" does not exist

So the question is how to make the sort to work for column active_jobs, please note that this is not the only sortable column on page, almost all columns are sortable and this is the only one not working.

Michael Gorman · Answer 1 · 2017-06-05T15:20:42.507

1

From this post on the activeadmin github a possible workaround to this problem would be

index do
  column :active_jobs, sortable: 'active_jobs' do |staffroom_page|
    staffroom_page.staffroom.jobs.where(:state=> 'active').count
  end
end

controller do


def scoped_collection
  super.joins(
    %(LEFT JOIN "jobs" ON "staffroom_pages"."staffroom_id" = "jobs"."staffroom_id"
        AND "jobs"."state" = 'active'))
      .select('staffroom_pages.*, COUNT(jobs.id) as active_jobs')
      .group('staffroom_pages.id')
  end
end

edited Jun 05 '17 at 15:20

answered Jun 05 '17 at 13:39

Michael Gorman

1,077
7
13

thanks for having a look, this leads to the error PG::UndefinedTable: ERROR: missing FROM-clause entry for table "jobs" LINE 1: ...staffrooms" ON "staffroom_pages"."staffroom_id" = "jobs"."st... – Saadia Jun 05 '17 at 14:57
my bad, I had originally thought to join through the staffroom table, but then I realized since both tables have the same foreign key you could skip that join, but I forgot to change what table I was joining to. Edited – Michael Gorman Jun 05 '17 at 15:05
now it seems to complain about PG::UndefinedColumn: ERROR: column "active" does not exist LINE 2: AND "jobs"."state" = "active" WHERE "staffroom_pages... – Saadia Jun 05 '17 at 15:12

score 0 · Answer 2 · answered Jun 09 '17 at 17:46

0

Did you look at the documentation on custom sorting?

answered Jun 09 '17 at 17:46

Piers C

2,880
1
23
29

Rails - ActiveAdmin create relationship count column sortable

2 Answers2

Linked