Pundit Policy Scope for Has Many Through Relationship

Question

First Post here, so I hope it makes sense, but I've been spinning my wheels too long on this. First a little context. I am building a Wiki app with 3 models: a User, Wiki, and Collaboration (join table).

I am using Devise and Pundit and have 4 classes of users that should view a different subset of wikis based on their status.

Here's the rules:

• Public User (not logged in) - should view(no edit) only Public wikis (:hide => false)
• Authenticated User(:role => "standard") - should View, Edit & Delete only Public wikis.
• Premium User (:role => "premium") -View, Edit, Delete Public wikis and the ability to create private wikis(:hide => true) and add collaborators to their private wikis which gives them edit rights to that wiki.
• Admin (:role => "admin") full control over all records.

So I have a lengthy scope.joins condition in Policy scope(checks state of user) for my Wiki index view to give the current_user a subset of wikis list based on their role.

It that keeps spitting out this error:

Started POST "/__better_errors/59802a57d82fd17e/variables" for 127.0.0.1 at 2014-12-02 09:05:41 -0800
  Wiki Load (0.4ms)  SELECT "wikis".* FROM "wikis" INNER JOIN "collaborations" ON "collaborations"."wiki_id" = "wikis"."id" WHERE (hide = 'f' or user_id = 2 or collaborations.user_id = 2)
SQLite3::SQLException: ambiguous column name: user_id: SELECT "wikis".* FROM "wikis" INNER JOIN "collaborations" ON "collaborations"."wiki_id" = "wikis"."id" WHERE (hide = 'f' or user_id = 2 or collaborations.user_id = 2)  

Here's the Policy

class WikiPolicy < ApplicationPolicy

  # What collections a user can see users `.where` 
  class Scope < Scope

    def resolve
      if user && user.role == 'admin'
        scope.all 
      elsif user
        scope.joins(:collaborations).where("hide = :hide or user_id = :owner_id or collaborations.user_id = :collaborator_id", {hide: false, owner_id: user.id, collaborator_id: user.id})
      else 
        scope.where hide: false
      end
    end
  end


  #Policies are boolean logic true or false to determine if a user has access to a controller action.
  def update?
    (user && user.role == 'admin') || (user && record.users.pluck(:id).include?(user.id)) || (user && user.id == record.owner.id )
  end

  def show?
    (user.present? && user.admin?) or not record.hide?
  end

  def premium?
    user.admin? or user.premium?
  end

  def edit?

  end

end      

My models

class Wiki < ActiveRecord::Base
  belongs_to :user
  has_many :collaborations, dependent: :destroy
  has_many :users, through: :collaborations

  def owner
    user
  end

  def collaborators
    users
  end

  validates :title, length: { minimum: 5 }, presence: true
  validates :body, length: { minimum: 20 }, presence: true

end

class User < ActiveRecord::Base
  devise :database_authenticatable, :registerable,
         :recoverable, :rememberable, :trackable, :validatable, :confirmable
  has_many :wikis
  has_many :collaborations 
  has_many :cowikis, through: :collaborations, source: :wiki

  after_initialize :init

  def admin?
    role == 'admin'
  end

  def premium?
    role == 'premium'
  end

  def standard?
    role == 'standard'
  end

private
  def init
    if self.new_record? && self.role.nil?
      self.role = 'standard'
    end
  end
end

 class Collaboration < ActiveRecord::Base
  belongs_to :user
  belongs_to :wiki
end

Wiki Controller

class WikisController < ApplicationController
  def index
   @wikis = policy_scope(Wiki)  
    # authorize @wikis
  end

  def show
    @wiki = Wiki.find(params[:id])
    authorize @wiki
  end

  def new
    @wiki = Wiki.new
    authorize @wiki
  end

  def create
    @wiki = Wiki.new(wiki_params)
    authorize @wiki
    if @wiki.save
      flash[:notice] = "Post was saved."
      redirect_to @wiki
    else
      flash[:error] = "There was an error saving the post. Please try again."
      render :new
    end
  end

  def edit
    @user = current_user
    @users = User.all
     @wiki = Wiki.find(params[:id])
     authorize @wiki
  end

 def update
   @wiki = Wiki.find(params[:id])
   authorize @wiki
   @wiki.collaborators = params[:wiki][:user_ids]
   if @wiki.update_attributes(wiki_params)
     flash[:notice] = "Post was updated."
     redirect_to @wiki
   else
     flash[:error] = "There was an error saving the post. Please try again."
     render :edit
   end
 end

  def destroy
    @wiki = Wiki.find(params[:id])
    authorize @wiki
    title = @wiki.title

    if @wiki.destroy
      flash[:notice] = "\"#{title}\" was deleted successfully."
      redirect_to wikis_path
    else
      flash[:error] = "There was an error deleting the wiki."
      render :show
    end
  end

  private

   def wiki_params
     params.require(:wiki).permit(:title, :body, :hide)
   end
end

I hope you are not overwhelmed with code, but I wanted to provide as much info as possible.

Thanks for your help!

oh this is most likely the offending code but I included everything else for context.

scope.joins(:collaborations).where("hide = :hide or user_id = :owner_id or collaborations.user_id = :collaborator_id", {hide: false, owner_id: user.id, collaborator_id: user.id})

Answer 1

This is the most valuable part of the error message:

SQLite3::SQLException: ambiguous column name: user_id

The ambiguous column name error can happen when the SQL result set has multiple columns with the same name. In this case the column is user_id.

The user_id column is on the wikis table and the collaborations table. Both of these tables are used in the SQL query.

Here's the SQL snippet causing the problem:

# DB can't figure out which table "user_id = 2" refers to:
WHERE (hide = 'f' or user_id = 2 or collaborations.user_id = 2)

We need to specify a table name prefix on that user_id column.

To do this, try changing this line in WikiPolicy from:

scope.joins(:collaborations)
    .where("hide = :hide or user_id = :owner_id or collaborations.user_id = :collaborator_id", {hide: false, owner_id: user.id, collaborator_id: user.id})

to:

scope.joins(:collaborations)
    .where("hide = :hide or wikis.user_id = :owner_id or collaborations.user_id = :collaborator_id", {hide: false, owner_id: user.id, collaborator_id: user.id})