Rails 3 Activerecord Query Using Both SQL in and SQL or Operators

ActiveRecord OR query

Use ARel

t = Post.arel_table

results = Post.where(
  t[:author].eq("Someone").
  or(t[:title].matches("%something%"))
)

The resulting SQL:

ree-1.8.7-2010.02 > puts Post.where(t[:author].eq("Someone").or(t[:title].matches("%something%"))).to_sql
SELECT     "posts".* FROM       "posts"  WHERE     (("posts"."author" = 'Someone' OR "posts"."title" LIKE '%something%'))

How to specify multiple values in where with AR query interface in rails3

Sounds like you're doing something like this:

Model.where("attribute = ? OR attribute2 = ?", [value, value])

Whereas you need to do this:

# notice the lack of an array as the last argument
Model.where("attribute = ? OR attribute2 = ?", value, value)

Have a look at http://guides.rubyonrails.org/active_record_querying.html#array-conditions for more details on how this works.

Rails: Using greater than/less than with a where statement

Try this

User.where("id > ?", 200) 

How to use ANY instead of IN in a WHERE clause?

There are two variants of IN expressions:

• expression IN (subquery)
• expression IN (value [, ...])

Similarly, two variants with the ANY construct:

• expression operator ANY (subquery)
• expression operator ANY (array expression)

A subquery works for either technique, but for the second form of each, IN expects a list of values (as defined in standard SQL) while = ANY expects an array.

Which to use?

ANY is a later, more versatile addition, it can be combined with any binary operator returning a boolean value. IN burns down to a special case of ANY. In fact, its second form is rewritten internally:

IN is rewritten with = ANY

NOT IN is rewritten with <> ALL

Check the EXPLAIN output for any query to see for yourself. This proves two things:

• IN can never be faster than = ANY.
• = ANY is not going to be substantially faster.

The choice should be decided by what's easier to provide: a list of values or an array (possibly as array literal - a single value).

If the IDs you are going to pass come from within the DB anyway, it is much more efficient to select them directly (subquery) or integrate the source table into the query with a JOIN (like @mu commented).

To pass a long list of values from your client and get the best performance, use an array, unnest() and join, or provide it as table expression using VALUES (like @PinnyM commented). But note that a JOIN preserves possible duplicates in the provided array / set while IN or = ANY do not. More:

• Optimizing a Postgres query with a large IN

In the presence of NULL values, NOT IN is often the wrong choice and NOT EXISTS would be right (and faster, too):

• Select rows which are not present in other table

Syntax for = ANY

For the array expression Postgres accepts:

• an array constructor (array is constructed from a list of values on the Postgres side) of the form: ARRAY[1,2,3]
• or an array literal of the form '{1,2,3}'.

To avoid invalid type casts, you can cast explicitly:

ARRAY[1,2,3]::numeric[]
'{1,2,3}'::bigint[]

Related:

• PostgreSQL: Issue with passing array to procedure
• How to pass custom type array to Postgres function

Or you could create a Postgres function taking a VARIADIC parameter, which takes individual arguments and forms an array from them:

• Passing multiple values in single parameter

How to pass the array from Ruby?

Assuming id to be integer:

MyModel.where('id = ANY(ARRAY[?]::int[])', ids.map { |i| i})

But I am just dabbling in Ruby. @mu provides detailed instructions in this related answer:

• Sending array of values to a sql query in ruby?

---

Related Topics