Rails activerecord is querying the wrong column name in HABTM association

Question

I have two models, lists and categories, with a many-to-many relationship, with a join table called categories_lists. When I try and get all the categories a singular list belongs to, I get the following error:

PG::UndefinedColumn: ERROR: column categories_lists.category_id does not exist LINE 1: ...ER JOIN "categories_lists" ON "categories"."id" = "categorie... ^ HINT: Perhaps you meant to reference the column "categories_lists.categories_id"...

View:

<ul>
  <% @categories.each do |cat| %>
  <li><%= cat.title %></li>
  <% end %>
</ul>

Controller:

def show
  @categories = List.find(params[:id]).categories.all
end

Model:

class List < ApplicationRecord
  has_and_belongs_to_many :categories
end

Table:

describe categories_lists                                                                                                                                  
+---------------+--------+-------------+
| Column        | Type   | Modifiers   |
|---------------+--------+-------------|
| categories_id | bigint |             |
| lists_id      | bigint |             |
+---------------+--------+-------------+

Its making the categories singular, instead of keeping it plural the way its defined in the model. I don't know how to correct this.

Thoughts?

Answer 1

The default for foreign keys is singular, you've got it the wrong way in your table.

You can either change it in the table to category_id and list_id, or specify the non-standard fkeys in your habtms (setting both :foreign_key and :association_foreign_key options on both sides), eg.

has_and_belongs_to_many :categories, association_foreign_key: :categories_id, foreign_key: :lists_id

Update

From OP, the migration they used:

class ChangeColumnNamesCategoriesLists < ActiveRecord::Migration[5.2]
  def change
    rename_column :categories_lists, :categories_id, :category_id 
    rename_column :categories_lists, :lists_id, :list_id
  end
end