Rails + ActiveAdmin - filtering with ransacker causes a PG :: SyntaxError: ERROR: syntax error at or near the point ","

Question

I have a Ruby on Rails 4.1.4 project using activeadmin 1.0.0.pre from git://github.com/activeadmin/activeadmin , pg 0.17.1 , PostgreSQL 9.3

In the project, I have the following 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 have a task to implement an ActiveAdmin filter on an index page called "by_active_projects", so it should show users who have determined the number of active projects, which means those projects that have archive == false . For instance. if I type “2” in the filter, he should find such accounts that have exactly 2 active projects.

At the moment, I registered the "Account" resource in ActiveAdmin, and I added filter :by_active_projects_eq in admin/account.rb

After that, I defined the scope of having_active_projects for the Account model (models / account.rb):

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

The next step, I defined ransacker for the Account model as follows:

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

There is one account in the development database that has exactly 8 active projects, and filtering works fine for it. But when I tried to filter accounts for 2 active projects, I ran into an error. There are three such accounts in the database, and the error page informed me that the query syntax is incorrect:

 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

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

this thing is generated:

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

I tried to dig out the source code by moving with breakpoints to code snippets in ActiveRecord, ActiveAdmin, and Ransack lib and found out that the relationship is built using Arel :: Nodes: Equality . I am not sure if this is the reason, but I can say for sure:

Library / 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 the Active Record relation for the account;

before calling build_where on line # 565, self.to_sql is

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

after calling and assigning the result to self.where_values ,

self.to_sql equals

 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 this is appreciated! Thanks!

Answer 1

So, I found a solution:

Firstly, I changed my filter in admin/account.rb from

 filter :by_active_projects_eq 

to

 filter :by_active_projects_in, :as => :string 

This approach led to proper 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 with

 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 method of its implementation also caused an incorrect request: for example, such accounts do not exist exactly 5 active projects. In this case

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

returns an "Empty Array", and accessing this array with data ||= nil never returned nil , and this led to SQL looking 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 

note the "accounts"."id" IN () , which causes problems.

After replacing data ||= nil with data.present? ? data : nil data.present? ? data : nil data.present? ? data : nil , if there was no data , nil was assigned to it, and this part in SQL was created differently: "accounts"."id" IN (NULL)