ActiveRecord 'where' query not working for certain string columns in postgresql

Question

I'm new to activerecord and postgresql and I have the following issue with regards to a activerecord query. Please help me understand why this is happening and how I can resolve it.

I have multiple text columns which is declared in a standard way in my schema file.

create_table "time_entries", force: :cascade do |t|
  t.string   "user"
  t.string   "email"    
  etc ....
end

I'm trying to find records with 'user' = 'Test'.

TimeEntry.where(user: "Test")

works fine, however

TimeEntry.where("user = 'Test'")

does not work.

I can see that the two query yields different sql queries but I'm trying to understand why this works for the 'email' field but not for the 'user' field.

User Field Query:

irb(main):011:0> TimeEntry.where(user: "Test")
  TimeEntry Load (0.5ms)  SELECT "time_entries".* FROM "time_entries" WHERE "time_entries"."user" = $1  [["user", "Test"]]
=> #<ActiveRecord::Relation [#<TimeEntry id: 1, user: "Test", email: "[email protected]", task: nil, description: "TEST CRM", billable: "No", start_date: "2016-01-03", start_time: "2000-01-01 14:20:22", end_date: "2016-01-03", end_time: "2000-01-01 14:26:29", duration: "2000-01-01 00:06:07", tags: nil, amount: 111, created_at: "2017-01-03 15:39:51", updated_at: "2017-01-03 15:39:51", project_id: 1, resource_id: nil>]>

irb(main):012:0> TimeEntry.where("user = 'Test'")
  TimeEntry Load (0.4ms)  SELECT "time_entries".* FROM "time_entries" WHERE (user = 'Test')
=> #<ActiveRecord::Relation []>
irb(main):013:0>

Email Field Query:

irb(main):013:0> TimeEntry.where(email: "[email protected]")
  TimeEntry Load (0.3ms)  SELECT "time_entries".* FROM "time_entries" WHERE "time_entries"."email" = $1  [["email", "[email protected]"]]
=> #<ActiveRecord::Relation [#<TimeEntry id: 1, user: "Test", email: "[email protected]", task: nil, description: "TEST CRM", billable: "No", start_date: "2016-01-03", start_time: "2000-01-01 14:20:22", end_date: "2016-01-03", end_time: "2000-01-01 14:26:29", duration: "2000-01-01 00:06:07", tags: nil, amount: 111, created_at: "2017-01-03 15:39:51", updated_at: "2017-01-03 15:39:51", project_id: 1, resource_id: nil>]>

irb(main):014:0> TimeEntry.where("email = '[email protected]'")
  TimeEntry Load (0.4ms)  SELECT "time_entries".* FROM "time_entries" WHERE (email = '[email protected]')
=> #<ActiveRecord::Relation [#<TimeEntry id: 1, user: "Test", email: "[email protected]", task: nil, description: "TEST CRM", billable: "No", start_date: "2016-01-03", start_time: "2000-01-01 14:20:22", end_date: "2016-01-03", end_time: "2000-01-01 14:26:29", duration: "2000-01-01 00:06:07", tags: nil, amount: 111, created_at: "2017-01-03 15:39:51", updated_at: "2017-01-03 15:39:51", project_id: 1, resource_id: nil>]>

Thanks for your help.

Is 'user' a keyword-like column name? stackoverflow.com/questions/7651417/… — Riaan
– Riaan, Commented Jan 3, 2017 at 16:37

Riaan · Accepted Answer · 2017-01-03 16:51:01Z

1

Ok, seems this issue occurs due to 'user' being a reserved keyword in postgres.

The following code works fine:

TimeEntry.where('time_entries.user = ?', 'Test')

or

TimeEntry.where('"user" = ?','Test')

answered Jan 3, 2017 at 16:51

Riaan

111 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

ActiveRecord 'where' query not working for certain string columns in postgresql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related