SQLite3::SQLException: no such column

Question

I'm trying to build an app where people can specify what posts need to be read before this one and here I'm trying to show them.

  <%= @post.before.each do |before| %>
  <li><%= before.title %></li>
  <% end %>

This gets me this error:

SQLite3::SQLException: no such column: connections.post_id: SELECT "posts".* FROM "posts" INNER JOIN "connections" ON "posts"."id" = "connections"."after_id" WHERE "connections"."post_id" = ?

There isn't supposed to be a post_id column in connections, there is supposed to be an before_id column in it.

My models are:

post.rb:

class Post < ApplicationRecord
    has_many :connections
    has_many :before, through: :connections, source: :after, foreign_key: "before_id"
    has_many :after, through: :connections, source: :before, foreign_key: "after_id"
end

connection.rb

class Connection < ApplicationRecord
    belongs_to :before, class_name: "Post", foreign_key: "before_id"
    belongs_to :after, class_name: "Post", foreign_key: "after_id"
end

The controller code for posts - automatically generated with scaffolding:

class PostsController < ApplicationController
  before_action :set_post, only: [:show, :edit, :update, :destroy]

  # GET /posts
  # GET /posts.json
  def index
    @posts = Post.all
  end

  # GET /posts/1
  # GET /posts/1.json
  def show
  end

  # GET /posts/new
  def new
    @post = Post.new
  end

  # GET /posts/1/edit
  def edit
  end

  # POST /posts
  # POST /posts.json
  def create
    @post = Post.new(post_params)

    respond_to do |format|
      if @post.save
        format.html { redirect_to @post, notice: 'Post was successfully created.' }
        format.json { render :show, status: :created, location: @post }
      else
        format.html { render :new }
        format.json { render json: @post.errors, status: :unprocessable_entity }
      end
    end
  end

  # PATCH/PUT /posts/1
  # PATCH/PUT /posts/1.json
  def update
    respond_to do |format|
      if @post.update(post_params)
        format.html { redirect_to @post, notice: 'Post was successfully updated.' }
        format.json { render :show, status: :ok, location: @post }
      else
        format.html { render :edit }
        format.json { render json: @post.errors, status: :unprocessable_entity }
      end
    end
  end

  # DELETE /posts/1
  # DELETE /posts/1.json
  def destroy
    @post.destroy
    respond_to do |format|
      format.html { redirect_to posts_url, notice: 'Post was successfully destroyed.' }
      format.json { head :no_content }
    end
  end

  private
    # Use callbacks to share common setup or constraints between actions.
    def set_post
      @post = Post.find(params[:id])
    end

    # Never trust parameters from the scary internet, only allow the white list through.
    def post_params
      params.require(:post).permit(:title, :text)
    end
end

Answer 1

has_many :connections in your Post model is telling Rails that you have a post_id on the connections table. If this is not the case, I recommend deleting the association or defining a foreign_key for the association.

I would recode your model as follows:

class Post < ApplicationRecord
  has_many :before, class_name: 'Connection', foreign_key: "before_id"
  has_many :after, class_name: 'Connection',  foreign_key: "after_id"
end

NOTE: You have source: :before and source: :after set opposite their respective association names and foreign_key names. I suspect that was not intentional. However, if it was intentional, you may want to swap foreign_keys between your two associations (i.e put the before_id on the after association and vis versa).