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.
Top comments (0)