ActiveRecord::StatementInvalid PG::UndefinedColumn: ERROR

Question

I am using rails_admin for admin panel. Just change association in Image model

From this

class Image < ApplicationRecord
   belongs_to :user 
   belongs_to :product 
end

to this

class Image < ApplicationRecord
   has_one :user 
   has_one :product 
end

and User model is

class User < ApplicationRecord
   has_many :images,dependent: :destroy
end

Getting this error when I try to edit user from admin panel.From other side it is working fine.

ActiveRecord::StatementInvalid at /user/72/edit

PG::UndefinedColumn: ERROR:  column users.image_id does not exist
LINE 1: SELECT  "users".* FROM "users" WHERE "users"."image_id" = $1...
                                         ^
: SELECT  "users".* FROM "users" WHERE "users"."image_id" = $1 LIMIT $2

score 2 · Accepted Answer · edited May 23 '17 at 11:53

2

Rails has great documentation. The documentation for has_one found here states that "This method should only be used if the other class contains the foreign key" so it is looking for the foreign key of image_id on the user record. You would create this through a migration; please see this stack overflow post for more information on foreign keys and migrations.

But before you go that far, please consider:

Why would you change the Image relation from belongs_to :user to has_one :user in the first place? To boil down SQL associations and how Rails maps to them, if user has_many :images then images must necessarily belong_to user; they are opposite sides of the relation, with an exception being a has_and_belongs_to_many relation. If A belongs to B, then B has one (or many) of A. I would strongly encourage you to keep the images as having belongs_to relationships, while User and Product can have_many images. To anecdotally explain why this makes sense: a user might have profile pictures of their face, their house, etc. (has_many :images). A product (shoes) might have multiple pictures of the same shoe (has_many :images), but it is very unlikely that a particular picture of a shoe would map to both a user and a product.
I would strongly encourage you manage images and attachments using a gem like attachinary or paperclip. If you're handling uploaded file attachments, libraries like this will save you headaches down the road when it comes to resizing, image compression, etc. I am sure your direction in trying to change the relation was informed by some of the issues that these gems will solve for you.

edited May 23 '17 at 11:53

Community

1
1

answered Feb 02 '17 at 05:22

quetzaluz

1,071
12
17

I understand your explanation but I am using a single image table for both user and product . User image is saved at the time of new_user and edit_user and same as for product. user can have maximum 3 images. user and product have no direct relation it is like this user->store->products .But when I am using belongs_to for both user and product their ids is required in image table that's not I want. if user save a image than in image table product_id is null and vice versa and this is not happening in case of belongs_to . – Tanay Sharma Feb 02 '17 at 05:28
That makes sense and one image table is good design; in this case you may need to have an intersection table the columns "entity, entity_id, image_id" Entity would be the class name and the id, so a row would be something like "User, 1, 2" where 1 is the user_id and 2 is the image_id. I again encourage you to use something like Attachinary, which handles all of this setup for you. – quetzaluz Feb 02 '17 at 05:31
One more note on that is that the "has_many" would also need a "through:" declaration. More information on that here: http://guides.rubyonrails.org/association_basics.html#the-has-many-through-association – quetzaluz Feb 02 '17 at 05:31
I am using paperclip for image uploading and I updated my previous comment. – Tanay Sharma Feb 02 '17 at 05:36
Awesome! This guide covers how to use paperclip for this well: https://github.com/thoughtbot/paperclip#quick-start So to speak practically, User and Product would both have ` has_attached_file :image` and you should be set – quetzaluz Feb 02 '17 at 05:39
I hoped this helped as my main goal was to provide a quick answer after your initial post; if it didn't hopefully other answers can help. – quetzaluz Feb 02 '17 at 05:40
1

Just a little rails 5 trick `class Image < ApplicationRecord belongs_to :user belongs_to :product, optional: true end` It is working for me and thanks for your help . – Tanay Sharma Feb 02 '17 at 05:46

stevec · Answer 2 · 2021-09-13T03:22:02.760

Check capitalization

I arrived here because I was using incorrect capitalization in the column name.

The column was advertiserName, and I was doing

Listing.where(AdvertiserName: "Lowes")
Traceback (most recent call last):
ActiveRecord::StatementInvalid (PG::UndefinedColumn: ERROR:  column listings.AdvertiserName does not exist)
LINE 1: SELECT "listings".* FROM "listings" WHERE "listings"."Advert...

Changing the query to Listing.where(advertiserName: "Lowes") fixed it immediately.

Check quotes

Note also that PostgreSQL is not forgiving when using double quotes in

Check capitalization in column names

See more on that here

ActiveRecord::StatementInvalid PG::UndefinedColumn: ERROR

2 Answers2