How to use Sequel to select one field from database

Question

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.

Donnettedonni

Methinks you should be using current_user[:username]. – Placida

Thanks for your advice. Solved: DB['select username from users where email_address = ?', email_address].each do |row| current_user = row[:username] ..... end Just like PHP. LoL! – Donnettedonni

Of course! current_user[:username] works too. You see i am new to ruby and to ORM style database interaction, so it's all a bit of a mystery to me. Anyway, cheers! – Donnettedonni

Don't write your queries like DB["some query"]. You're missing the power of Sequel and ORMs when you do that. Read the Sequel cheat sheet for a quick overview of how it's supposed to be used. – Pamella

Answer 1

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]

Kilogrammeter

Answer 2

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)

Donettedoney