Or & and in Rails ActiveRecord where clause

Question

I'm using Rails 3.2 and I've got a database table in which I want to find all the rows that match the following criteria:

a = true and b = true and ( 0< c <1 or d=1), a, b, c, d are columns.

Can I have something like:

 Route.where(:a => true,
             :b => true,
             :c => 0..1 OR :d=1
             ).all

Take a look at http://stackoverflow.com/questions/3548548/rails3-combine-scope-with-or — Christoph Petschnig, Jun 09 '12 at 07:43
Have a look on this screencast http://railscasts.com/episodes/354-squeel — mohamagdy, Jun 09 '12 at 15:16

score 19 · Accepted Answer · answered Jun 09 '12 at 06:16

19

I may be wrong, but I don't think you could form that query using the Arel-based where function; you'd need to form up the database query string yourself.

Assuming you're using SQLite or Postgres:

Route.where("a = true and b = true and ((c > 0 and c < 1) or d = 1)").all

I haven't tested this code, but I suspect that might do the job for you. Note this is less 'portable' code; if you change the database you're using the query may break.

answered Jun 09 '12 at 06:16

Rob d'Apice

2,416
1
19
29

3

`where('a = :true and b = :true and ((c > :lo and c < :hi) or d = :d', :true => true, :lo => 0, :hi => 1, :d => 1)` would be safer, different databases uses different things for "true". Or even `.where(:a => true, :b => true).where('c > :lo and c < :hi) or d = :d', ...)`. – mu is too short Jun 09 '12 at 06:18

score 16 · Answer 2 · answered Sep 04 '14 at 19:33

16

In Rails 4 you can also do

Route.where(:a => true,:b => true,:c => [1,2]).all

This will find where c is 1 or 2.

answered Sep 04 '14 at 19:33

user2031423

347
4
7

Is .all is required ? I think it is default. – learner Apr 22 '15 at 16:20
1

Yea .all isn't required, I was just keeping it consistent with the question. – user2031423 May 28 '15 at 23:38
This worked fine. Thanks. You can also use symbols like this: `Route.where(a: true, b: true, c: [1,2])` – Promise Preston Jul 17 '21 at 09:26

pepe · Answer 3 · 2012-06-15T14:29:28.873

4

I think Rob is right about arel not supporting OR yet. From the arel site:

The OR operator is not yet supported. It will work like this:

users.where(users[:name].eq('bob').or(users[:age].lt(25)))

The AND operator will behave similarly.

edited Jun 15 '12 at 14:29

answered Jun 13 '12 at 21:53

pepe

555
4
8

Or & and in Rails ActiveRecord where clause

3 Answers3