cancancan
cancancan copied to clipboard
Published
20 hours ago •
CanCanCommunity
CanCanCommunity
Working with large collections
Steps to reproduce
The following ability conditions differ in behaviour:
class Ability
include CanCan::Ability
def initialize(user)
can :manage, Customer, id: user.customer_ids
can :manage, Customer, id: user.customers.select(:id)
end
end
https://gist.github.com/dmke/937a7d0323a6b5907c178da103df850f
ruby main.rb --name test_via_ids
-- create_table(:customers, {:force=>true})
D, [2021-08-23T19:27:19.549694 #12265] DEBUG -- : (0.5ms) SELECT sqlite_version(*)
D, [2021-08-23T19:27:19.549991 #12265] DEBUG -- : (0.0ms) DROP TABLE IF EXISTS "customers"
D, [2021-08-23T19:27:19.550245 #12265] DEBUG -- : (0.1ms) CREATE TABLE "customers" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" varchar)
-> 0.0042s
-- create_table(:users, {:force=>true})
D, [2021-08-23T19:27:19.550440 #12265] DEBUG -- : (0.0ms) DROP TABLE IF EXISTS "users"
D, [2021-08-23T19:27:19.550602 #12265] DEBUG -- : (0.1ms) CREATE TABLE "users" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" varchar)
-> 0.0003s
-- create_join_table(:customers, :users, {:force=>true})
D, [2021-08-23T19:27:19.550869 #12265] DEBUG -- : (0.0ms) DROP TABLE IF EXISTS "customers_users"
D, [2021-08-23T19:27:19.551037 #12265] DEBUG -- : (0.1ms) CREATE TABLE "customers_users" ("customer_id" integer NOT NULL, "user_id" integer NOT NULL)
-> 0.0004s
D, [2021-08-23T19:27:19.568794 #12265] DEBUG -- : (0.1ms) CREATE TABLE "ar_internal_metadata" ("key" varchar NOT NULL PRIMARY KEY, "value" varchar, "created_at" datetime(6) NOT NULL, "updated_at" datetime(6) NOT NULL)
D, [2021-08-23T19:27:19.574234 #12265] DEBUG -- : ActiveRecord::InternalMetadata Load (0.5ms) SELECT "ar_internal_metadata".* FROM "ar_internal_metadata" WHERE "ar_internal_metadata"."key" = ? LIMIT ? [["key", "environment"], ["LIMIT", 1]]
D, [2021-08-23T19:27:19.577575 #12265] DEBUG -- : TRANSACTION (0.0ms) begin transaction
D, [2021-08-23T19:27:19.577766 #12265] DEBUG -- : ActiveRecord::InternalMetadata Create (0.1ms) INSERT INTO "ar_internal_metadata" ("key", "value", "created_at", "updated_at") VALUES (?, ?, ?, ?) [["key", "environment"], ["value", "development"], ["created_at", "2021-08-23 17:27:19.577235"], ["updated_at", "2021-08-23 17:27:19.577235"]]
D, [2021-08-23T19:27:19.577901 #12265] DEBUG -- : TRANSACTION (0.0ms) commit transaction
Run options: -n test_via_ids --seed 48407
# Running:
D, [2021-08-23T19:27:19.598178 #12265] DEBUG -- : TRANSACTION (0.0ms) begin transaction
D, [2021-08-23T19:27:19.598352 #12265] DEBUG -- : Customer Create (0.1ms) INSERT INTO "customers" ("name") VALUES (?) [["name", "UPS"]]
D, [2021-08-23T19:27:19.598477 #12265] DEBUG -- : TRANSACTION (0.0ms) commit transaction
D, [2021-08-23T19:27:19.598727 #12265] DEBUG -- : TRANSACTION (0.0ms) begin transaction
D, [2021-08-23T19:27:19.598880 #12265] DEBUG -- : Customer Create (0.0ms) INSERT INTO "customers" ("name") VALUES (?) [["name", "DHL"]]
D, [2021-08-23T19:27:19.598975 #12265] DEBUG -- : TRANSACTION (0.0ms) commit transaction
D, [2021-08-23T19:27:19.599221 #12265] DEBUG -- : TRANSACTION (0.0ms) begin transaction
D, [2021-08-23T19:27:19.599327 #12265] DEBUG -- : Customer Create (0.0ms) INSERT INTO "customers" ("name") VALUES (?) [["name", "FedEx"]]
D, [2021-08-23T19:27:19.599667 #12265] DEBUG -- : TRANSACTION (0.0ms) commit transaction
D, [2021-08-23T19:27:19.599974 #12265] DEBUG -- : TRANSACTION (0.0ms) begin transaction
D, [2021-08-23T19:27:19.600081 #12265] DEBUG -- : Customer Create (0.0ms) INSERT INTO "customers" ("name") VALUES (?) [["name", "Other"]]
D, [2021-08-23T19:27:19.600171 #12265] DEBUG -- : TRANSACTION (0.0ms) commit transaction
D, [2021-08-23T19:27:19.601971 #12265] DEBUG -- : TRANSACTION (0.0ms) begin transaction
D, [2021-08-23T19:27:19.602101 #12265] DEBUG -- : User Create (0.1ms) INSERT INTO "users" ("name") VALUES (?) [["name", "manager"]]
D, [2021-08-23T19:27:19.602223 #12265] DEBUG -- : TRANSACTION (0.0ms) commit transaction
D, [2021-08-23T19:27:19.609885 #12265] DEBUG -- : TRANSACTION (0.0ms) begin transaction
D, [2021-08-23T19:27:19.613449 #12265] DEBUG -- : User::HABTM_Customers Create (0.1ms) INSERT INTO "customers_users" ("customer_id", "user_id") VALUES (?, ?) [["customer_id", 1], ["user_id", 1]]
D, [2021-08-23T19:27:19.614338 #12265] DEBUG -- : User::HABTM_Customers Create (0.0ms) INSERT INTO "customers_users" ("customer_id", "user_id") VALUES (?, ?) [["customer_id", 2], ["user_id", 1]]
D, [2021-08-23T19:27:19.614933 #12265] DEBUG -- : User::HABTM_Customers Create (0.1ms) INSERT INTO "customers_users" ("customer_id", "user_id") VALUES (?, ?) [["customer_id", 3], ["user_id", 1]]
D, [2021-08-23T19:27:19.615074 #12265] DEBUG -- : TRANSACTION (0.0ms) commit transaction
D, [2021-08-23T19:27:19.615933 #12265] DEBUG -- : Customer Load (0.1ms) SELECT "customers".* FROM "customers" INNER JOIN "customers_users" ON "customers"."id" = "customers_users"."customer_id" WHERE "customers_users"."user_id" = ? [["user_id", 1]]
D, [2021-08-23T19:27:19.616830 #12265] DEBUG -- : (0.1ms) SELECT COUNT(*) FROM "customers" WHERE "customers"."id" IN (?, ?, ?) [["id", 1], ["id", 2], ["id", 3]]
.
Finished in 0.021713s, 46.0559 runs/s, 138.1676 assertions/s.
1 runs, 3 assertions, 0 failures, 0 errors, 0 skips
ruby main.rb --name test_via_select
-- create_table(:customers, {:force=>true})
D, [2021-08-23T19:27:56.680072 #12577] DEBUG -- : (0.6ms) SELECT sqlite_version(*)
D, [2021-08-23T19:27:56.680494 #12577] DEBUG -- : (0.0ms) DROP TABLE IF EXISTS "customers"
D, [2021-08-23T19:27:56.680750 #12577] DEBUG -- : (0.1ms) CREATE TABLE "customers" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" varchar)
-> 0.0042s
-- create_table(:users, {:force=>true})
D, [2021-08-23T19:27:56.680947 #12577] DEBUG -- : (0.0ms) DROP TABLE IF EXISTS "users"
D, [2021-08-23T19:27:56.681106 #12577] DEBUG -- : (0.1ms) CREATE TABLE "users" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" varchar)
-> 0.0003s
-- create_join_table(:customers, :users, {:force=>true})
D, [2021-08-23T19:27:56.681361 #12577] DEBUG -- : (0.0ms) DROP TABLE IF EXISTS "customers_users"
D, [2021-08-23T19:27:56.681527 #12577] DEBUG -- : (0.1ms) CREATE TABLE "customers_users" ("customer_id" integer NOT NULL, "user_id" integer NOT NULL)
-> 0.0004s
D, [2021-08-23T19:27:56.698463 #12577] DEBUG -- : (0.1ms) CREATE TABLE "ar_internal_metadata" ("key" varchar NOT NULL PRIMARY KEY, "value" varchar, "created_at" datetime(6) NOT NULL, "updated_at" datetime(6) NOT NULL)
D, [2021-08-23T19:27:56.704510 #12577] DEBUG -- : ActiveRecord::InternalMetadata Load (0.6ms) SELECT "ar_internal_metadata".* FROM "ar_internal_metadata" WHERE "ar_internal_metadata"."key" = ? LIMIT ? [["key", "environment"], ["LIMIT", 1]]
D, [2021-08-23T19:27:56.707978 #12577] DEBUG -- : TRANSACTION (0.0ms) begin transaction
D, [2021-08-23T19:27:56.708180 #12577] DEBUG -- : ActiveRecord::InternalMetadata Create (0.1ms) INSERT INTO "ar_internal_metadata" ("key", "value", "created_at", "updated_at") VALUES (?, ?, ?, ?) [["key", "environment"], ["value", "development"], ["created_at", "2021-08-23 17:27:56.707614"], ["updated_at", "2021-08-23 17:27:56.707614"]]
D, [2021-08-23T19:27:56.708315 #12577] DEBUG -- : TRANSACTION (0.0ms) commit transaction
Run options: -n test_via_select --seed 30378
# Running:
D, [2021-08-23T19:27:56.728509 #12577] DEBUG -- : TRANSACTION (0.0ms) begin transaction
D, [2021-08-23T19:27:56.728686 #12577] DEBUG -- : Customer Create (0.1ms) INSERT INTO "customers" ("name") VALUES (?) [["name", "UPS"]]
D, [2021-08-23T19:27:56.728810 #12577] DEBUG -- : TRANSACTION (0.0ms) commit transaction
D, [2021-08-23T19:27:56.729062 #12577] DEBUG -- : TRANSACTION (0.0ms) begin transaction
D, [2021-08-23T19:27:56.729168 #12577] DEBUG -- : Customer Create (0.0ms) INSERT INTO "customers" ("name") VALUES (?) [["name", "DHL"]]
D, [2021-08-23T19:27:56.729282 #12577] DEBUG -- : TRANSACTION (0.0ms) commit transaction
D, [2021-08-23T19:27:56.729509 #12577] DEBUG -- : TRANSACTION (0.0ms) begin transaction
D, [2021-08-23T19:27:56.729613 #12577] DEBUG -- : Customer Create (0.0ms) INSERT INTO "customers" ("name") VALUES (?) [["name", "FedEx"]]
D, [2021-08-23T19:27:56.729703 #12577] DEBUG -- : TRANSACTION (0.0ms) commit transaction
D, [2021-08-23T19:27:56.729928 #12577] DEBUG -- : TRANSACTION (0.0ms) begin transaction
D, [2021-08-23T19:27:56.730032 #12577] DEBUG -- : Customer Create (0.0ms) INSERT INTO "customers" ("name") VALUES (?) [["name", "Other"]]
D, [2021-08-23T19:27:56.730121 #12577] DEBUG -- : TRANSACTION (0.0ms) commit transaction
D, [2021-08-23T19:27:56.732036 #12577] DEBUG -- : TRANSACTION (0.0ms) begin transaction
D, [2021-08-23T19:27:56.732146 #12577] DEBUG -- : User Create (0.0ms) INSERT INTO "users" ("name") VALUES (?) [["name", "manager"]]
D, [2021-08-23T19:27:56.732268 #12577] DEBUG -- : TRANSACTION (0.0ms) commit transaction
D, [2021-08-23T19:27:56.740462 #12577] DEBUG -- : TRANSACTION (0.1ms) begin transaction
D, [2021-08-23T19:27:56.744168 #12577] DEBUG -- : User::HABTM_Customers Create (0.1ms) INSERT INTO "customers_users" ("customer_id", "user_id") VALUES (?, ?) [["customer_id", 1], ["user_id", 1]]
D, [2021-08-23T19:27:56.744737 #12577] DEBUG -- : User::HABTM_Customers Create (0.0ms) INSERT INTO "customers_users" ("customer_id", "user_id") VALUES (?, ?) [["customer_id", 2], ["user_id", 1]]
D, [2021-08-23T19:27:56.745251 #12577] DEBUG -- : User::HABTM_Customers Create (0.0ms) INSERT INTO "customers_users" ("customer_id", "user_id") VALUES (?, ?) [["customer_id", 3], ["user_id", 1]]
D, [2021-08-23T19:27:56.745390 #12577] DEBUG -- : TRANSACTION (0.0ms) commit transaction
D, [2021-08-23T19:27:56.746408 #12577] DEBUG -- : (0.1ms) SELECT COUNT(*) FROM "customers" WHERE "customers"."id" IN (SELECT "customers"."id" FROM "customers" INNER JOIN "customers_users" ON "customers"."id" = "customers_users"."customer_id" WHERE "customers_users"."user_id" = ?) [["user_id", 1]]
F
Failure:
BugTest#test_via_select [main.rb:92]:
Expected false to be truthy.
rails test main.rb:82
Finished in 0.020929s, 47.7799 runs/s, 95.5597 assertions/s.
1 runs, 2 assertions, 1 failures, 0 errors, 0 skips
Expected behaviour
The definitions should behave the same.
Actual behaviour
It matters whether I use an array of IDs or a sub-query:
ability.can? :read, Customer.first
With can :manage, Customer, user.customer_ids this works, but can :manage, Customer, user.customers.select(:id) it fails (see test cases in the Gist above).
Related issue
The user.customer_ids variant generates a potentially huge query (I've seen a prepared statement in production with 17k placeholders).
Compare the SQL query of Customer.accessible_by(ability).count for:
-
can :manage, Customer, user.customer_ids:-- user.customer_ids SELECT "customers".* FROM "customers" INNER JOIN "customers_users" ON "customers"."id" = "customers_users"."customer_id" WHERE "customers_users"."user_id" = ? -- Customer.accessible_by SELECT COUNT(*) FROM "customers" WHERE "customers"."id" IN (?, ?, ?) -
can :manage, Customer, user.customers.select(:id):-- Customer.accessible_by SELECT COUNT(*) FROM "customers" WHERE "customers"."id" IN ( -- user.customers.select(:id) SELECT "customers"."id" FROM "customers" INNER JOIN "customers_users" ON "customers"."id" = "customers_users"."customer_id" WHERE "customers_users"."user_id" = ? )
System configuration
Rails version: 6.1
Ruby version: 2.7
CanCanCan version: 3.3.0
I'd refactor this to query the customers based on either a scope or, as this is a simple association here, using the foreign keys:
class Customer
belongs_to :user, inverse_of: :customers
scope :for_user, scope :for_user, ->(user) { where(user_id: user.id) }
end
class Ability
include CanCan::Ability
def initialize(user)
can :manage, Customer, user_id: user.id
can :manage, Customer.for_user(user)
end
end
