ActiveRecord::StatementInvalid, SQLite3::SQLException: no such column:

Question

I'm working on my RoR skills and for a school assignment I was asked to create a scope subclass inside of a post policy in order to allow users with specific roles (admin, moderator, member and guest) to be able to view posts with varying amounts of access. When signed in as admin or a moderator all posts should be visible. As a member only posts posted by the logged in member should be visible. And as a guest no posts should be visible.

When I sign in as admin or a moderator I am able to view all posts on the site, however, when viewing the site as a member or guest I am unable to get things to work correctly and receive an error. Can someone please help?

Error I receive:

ActiveRecord::StatementInvalid in Posts#index

Showing /Users/Jason/code/bloccit/app/views/posts/index.html.erb where line #7 raised:

SQLite3::SQLException: no such column: posts.published: SELECT "posts".* FROM "posts"  WHERE "posts"."published" = 't'  ORDER BY created_at DESC

<%= link_to "New Post", new_post_path, class: 'btn btn-success' %>
  <% end %>

  <% @posts.each do |post| %>
    <div class="media">
      <div class="media-body">
        <h4 class="media-heading">

Here's my views/posts/index.html.erb

<h1>All Posts</h1>

<% if policy(Post.new).create? %>
  <%= link_to "New Post", new_post_path, class: 'btn btn-success' %>
<% end %>

<% @posts.each do |post| %>
  <div class="media">
    <div class="media-body">
      <h4 class="media-heading">
        <%= link_to post.title, post %>
      </h4>
      <small>
        submitted <%= time_ago_in_words(post.created_at) %> ago by <%= post.user.name %><br>
        <%= post.comments.count %> Comments
      </small>
    </div>
  </div>
<% end %>

post_policy.rb

class PostPolicy < ApplicationPolicy
  def index?
    true
  end

  class Scope < Scope
    attr_reader :user, :scope

    def initialize(user, scope)
      @user = user
      @scope = scope
    end

    def resolve
      if user.admin? || user.moderator?
        scope.all
      else
        scope.where(:published => true)
      end
    end
  end
end

application_policy.rb

class ApplicationPolicy
  attr_reader :user, :record

  def initialize(user, record)
    @user = user
    @record = record
  end

  def index?
    false
  end

  def show?
    scope.where(id: record.id).exists?
  end

  def create?
    user.present?
  end

  def new?
    create?
  end

  def update?
    user.present? && (record.user == user || user.admin?)
  end

  def edit?
    update?
  end

  def destroy?
    update?
  end

  def scope
    record.class
  end

  class Scope
    attr_reader :user, :scope

    def initialize(user, scope)
      @user = user
      @scope = scope
    end

    def resolve
      scope
    end
  end
end

post.rb

class Post < ActiveRecord::Base
  has_many :comments
  belongs_to :user

  default_scope { order('created_at DESC') }
end

user.rb

class User < ActiveRecord::Base
  # Include default devise modules. Others available are:
  # :confirmable, :lockable, :timeoutable and :omniauthable
  devise :database_authenticatable, :registerable,
         :recoverable, :rememberable, :trackable, :validatable, :confirmable

  has_many :posts

  def admin?
    role == 'admin'
  end

  def moderator?
    role == 'moderator'
  end

  def member?
    role == 'member'
  end
end

Lastly, here is my posts_controller.rb

class PostsController < ApplicationController
  def index
    @posts = policy_scope(Post)
  end

  def show
    @post = Post.find(params[:id])
  end

  def new
    @post = Post.new
    authorize @post
  end

  def create
    @post = Post.new(params.require(:post).permit(:title, :body))
    @post.user = current_user
    authorize @post
    if @post.save
      flash[:notice] = "Post was saved."
      redirect_to @post
    else
      flash[:error] = "There was an error saving the post.  Please try again."
      render :new
    end
  end

  def edit
    @post = Post.find(params[:id])
    authorize @post
  end

  def update
    @post = Post.find(params[:id])
    authorize @post
    if @post.update_attributes(params.require(:post).permit(:title, :body))
      flash[:notice] = "Post was updated."
      redirect_to @post
    else
      flash[:error] = "There was an error saving the post.  Please try again."
      render :edit
    end
  end
end

Answer 1

SQLite3::SQLException: no such column: posts.published:

Error Says You don't have published attribute in your table which is of course a boolean type as per your query

SELECT "posts".* FROM "posts"  WHERE "posts"."published" = 't'  ORDER BY created_at DESC

Please add a migration to your Post model:

$ rails g migration add_published_to_posts published:boolean

Then add default value to your published attribute

class AddPublishedToPosts < ActiveRecord::Migration
  def change
    add_column :posts, :published, :boolean, :default => false
  end
end

Finally, run : $ rake db:migrate

Answer 2

You have this method in your PostPolicy

def resolve
  if user.admin? || user.moderator?
     scope.all
   else
     scope.where(:published => true)
   end
end

And as you said, you are getting error when you signed in as member, so in the above method the else block will be executed and it looks for the posts where :published is true. So as you didn't have the published column in posts table, the error is triggered.

To resolve this, create a migration file to add published as boolean attribute with default value set to true to posts table.

Your migration file will look like this

class AddPublishedToPosts < ActiveRecord::Migration
  def change
    add_column :posts, :published, :boolean, :default => true
  end
end

And do rake:db:migrate