cancan generates wrong table names for sql

[matiss]

After updating to 1.6.8 I discovered a bug which wasn't in 1.6.7

Cancan generates wrong table names for sql when using associations.

Ability

can :manage, Video, uploader: { client_id: user.client_id }

Video model

class Video < ActiveRecord::Base
  belongs_to :uploader, class_name: 'User', foreign_key: :uploader_id
end

And the error I'm getting is:

Mysql2::Error: Unknown column 'uploader.client_id' in 'where clause': SELECT  `videos`.* FROM `videos` INNER JOIN `users` ON `users`.`id` = `videos`.`uploader_id` WHERE `uploader`.`client_id` = 7 ORDER BY created_at DESC LIMIT 5

Table name 'uploader' should be 'users'.

Thanks.

[andhapp]

@matiss: Hey, when exactly do you get this error.

Mysql2::Error: Unknown column 'uploader.client_id' in 'where clause': SELECT  `videos`.* FROM `videos` INNER JOIN `users` ON `users`.`id` = `videos`.`uploader_id` WHERE `uploader`.`client_id` = 7 ORDER BY created_at DESC LIMIT 5

I mean what code can I can execute to replicate it my side. Thanks

[matiss]

@andhapp Hey, I had to test it again and the code who is causing the error is following:

In controllers

class Cms::DashboardController < Cms::ApplicationController
  authorize_resource :class => false

  def index
    @videos = Video.accessible_by(current_ability).order('created_at DESC').limit(5)
  end

end

and this one too is causing the same error

class Cms::VideosController < Cms::ApplicationController
  load_and_authorize_resource :video

  def index
    @videos = Video.filter(params[:search], params[:page], :title, current_ability)
  end

end

Filter module (Included in Video model)

module SimpleFilter
  def self.included(base)
    base.extend(self)
  end

  def filter(search, page, column = 'name', ability=nil, order='id DESC')
    if search && search != ''
      if column.is_a? Array
        conditions = ''
        column.each_with_index do |col, index|
          if index == 0
            conditions += "#{col} LIKE '%#{search}%'"
          else
            conditions += " OR #{col} LIKE '%#{search}%"
          end
        end
      else
        conditions = ["#{column} LIKE ?", "%#{search}%"]
      end

      results = self.where(conditions).order(order)
      results.accessible_by(ability) if ability
      return Kaminari.paginate_array(results).page(page)
    else
      return ability ? order(order).page(page).accessible_by(ability) : order(order).page(page)
    end
  end
end

If you need anything else feel free to ask.

Thanks.

[timgremore]

I've been dealing with this issue as well and here is another example, if it helps:

module Courses
    class Course < ActiveRecord::Base
        has_many :enrollments, as: :enrollable
    end
end

module Courses
    class Enrollment < ActiveRecord::Base
        belongs_to :enrollable, polymorphic: true
        belongs_to :user
    end
end

module Courses
    class Ability
        include CanCan::Ability

        def initialize(user = nil)
            user ||= User.new

            can :read, Courses::Course, enrollments: { user_id: user.id }
        end
    end
end

Trying the following:

Courses::Course.accessible_by(Courses::Ability.new(User.first)).count.to_sql

Results in the following:

SELECT COUNT(*) FROM "courses_courses" INNER JOIN "courses_enrollments" ON "courses_enrollments"."enrollable_id" = "courses_courses"."id" AND "courses_enrollments"."type" IN ('Courses::Enrollment') AND "courses_enrollments"."enrollable_type" = 'Courses::Course' WHERE "enrollments"."user_id" = 3

Looks good except the WHERE clause is using the wrong table name: should be "courses_enrollments.user_id", not "enrollments.user_id"

[andhapp]

@matiss: Did you also update rails whilst updating CanCan? Because CanCan doesn't really do anything intelligent with the belongs_to definitions. It just hands it to ActiveRecord (Arel) for generating the sql and all. So, this might be a a bug outside of CanCan.

If you could let us know the rest of your environment changes, I'll try and debug it.

@timgremore: Your issue could be a little different, although related, because of namespaced model as well. Was this code working in 1.6.7? Did upgrade to 1.6.8 break this code? What about rails, did you upgrade that as well? Thanks.

[matiss]

@andhapp So I was running ruby-1.9.3-p125 [ x86_64 ] and Rails 3.2.6, and error appeared when I ran bundle update. I changed thinking_sphinx version and that's all. I end up using cancan 1.6.7, it's working fine.

[timgremore]

@matiss So my issue appears to be fixed by specifying the class name of my has_many association:

    module Courses
        class Course < ActiveRecord::Base
            has_many :enrollments, as: :enrollable, class_name: "Courses::Enrollment"
        end
    end

Thanks for your help - got me on the right track!

[andhapp]

@matiss: Can you please post the user and video and the ability class? I just need to see the associations between them and how is client_id related to a user? Thanks.

[matiss]

@andhapp

ability.rb

class Ability
  include CanCan::Ability

  def initialize(user)
    # Always should be a User class
    user ||= User.new

    # Define abilities
    if user.role?(:admin)
      can :manage, :all
      cannot :delete, Filter, default: true
      cannot :delete, User, id: user.id
    else
      # Default abilities
      cannot :manage, :all

      # Custom abilities for each role
      if user.role?(:client_admin)
        # #can :manage, [Client, Group, Homepage, Video, Playlist, Participant]
        can :manage, [Homepage, Playlist, Participant, :dashboard]

        can :read, Client, id: user.client_id

        can :manage, Video, uploader: { client_id: user.client_id }
        can :create, Video
        can :manage, Participant

        can :manage, User, client_id: user.client_id
        can :create, User
        cannot :delete, User, id: user.id

        can :manage, Group, client_id: user.client_id
        can :create, Group

        can :manage, Homepage, client_id: user.client_id
        can :manage, Homepage, channel: { groups: { id: user.group_ids } }

        can :read, Channel, clients: { id: user.client_id }

        # Playlist collections/folders
        can :manage, Collection, user_id: user.id
        can :create, Collection

        # Playlists
        can [:read, :create], Playlist
        can [:update, :destroy], Playlist, user_id: user.id

        # Digests
        can :manage, PlaylistDigest, user_id: user.id

        # Filters
        can :read, Filter

        # Segments
        can :read, Segment

        # Share
        can [:new, :create], Share

        # Comments
        can :manage, Comment

        # Homepage Sections
        can :manage, HomepageSection

        # Assets
        can :manage, Asset, user_id: user.id

        # Downlaods
        can :read, Download

      elsif user.role?(:content_creator)
        can :manage, User, id: user.id
        can :manage, Video, uploader_id: user.id
        can :create, Video
        can :manage, [Participant, FilterData]
      elsif user.role?(:form_admin)
        # can :manage, Form
        can :read, :all
      elsif user.role?(:client_user)
        can :read, Homepage, client_id: user.client_id
        can :read, Homepage, channel: { groups: { id: user.group_ids } }

        # can :read, Channel, clients: {id: user.client_id}, groups: { id: user.group_ids }
        can :read, Channel, groups: { id: user.group_ids }

        # Playlists
        can [:read, :create], Playlist
        can [:update, :destroy], Playlist, user_id: user.id

        # Playlist Clips
        can [:read, :create], PlaylistClip

        # Playlist collections/folders
        can :manage, Collection, user_id: user.id
        can :create, Collection

        # Digests
        can :read, PlaylistDigest
        can :manage, PlaylistDigest, playlist: { user_id: user.id }
        can :create, PlaylistDigest

        # Comments
        can :manage, Comment
        cannot :destroy, Comment

        # Segments
        can :read, Segment

        # Filters
        can :read, Filter

        # Videos
        can :read, Video

        # Share
        can [:new, :create], Share

        # Comments
        can :create, Comment

        # Assets
        can :manage, Asset, user_id: user.id

        # Downlaods
        can :read, Download
      end

    end
  end

end

user.rb

class User < ActiveRecord::Base
  include SimpleFilter

  # Important! Never change value of role, always add role with unique value(integer)
  Roles = {
    client_user: 0,
    form_admin: 1,
    content_creator: 2,
    client_admin: 3,
    tbs_admin: 4
  }

  Sharing = {
    none: 0,
    internal: 1,
    external: 2
  }

  # Include default devise modules. Others available are:
  # :token_authenticatable, :encryptable, :confirmable, :lockable, :timeoutable and :omniauthable
  devise :database_authenticatable, :registerable,
         :recoverable, :rememberable, :trackable, :validatable, :confirmable

  # Setup accessible (or protected) attributes for your model
  attr_accessible :name, :roles, :email, :password, :password_confirmation, :remember_me,
                  :client_id, :position, :employee, :trial_password, :trial_length, :trial_expires, :roles,
                  :group_ids, :channel_id, :avatar, :avatar_cache

  # Relations
  belongs_to :client

  ## Only content creators belong to channel and have a participant
  belongs_to :channel
  belongs_to :participant

  belongs_to :admins, inverse_of: :user
  belongs_to :content_creators, inverse_of: :user
  has_and_belongs_to_many :groups, uniq: true, before_add: :validate_group

  # Playlists
  has_many :playlists

  # Digests
  has_many :digests, class_name: 'PlaylistDigest'

  ## Comments
  has_many :comments

  # Shares
  has_many :shares
  has_many :shared, foreign_key: :sharer_id

  # Downloads
  has_many :downloads, dependent: :destroy

  # Validation
  validates_presence_of :name, :email
  validates_presence_of :client, if: Proc.new { |user| !user.new_record? }
  validates_presence_of :groups, if: Proc.new { |user| !user.new_record? }
  validates_presence_of :channel_id, if: Proc.new { |user| user.role?(:content_creator) }

  # Callbacks
  after_create :add_to_default_group
  after_create :create_participant

  # Scopes
  scope :with_role, lambda { |role| {conditions: "roles_mask & #{2**Roles[role.to_sym]} > 0"} }

  # File upload
  mount_uploader :avatar, ImageUploader

  # Class Methods

   ...

end

video.rb

class Video < ActiveRecord::Base
  include SimpleFilter

  paginates_per 15

  # Callbacks
  before_validation :set_defaults
  after_validation :check_reviewed
  before_save :generate_transcript_without_timestamps!
  after_create :encode

  def self.Level()
    {
      low: 1,
      medium: 2,
      high: 3
    }
  end

  TranscriptionStatus =
  {
    none: 0,
    waiting_for_encoding: 4,
    ready_to_send: 1,
    sent_for_transcription: 2,
    transcribed: 3,
    failed: 5
  }

  # Separator for Sphinx indexing of participant metadata ("Filters")
  FILTER_VALUE_SEPARATOR = '~~~'

  attr_accessor :primary_channel_id, :author
  attr_accessible :title, :description, :video, :transcript, :date,
                  :participants_attributes, :video_cache, :tag_ids, :image, :image_cache,
                  :primary_channel, :channel_ids, :primary_channel_id, :location_ids, :engaging, :insightful,
                  :experience_personal, :experience_customer, :experience_employee, :is_published,
                  :start_timecode, :end_timecode, :duration, :transcript_topics, :transcript_tags,
                  :other_channel_ids, :author

  # Relations
  # Content creator
  belongs_to :uploader, class_name: 'User', foreign_key: :uploader_id

  # All Channels
  has_many :video_channels, :dependent => :destroy
  has_many :channels, through: :video_channels, source: :channel

  # Primary/Default channel
  has_one :primary_video_channels, class_name: 'VideoChannel', conditions: { primary: true }
  has_one :primary_channel, through: :primary_video_channels, source: :channel

  # Non primary channels
  # This is needed for to set non primary channels in video
  has_many :other_video_channels, class_name: 'VideoChannel', conditions: { primary: false }
  has_many :other_channels, through: :other_video_channels, source: :channel

  has_many :filter_data, class_name: 'FilterData', :dependent => :destroy

  has_many :participants, through: :filter_data, uniq: true
  accepts_nested_attributes_for :participants

  # Categories, topics, tags
  has_many :video_tags, :dependent => :destroy
  has_many :tags, through: :video_tags

  # Locations
  has_and_belongs_to_many :locations

  # Sections
  has_many :homepage_sections, as: :featured

  # Encodings
  has_many :encodings, class_name: 'VideoEncoding', :dependent => :destroy

  # Clips
  has_many :clips, class_name: "VideoClip", :dependent => :destroy

  ## Sharing
  has_many :shares, as: :shareable

  ... 

end

[xhoy]

Thanks for your submission! The ryanb/cancan repository has been inactive since Sep 06, 2013.
Since only Ryan himself has commit permissions, the CanCan project is on a standstill.

CanCan has many open issues, including missing support for Rails 4. To keep CanCan alive, an active fork exists at cancancommunity/cancancan. The new gem is cancancan. More info is available at #994.

If your pull request or issue is still applicable, it would be really appreciated if you resubmit it to CanCanCan.

We hope to see you on the other side!