sql_strategies.md

SQL Strategies

When fetching records from the database, CanCanCan generates the SQL for you.

The generated SQL, although correct, might not be performant.

In the history of CanCanCan we had many issues with different versions of the generated SQL and we finally reached to the conclusion that there's no single solutions that fits all the needs.

That's why in the latest versions of CanCanCan, you are given the possibility to customize how the SQL is generated and choose from multiple options.

You can customize the SQL startegy globally with:

# config/initializers/cancancan.rb

CanCan.accessible_by_strategy = :subquery # :left_join is the default

or on a single accessible_by call:

Article.accessible_by(current_ability, strategy: :subquery) # :left_join is, again, the default

or on a group of queries:

CanCan.with_accessible_by_strategy(:subquery) do
  Article.accessible_by(current_ability)
  # ...
end

Here is a complete list of the available strategies, explained by examples.

Given the following permissions:

can :read, Article, mentions: { user: { name: u.name } }

:left_join

Note that in the default strategy, we use the DISTINCT clause which might cause performance issues.

SELECT DISTINCT "articles".*
FROM "articles"
LEFT OUTER JOIN "mentions" ON "mentions"."article_id" = "articles"."id"
LEFT OUTER JOIN "users" ON "users"."id" = "mentions"."user_id"
WHERE "users"."name" = 'pippo'

:subquery

By using the :subquery strategy, the DISTINCT clause can be removed.

SELECT "articles".*
FROM "articles"
WHERE "articles"."id" IN
  (SELECT "articles"."id"
   FROM "articles"
   LEFT OUTER JOIN "mentions" ON "mentions"."article_id" = "articles"."id"
   LEFT OUTER JOIN "users" ON "users"."id" = "legacy_mentions"."user_id"
   WHERE "users"."name" = 'pippo')