0

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: "test@testemail.com", 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: "test@testemail.com")
  TimeEntry Load (0.3ms)  SELECT "time_entries".* FROM "time_entries" WHERE "time_entries"."email" = $1  [["email", "test@testemail.com"]]
=> #<ActiveRecord::Relation [#<TimeEntry id: 1, user: "Test", email: "test@testemail.com", 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 = 'test@testemail.com'")
  TimeEntry Load (0.4ms)  SELECT "time_entries".* FROM "time_entries" WHERE (email = 'test@testemail.com')
=> #<ActiveRecord::Relation [#<TimeEntry id: 1, user: "Test", email: "test@testemail.com", 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.

Riaan
  • 11
  • 1
  • 3
  • 1
    Is 'user' a keyword-like column name? http://stackoverflow.com/questions/7651417/escaping-keyword-like-column-names-in-postgres – Riaan Jan 03 '17 at 16:37

1 Answers1

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')
Riaan
  • 11
  • 1
  • 3