shlomo_maghen shlomo_maghen - 5 months ago 16
Ruby Question

Column username doesn't exist error when querying devise model with find_by_sql

I'm trying to query my Devise-created table with find_by_sql and am running into a strange error.

User.find_by_sql("select username from user")


results in:

PG::UndefinedColumn: ERROR: column "username" does not exist
LINE 1: select username from user
: select username from user

ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR:
column "username" does not exist

LINE 1: select username from user
^
: select username from user


Here's my devise migration (I added the username field):

class DeviseCreateUsers < ActiveRecord::Migration
def change
create_table(:users) do |t|
## Database authenticatable
t.string :username, null: false
t.string :email, null: false, default: ""
t.string :encrypted_password, null: false, default: ""

## Recoverable
t.string :reset_password_token
t.datetime :reset_password_sent_at

## Rememberable
t.datetime :remember_created_at

## Trackable
t.integer :sign_in_count, default: 0, null: false
t.datetime :current_sign_in_at
t.datetime :last_sign_in_at
t.string :current_sign_in_ip
t.string :last_sign_in_ip


t.timestamps null: false
end

add_index :users, :email, unique: true
add_index :users, :reset_password_token, unique: true
end
end


As you can see I have declared the field username. Querying other tables in this manner works perfectly. I have also tried
User.find_by_sql("select 'username' from user")
, and that just returns
[#<User id: nil>]
.

Thanks!

Edit: want to add that
User.last
returns

#<User id: 1, username: "xxxxxx", email: "xxxxxxxx",
encrypted_password: "xxxxxx", reset_password_token: nil,
reset_password_sent_at: nil, remember_created_at: nil,
sign_in_count: 2, current_sign_in_at: "2016-06-26 20:16:59",
last_sign_in_at: "2016-06-25 22:00:31", current_sign_in_ip: "xxxxx",
last_sign_in_ip: "xxxx", created_at: "2016-06-25 22:00:31",
updated_at: "2016-06-26 20:16:59">


with the correct values in place of 'xxxxx'

Answer

It should be because the name of the table is users rather than user. You can check schema.rb to check that the name of table is users or user. However, according to the migration, it has to be users.

This should work: User.find_by_sql("select username from users"). And you can use User.find_by_sql("select id, username from users") if you need user_id of record along with the username.

Or rather I would suggest you to do it in the ruby way: User.select(:username)

Hope it Helps : )