DEV Community

Prathamesh Sonpatki
Prathamesh Sonpatki

Posted on • Originally published at prathamesh.tech on

OR query with multiple conditions on same column using Sequel

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'
Enter fullscreen mode Exit fullscreen mode

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

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

Enter fullscreen mode Exit fullscreen mode

This results into following SQL.

"SELECT * FROM \"users\" WHERE ((\"x\" = 1) OR (\"y\" = 2))"
Enter fullscreen mode Exit fullscreen mode

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')"
Enter fullscreen mode Exit fullscreen mode

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'))">
Enter fullscreen mode Exit fullscreen mode

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 (0)