Rails+ActiveAdmin - filtering with ransacker throws an error PG::SyntaxError: ERROR: syntax error at or near ","

Question

I have a project on Ruby on Rails 4.1.4, using `activeadmin 1.0.0.pre from git://github.com/activeadmin/activeadmin`, `pg 0.17.1`, PostgreSQL 9.3 In the project I have these models: `class User has_one :account` `class Account belongs_to :user has_many :project_accounts has_many :projects, :through => :project_accounts` `class Project # the project has a boolean attribute 'archive' has_many :project_accounts` `class ProjectAccount belongs_to :account belongs_to :project` I've got a task to implement an ActiveAdmin filter on the index page, called "by_active_projects", so it has to show the users that have defined number of active projects, that means such projects that have `archive == false`. E.g. if I type '2' in the filter, it has to find such accounts that have exactly 2 active projects. For now I have registered "Account" resource in ActiveAdmin, and within `admin/account.rb` i've added `filter :by_active_projects_eq` After that I've defined a scope `having_active_projects` for A…

Accepted Answer

So I found the solution:

First, I've changed my filter in `admin/account.rb` from

```
filter :by_active_projects_eq
```

to

```
filter :by_active_projects_in,
         :as => :string
```

This approach resulted in correct SQL generation,

```
"accounts"."id" IN ('e4d247ec-e64d-4e8a-996a-4d73ccb11257', 'bcb8fa61-4a53-4b45-8954-8fb6ae328365', '93d670b6-7b8f-4c27-91cc-e0f44c137114')
```

After that I also had to change my ransacker from

```
ransacker :by_active_projects, formatter: proc{ |v|
    data = Account.having_active_projects(v).map(&:id)
    data ||= nil
  } do |parent|
    parent.table[:id]
  end
```

to

```
ransacker :by_active_projects, formatter: proc{ |v|
    data = Account.having_active_projects(v).pluck(:id)
    data.present? ? data : nil
  } do |parent|
    parent.table[:id]
  end
```

because the way it was implemented also caused incorrect query: for example there are no such accounts that have exactly 5 active projects. In that case

```
data = Account.having_active_projects(v).pluck(:id)
```

returned "Empty Array", and dealing with this Array with `data ||= nil` never actually returned `nil`, and that resulted in SQL like this:

```
SELECT COUNT(count_column) FROM (SELECT  1 AS count_column FROM "accounts"  WHERE "accounts"."deleted_at" IS NULL AND "accounts"."id" IN () LIMIT 30 OFFSET 0) subquery_for_count
```

notice the `"accounts"."id" IN ()` part, that was causing trouble.

After replacing `data ||= nil` with `data.present? ? data : nil`, if `data` was not present it was assigned a `nil`, and that part in SQL was generated corectly: `"accounts"."id" IN (NULL)`

Rails+ActiveAdmin - filtering with ransacker throws an error PG::SyntaxError: ERROR: syntax error at or near ","

Solution

Problem