Skip to content

Instantly share code, notes, and snippets.

@dominikb

dominikb/example.rb

Last active August 13, 2024 15:54
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save dominikb/83adb5465222c6b0b224df7bada48931 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save dominikb/83adb5465222c6b0b224df7bada48931 to your computer and use it in GitHub Desktop.
Download ZIP
CanCanCan illegal table alias
Raw
example.rb
# frozen_string_literal: true
require 'bundler/inline'
gemfile(true) do
source 'https://rubygems.org'
gem 'activerecord', '~> 7.1.0'
gem 'sqlite3', '~> 1.4'
gem 'cancancan', '~> 3.6.0', require: false
# Potential fix mentioned in https://github.com/CanCanCommunity/cancancan/issues/696 but does not work either
# gem 'cancancan', '~> 3.4.0', require: false, git: 'https://github.com/daveallie/cancancan', branch: 'fix/conditions-extractor-aliases'
end
require 'active_record'
require 'cancancan'
ActiveRecord::Base.logger = Logger.new(STDOUT)
ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: ':memory:')
ActiveRecord::Schema.define do
create_table :teams, force: true do |t|
end
create_table :team_accesses, force: true do |t|
t.integer :privileged_team_id
t.integer :accessible_team_id
end
create_table :memberships, force: true do |t|
t.integer :team_id
end
create_table :appreciations, force: true do |t|
t.integer :receiver_id
t.integer :sender_id
t.string :visibility
end
end
class Team < ActiveRecord::Base
has_many :memberships
has_many :privileged_for_team_accesses, class_name: 'TeamAccess', foreign_key: :privileged_team_id,
dependent: :destroy, inverse_of: :privileged_team
has_many :accessible_teams, class_name: 'Team', through: :privileged_for_team_accesses
has_many :accessible_by_team_accesses, class_name: 'TeamAccess', foreign_key: :accessible_team_id,
dependent: :destroy, inverse_of: :accessible_team
has_many :privileged_teams, class_name: 'Team', through: :accessible_by_team_accesses
end
class TeamAccess < ActiveRecord::Base
belongs_to :privileged_team, class_name: 'Team'
belongs_to :accessible_team, class_name: 'Team'
end
class Membership < ActiveRecord::Base
belongs_to :team
has_many :accessible_teams, through: :team
end
class Appreciation < ActiveRecord::Base
belongs_to :receiver, class_name: 'Membership'
enum visibility: {
team: 'team',
receiver: 'receiver',
}, _default: 'team'
end
class WorkingAbility
include ::CanCan::Ability
def initialize
can :read, Appreciation, { receiver: { team: { id: 1 } }, visibility: 'team' }
can :read, Appreciation, { receiver: { team: { privileged_teams: { id: 1 } } }, visibility: 'team' }
end
end
class BrokenAbility
include ::CanCan::Ability
def initialize
can :read, Appreciation, { receiver: { team: { privileged_teams: { id: 1 } } }, visibility: 'team' }
can :read, Appreciation, { receiver: { team: { id: 1 } }, visibility: 'team' }
end
end
working_query = Appreciation.accessible_by(WorkingAbility.new)
broken_query = Appreciation.accessible_by(BrokenAbility.new)
puts(working_query.to_sql)
puts(broken_query.to_sql)
puts(working_query.load)
puts(broken_query.load)
# OUTPUT
# SELECT "appreciations".* FROM "appreciations" WHERE "appreciations"."id" IN (SELECT "appreciations"."id" FROM "appreciations" LEFT OUTER JOIN "memberships" ON "memberships"."id" = "appreciations"."receiver_id" LEFT OUTER JOIN "teams" ON "teams"."id" = "memberships"."team_id" LEFT OUTER JOIN "team_accesses" ON "team_accesses"."accessible_team_id" = "teams"."id" LEFT OUTER JOIN "teams" "privileged_teams_team_accesses" ON "privileged_teams_team_accesses"."id" = "team_accesses"."privileged_team_id" WHERE (("privileged_teams_team_accesses"."id" = 1 AND "appreciations"."visibility" = 'team') OR ("teams"."id" = 1 AND "appreciations"."visibility" = 'team')))
# SELECT "appreciations".* FROM "appreciations" WHERE "appreciations"."id" IN (SELECT "appreciations"."id" FROM "appreciations" LEFT OUTER JOIN "memberships" ON "memberships"."id" = "appreciations"."receiver_id" LEFT OUTER JOIN "teams" ON "teams"."id" = "memberships"."team_id" LEFT OUTER JOIN "team_accesses" ON "team_accesses"."accessible_team_id" = "teams"."id" LEFT OUTER JOIN "teams" "privileged_teams_team_accesses" ON "privileged_teams_team_accesses"."id" = "team_accesses"."privileged_team_id" WHERE (("teams_memberships"."id" = 1 AND "appreciations"."visibility" = 'team') OR ("teams"."id" = 1 AND "appreciations"."visibility" = 'team')))
# D, [2024-08-13T17:46:23.699135 #58715] DEBUG -- : Appreciation Load (0.1ms) SELECT "appreciations".* FROM "appreciations" WHERE "appreciations"."id" IN (SELECT "appreciations"."id" FROM "appreciations" LEFT OUTER JOIN "memberships" ON "memberships"."id" = "appreciations"."receiver_id" LEFT OUTER JOIN "teams" ON "teams"."id" = "memberships"."team_id" LEFT OUTER JOIN "team_accesses" ON "team_accesses"."accessible_team_id" = "teams"."id" LEFT OUTER JOIN "teams" "privileged_teams_team_accesses" ON "privileged_teams_team_accesses"."id" = "team_accesses"."privileged_team_id" WHERE (("privileged_teams_team_accesses"."id" = 1 AND "appreciations"."visibility" = 'team') OR ("teams"."id" = 1 AND "appreciations"."visibility" = 'team')))
# D, [2024-08-13T17:46:23.699327 #58715] DEBUG -- : Appreciation Load (0.1ms) SELECT "appreciations".* FROM "appreciations" WHERE "appreciations"."id" IN (SELECT "appreciations"."id" FROM "appreciations" LEFT OUTER JOIN "memberships" ON "memberships"."id" = "appreciations"."receiver_id" LEFT OUTER JOIN "teams" ON "teams"."id" = "memberships"."team_id" LEFT OUTER JOIN "team_accesses" ON "team_accesses"."accessible_team_id" = "teams"."id" LEFT OUTER JOIN "teams" "privileged_teams_team_accesses" ON "privileged_teams_team_accesses"."id" = "team_accesses"."privileged_team_id" WHERE (("teams_memberships"."id" = 1 AND "appreciations"."visibility" = 'team') OR ("teams"."id" = 1 AND "appreciations"."visibility" = 'team')))
# ~/.asdf/installs/ruby/3.3.4/lib/ruby/gems/3.3.0/gems/sqlite3-1.7.3-arm64-darwin/lib/sqlite3/database.rb:177:in `initialize': SQLite3::SQLException: no such column: teams_memberships.id (ActiveRecord::StatementInvalid)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment