CODEWITHSUNDEEP
rubypostgresqlsinatrasequel
user1903663
user1903663

Reputation: 1725

How to use Sequel to select one field from database

I am using Sinatra and Sequel with PostgreSQL.

After authentication, I want to welcome the user by printing their name but I cannot get only the value of the user's name from the database, it comes out as a hash.

The query is:

current_user = DB[:users].select(:username).where('password = ?', password).first

and the resulting piece of data is: 

Welcome, {:username=>"Rich"}

which looks rather weird, I would prefer it to read "Welcome, Rich".

What am I doing wrong here? I tried the same query without 'first" at the end and that does not work either.

Upvotes: 5

Views: 5998

Answers (2)

mwp
mwp

Reputation: 8467

Try Sequel::Dataset#get. Also, as Phrogz points out, Sequel::Dataset#where can take a hash (it will securely escape values to prevent injection attacks).

current_username = DB[:users].where(password: password).get(:username)

There's also Sequel::Dataset#where_single_value, which is optimized for this exact situation:

current_username = DB[:users].select(:username).where_single_value(password: password)

Upvotes: 2

Phrogz
Phrogz

Reputation: 303291

You can either pull the (single) column you selected out of the Hash you are given:

current_user = DB[:users].select(:username).where('password=?', password).first[:username]

Or you can map your results to an array of usernames and pull the first:

# Using a hash in the filter method is simpler than SQL placeholders.
current_user = DB[:users].filter(password:password).select_map(:username).first

But the best way is to get only the user you care about, and then get the name:

# Using [] on a dataset returns the first row matching the criteria
current_user = DB[:users][password:password][:username]

Upvotes: 5

Related Questions