loading...

OR query with multiple conditions on same column using Sequel

prathamesh profile image Prathamesh Sonpatki Originally published at prathamesh.tech on ・2 min read

I recently started using Sequel to manipulate the PostgreSQL database in our Rails application at Last9.

I had to write an SQL query as follows.

SELECT * FROM users where status = 'inactive' OR status = 'deleted'

Sequel provides or function that can be used to construct OR expressions.

exp = Sequel.or(x: 1, y: 2)
DB[:users].where(exp)

This results into following SQL.

"SELECT * FROM \"users\" WHERE ((\"x\" = 1) OR (\"y\" = 2))"

Now let's try this same technique to write SQL for our use case. We want to select all users whose status is either

inactive or deleted.

> DB[:users].where(Sequel.or(status: 'deleted', status: 'inactive')).sql
(pry):8: warning: key :status is duplicated and overwritten on line 8
=> "SELECT * FROM \"users\" WHERE (\"status\" = 'inactive')"

But as you can see if we use same key which is status in this case, Ruby ignores it and the query that gets generated only has last value which is inactive in our case.

Ruby ignores multiple keys with same name in a hash so beware if you are using multiple values with same keys in a hash.

So how do we generate the OR query on same column using Sequel?

We can pass the arguments to Sequel.or as Array instead of Hash.

> DB[:users].where(Sequel.or([
                              ["status", "inactive"], 
                              ["status", "deleted"]
                             ])
                   )
=> "SELECT * FROM \"users\" WHERE (('status' = 'inactive') 
    OR ('status' = 'deleted'))">

This change makes sure that the OR query on same status column gets generated correctly.


Subscribe to my newsletteror follow me on Twitterto know more about how to use Sequel with Rails apps.

Discussion

pic
Editor guide