Calling .count .one? or .many? on with_roles scope in Rails 7 raises StatementInvalid error

[hidde-jan]

It seems rails 7 changed the way it performs counts.

Rails version: 7.0.3.1 Rolify version: 6.0.0

.count works in postgres, but raises in sqlite:

Event.with_roles(%i[admin manager], User.first).count
  Event Count (0.6ms)  SELECT COUNT("events".*) FROM "events" INNER JOIN "roles" ON "roles".resource_type IN ('Event') AND                                                                                                                 
                                    ("roles".resource_id IS NULL OR "roles".resource_id = "events"."id") WHERE ("roles".name IN ('admin','manager') AND "roles".resource_type IN ('Event')) AND ("roles"."id" IN (SELECT "roles"."id" FROM "roles" INNER JOIN "users_roles" ON "roles"."id" = "users_roles"."role_id" WHERE "users_roles"."user_id" = 1 AND "roles"."name" IN ('admin', 'manager')) AND (("roles".resource_id = "events"."id") OR ("roles".resource_id IS NULL)))            
/usr/local/bundle/gems/sqlite3-1.4.4/lib/sqlite3/database.rb:152:in `initialize': SQLite3::SQLException: near "*": syntax error (ActiveRecord::StatementInvalid)                                                                           
/usr/local/bundle/gems/sqlite3-1.4.4/lib/sqlite3/database.rb:152:in `initialize': near "*": syntax error (SQLite3::SQLException)     

.one? raises an invalid statement in both sqlite and postgres:

Event.with_roles(%i[admin manager], User.first).one?
D, [2022-09-11T08:16:30.877300 #503] DEBUG -- :   Event Count (1.9ms)  SELECT COUNT(count_column) FROM (SELECT "events".* AS count_column FROM "events" INNER JOIN "roles" ON "roles".resource_type IN ('Event') AND                                                                                                                 
                                    ("roles".resource_id IS NULL OR "roles".resource_id = "events"."id") WHERE ("roles".name IN ('admin','manager') AND "roles".resource_type IN ('Event')) AND ("roles"."id" IN (SELECT "roles"."id" FROM "roles" INNER JOIN "users_roles" ON "roles"."id" = "users_roles"."role_id" WHERE "users_roles"."user_id" = 1 AND "roles"."name" IN ('admin', 'manager')) AND (("roles".resource_id = "events"."id") OR ("roles".resource_id IS NULL))) LIMIT $1) subquery_for_count  [["LIMIT", 2]]
/opt/render/project/.gems/ruby/3.1.0/gems/activerecord-7.0.3.1/lib/active_record/connection_adapters/postgresql_adapter.rb:768:in `exec_params': PG::UndefinedColumn: ERROR:  column "count_column" does not exist (ActiveRecord::StatementInvalid)                                                                                  
LINE 1: SELECT COUNT(count_column) FROM (SELECT "events".* AS count_...                                                                      
                     ^                                                                                                                       
/opt/render/project/.gems/ruby/3.1.0/gems/activerecord-7.0.3.1/lib/active_record/connection_adapters/postgresql_adapter.rb:768:in `exec_params': ERROR:  column "count_column" does not exist (PG::UndefinedColumn)                                                                                                                  
LINE 1: SELECT COUNT(count_column) FROM (SELECT "events".* AS count_... 

[patrickemuller]

@hidde-jan do you know if this is still happening?

[wheatevo]

I am seeing the same in latest Rails 7 (7.0.8) and Rolify 6.0.1.

Seems like https://github.com/RolifyCommunity/rolify/pull/580 is related to this issue.