CODEWITHSUNDEEP
ruby-on-railssqlitepostgresqlherokurails-activerecord
MandM
MandM

Reputation: 3383

Rails and Heroku PGError: column does not exist for where argument

I've searched around and found some similar searches, but not one that actually detailed the issue I was having, so I figured I'd share my findings.

Problem
When pushing Ruby on Rails project to Heroku (DB = PostgresQL), I got some errors that I hadn't received in the dev environment (DB = SQLite).

Details
I have a model, say Message, that has a polymorphic association with an owner, which could either be a User or Customer (these aren't the actual models, so please don't ask why there is a polymorphic association when that obviously be something like a boolean customer column in a User model).

When querying the DB using ActiveRecord I had a method in the Message model that retrieved the messages associated with a certain User (it is important to note that in a polymorphic association for an owner, in this case, the Message model will add two columns, owner_id and owner_type, where owner_type would either be User or Customer)

The method was defined as follows:

    def self.getMessagesForUser(user)
      message_ids = "SELECT owner_id FROM messages WHERE owner_id = :user_id AND owner_type = \"User\""
      where("message_id IN (#{message_ids})", user_id: user.id)
    end

As you can see, the message_ids string is defined to be easily inserted into the where method for the model (hint: this is where the problem lies).

Error
I was receiving, the following error:

    ActionView::Template::Error (PGError: ERROR:  column "Customer" does not exist)

I knew something was awry because the argument was being perceived as a column, so I did some tinkering and found the solution.

Solution
I didn't realize that some querying languages (like SQL, PostgresQL, etc) are not as forgiving as SQLite when it comes to using either single-quotes or double-quotes in a query. Specifically, PostgresQL doesn't support double-quotes in the query, i.e. the User argument for owner_type in the message_ids string! I simply changed the line in the method from:

    message_ids = "SELECT owner_id FROM messages WHERE owner_id = :user_id AND owner_type = \"User\""

to:

    message_ids = "SELECT owner_id FROM messages WHERE owner_id = :user_id AND owner_type = \'User\'"

Put more simply:

"User" needed to be 'User'

And things went smoothly.

Upvotes: 1

Views: 1014

Answers (1)

MandM
MandM

Reputation: 3383

Solution
I didn't realize that some querying languages (like SQL, PostgresQL, etc) are not as forgiving as SQLite when it comes to using either single-quotes or double-quotes in a query. Specifically, PostgresQL doesn't support double-quotes in the query, i.e. the User argument for owner_type in the message_ids string! I simply changed the line in the method from:

    message_ids = "SELECT owner_id FROM messages WHERE owner_id = :user_id AND owner_type = \"User\""

to:

    message_ids = "SELECT owner_id FROM messages WHERE owner_id = :user_id AND owner_type = \'User\'"

Put more simply:

"User" needed to be 'User'

And things went smoothly.

Upvotes: 1

Related Questions