PG::UndefinedColumn: ERROR: column courts.user_id does not exist

Question

This is the error I receive when I run a test for this file.

The error is in line 83 when I attempt to remove the user from the database using the 'destroy' method.

This is the repo in question frozen in time.

The error:

ActiveRecord::StatementInvalid:
       PG::UndefinedColumn: ERROR:  column courts.user_id does not exist
       LINE 1: SELECT "courts".* FROM "courts" WHERE "courts"."user_id" = $...

I'm trying to connect the table users with the table court this way:

User is the administrator of many courts
A court can have only one administrator

As you can see, 'Court' has a foreign key linked to 'users', but called 'administrator'.

I want 'administrator' to be the alias of a user because in the future User and Court might have more relationships: 'owner' for example.

I can see that active record is building this query:

SELECT "courts".* FROM "courts" WHERE "courts"."user_id" = $...

Maybe this can be solved if Active Record build a query this way

SELECT "courts".* FROM "courts" WHERE "courts"."administrator_id" = $...

But I don't know how to do it and if that would be prudent.

Maybe there is another way to do this. Something tidier, I feel I'm not doing the association properly.

What do you recommend?

models/user.rb

class User < ActiveRecord::Base
    before_save :format_input
    # extend Devise::Models
    # Include default devise modules        .
    devise  :database_authenticatable,                
            # :validatable,
            # :recoverable,
            # :rememberable,
            # :trackable,             
            # :confirmable,
            # :omniauthable,
            :registerable
    
    # note that this include statement comes AFTER the devise block above
    include DeviseTokenAuth::Concerns::User
    validates :first_name, presence: true, length: { in: 1..20 }
    validates :last_name, presence: true, length: { in: 1..20 }
    validates :email, uniqueness: true
    validates_format_of :email, with: /@/
    validates :password, presence: true, length: { in: 8..20 }, :on => :create

    has_many :courts, dependent: :destroy
    
    private

    def format_input
            self.first_name = first_name.downcase.titleize
            self.last_name = last_name.downcase.titleize
            self.email = email.downcase
    end
end

models/court.rb

class Court < ApplicationRecord 

  belongs_to :administrator, :class_name => :user
  validates :name, presence: true, length: { in: 1..20 }
  validates :address, presence: true, length: { in: 1..50 }
  validates :description, presence: true, length: { in: 1..100 }
  validates :description, presence: true, length: { in: 1..100 }

end

user_3724716 · Answer

The issue is on this line.

has_many :courts, dependent: :destroy

By default, ActiveRecord assumes the foreign key is named _id. In this case, when you delete a user, ActiveRecord is trying to delete the associated courts using the user_id foreign key which doesn't exist. Pass the foreign_key option to the has_many call.

has_many :courts, dependent: :destroy, foreign_key: :administrator_id

From the docs:

:foreign_key

Specify the foreign key used for the association. By default this is guessed to be the name of this class in lower-case and _id suffixed. So a Person class that makes a has_many association will use person_id as the default :foreign_key.

There's another issue. You should pass the uppercased class name as a string in the belongs_to call in the Court model.

belongs_to :administrator, class_name: 'User'

This is unrelated to the question but you might want to check if it is okay to delete a court when the user record is deleted.

PG::UndefinedColumn: ERROR: column courts.user_id does not exist

1 Answers