Resource#with_role does two queries instead of join

[l0x-c0d3z]

Hi.

Right now I have a Scope resource, and a User class that can have roles on scopes.

If I run something like

Scope.with_role('operator', user)

I get two queries performed :

• the first one gets all the operator roles for the user
• the second one gets the Scopes

SELECT "roles"."id" FROM "roles" INNER JOIN "users_roles" ON "roles"."id" = "users_roles"."role_id" WHERE "users_roles"."user_id" = ? AND "roles"."name" = ?  [["user_id", 1033388936], ["name", "operator"]]
SELECT  "scopes".* FROM "scopes" INNER JOIN "roles" ON "roles".resource_type IN ('Scope') AND
                                    ("roles".resource_id IS NULL OR "roles".resource_id = "scopes"."id") WHERE ("roles".name IN ('project_manager') AND "roles".resource_type IN ('Scope')) AND ("roles"."id" IN (NULL) AND (("roles".resource_id = "scopes"."id") OR ("roles".resource_id IS NULL))) LIMIT ?  [["LIMIT", 11]]

It feels like there's a useless back-and-forth between ruby and the DB going on, and I feel like letting the DB perform a join would be much better, performance wise (esp. when I have a huge amount of roles assigned to some users).

I looked into it, and it appears there's an explicit conversion to array in Rolify::Adapter::ResourceAdapter#in:

roles = user.roles.where(:name => role_names).select("#{quote_table(role_class.table_name)}.#{quote_column(role_class.primary_key)}").to_a
relation.where("#{quote_table(role_class.table_name)}.#{quote_column(role_class.primary_key)} IN (?) AND ((#{quote_table(role_class.table_name)}.resource_id = #{quote_table(relation.table_name)}.#{quote_column(relation.primary_key)}) OR (#{quote_table(role_class.table_name)}.resource_id IS NULL))", roles)

Removing the to_a at the end of the first line does not seem to break anything, and there's now only one query happening:

SELECT  "scopes".* FROM "scopes" INNER JOIN "roles" ON "roles".resource_type IN ('Scope') AND
                                    ("roles".resource_id IS NULL OR "roles".resource_id = "scopes"."id") WHERE ("roles".name IN ('operator') AND "roles".resource_type IN ('Scope')) 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" = 883244766 AND "roles"."name" = 'operator') AND (("roles".resource_id = "scopes"."id") OR ("roles".resource_id IS NULL))) LIMIT ?  [["LIMIT", 11]]

I wonder why that to_a was added ? Is it for caching/preloading reasons ? Or can I safely remove it ?

[thomas-mcdonald]

It should be safe to remove and there has been a PR open to that effect for a long time. Reapplied the commit against master in the linked pull request

[thomas-mcdonald]

Just released version 6.0 with this change. Let me know if it works as expected!