Detect duplicate records using Sequel and PostgreSQL

Question

I want to check to see if the email address that a new person uses when registering is a duplicate of an existing record.

Here is the code:

post '/signup' do
    email_address = params['email_address']
    username = params['username']
    password = params['password']
    @duplicate = DB[:users].select(:email_address).where('email_address = ?', email_address)
    if email_address = @duplicate
        redirect "/?msg=Email address already in use. Try again"
        return
    end
end

This blocks all attempts even when the email address is not a duplicate and redirects with the error message.

If I substitute @duplicate with duplicate (without the @ mark) then same result, all attempts blocked. If I use == instead of = then duplicate email addresses are ignored and no attempts are blocked.

I know the query:

DB[:users].select(:email_address).where('email_address = ?', email_address)

is correct because I have tested it. So I assume the problem lies with the construction of the if clause.

score 1 · Answer 1 · answered May 13 '13 at 10:33

1

You need add .first at the end of you query otherwise you will get an array.

After that you can test if @duplicate != nil

answered May 13 '13 at 10:33

Julien Duponchelle

595
3
17

thanks, works. "nil" is new to me. The statement seems to mean if no result (i.e. no duplicate) then redirect .. which is the opposite of what I intend. If there is a 0 result then there is no duplicate so proceed. ?? Anyway, thank you. – user1903663 May 13 '13 at 10:49
nil == NULL it's mean you have no result – Julien Duponchelle May 14 '13 at 07:50

toro2k · Accepted Answer · 2013-05-13T12:02:57.427

If you write email_address = @duplicate the code inside the if will be always excuted since the condition for the if expression is always true. Anything in Ruby except nil and false evaluates as true in boolean context.

If you write email_address == @duplicate the code inside the if will be never executed since you are trying to compare a string (email_address) with an object of class Sequel::Postgres::Dataset (@duplicate).

A better approach could be

post '/signup' do
    # same code as yours...
    @duplicate = DB[:users].where(email_address: email_address).count
    unless @duplicate.zero?
      # redirect and return
    end
end

Thank you very much for both your solutions. I believe that I can generalize from here, in addition to solving my immediate problem. — user1903663, May 13 '13 at 12:35

Detect duplicate records using Sequel and PostgreSQL

2 Answers2