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:

1. class User has_one :account

2. class Account belongs_to :user has_many :project_accounts has_many :projects, :through => :project_accounts

3. class Project # the project has a boolean attribute 'archive' has_many :project_accounts

4. 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 Account model (models/account.rb):

scope :having_active_projects, ->(number) { joins(:projects).where("projects.archive = ?", false).having("count(projects) = ?", number).group("accounts.id") }

Next step, I've defined a ransacker for Account model like this:

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

In the development DB there is one account, that has exactly 8 active projects, and filtering works great for it. But when I've tried to filter accounts by 2 active projects, I faced an error. In the DB there are three such accounts, and the error page reported me that the query syntax is wrong:

SELECT COUNT(count_column) FROM (SELECT 1 AS count_column FROM "accounts" WHERE "accounts"."deleted_at" IS NULL AND "accounts"."id" = 'e4d247ec-e64d-4e8a-996a-4d73ccb11257', 'bcb8fa61-4a53-4b45-8954-8fb6ae328365', '93d670b6-7b8f-4c27-91cc-e0f44c137114' LIMIT 30 OFFSET 0) subquery_for_count

As you can see, instead of

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

this thing is being generated:

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

I tried to dig down into the source code, moving with breakpoints on the pieces of code within the ActiveRecord, ActiveAdmin and Ransack lib's and figured out that the relation is being constructed with the help of Arel::Nodes::Equality. I'm not sure this is the reason but one I can say for sure:

lib/active_record/relation/query_methods.rb `

560   def where!(opts = :chain, *rest)
561      if opts == :chain
562        WhereChain.new(self)
563      else
564        references!(PredicateBuilder.references(opts)) if Hash === opts
565        self.where_values += build_where(opts, rest)
566        self
567      end
568    end`

self here is an Active Record relation for Account;

before calling build_where on the row #565, self.to_sql equals to

SELECT "accounts".* FROM "accounts"  WHERE "accounts"."deleted_at" IS NULL  ORDER BY "accounts"."created_at" desc

after calling it and assigning the result to self.where_values,

self.to_sql equals to

SELECT "accounts".* FROM "accounts"  WHERE "accounts"."deleted_at" IS NULL AND "accounts"."id" = 'e4d247ec-e64d-4e8a-996a-4d73ccb11257', 'bcb8fa61-4a53-4b45-8954-8fb6ae328365', '93d670b6-7b8f-4c27-91cc-e0f44c137114'  ORDER BY "accounts"."created_at" desc

Any help or information on the matter is appreciated! Thanks!

Magnanimous

Answer 1

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)

Magnanimous