Forum: Ruby on Rails where condition with array

Rails 3.2.11


Say, my app needs special conditions like


        plans = Plan.arel_table
        @plan = Plan.where(
                                 plans[:user_id].not_eq(3)
                            ).where(
                                 plans[:user_id].not_eq(4)
                            ).where(
                                 plans[:user_id].not_eq(7)
                            )...

If many not_eq conditions are needed, the way written like this is
inefficient.

Is there anyway to write this in a more concise way?

soichi

Have you considered doing a sql query?

my approach would be this

query_array = []
[3,4,7].each do |value|
  query_array << "user_id != #{value}"
end

Plan.where("#{query_array}.join(" and ")")

Also, the past week I found this gist from ryan
https://gist.github.com/ryanb/4974414

I'm still analyzing it but it taught me a lot

Javier

On Sun, Feb 24, 2013 at 8:43 PM, Javier Quarite <jquarites@gmail.com> 
wrote:
> Plan.where("#{query_array}.join(" and ")")
>
> Also, the past week I found this gist from ryan
> https://gist.github.com/ryanb/4974414
>
> I'm still analyzing it but it taught me a lot
>
> Javier

If going the SQL route, easier might be:

Plan.where("user_id not in (3,4,7)")

thanks. I haven't done sql query at all but this time I did for the 
first time.

soichi

Try something like this:

Plan.where("user_id not in (#{[3,4,7].join(', ')})")



2013/2/25 tamouse mailing lists <tamouse.lists@gmail.com>

You can always use the underlying arel_table:

Plan.where(arel_table[:user_id].not_eq([3,4,7]))


Saving off what the user_ids of [3,4,7] mean might make it a bit clearer
too:

declarative_name = arel_table[:user_id].not_eq(ids)
Plane.where(declarative_name)

Or succinctly in a scope:

scope :declarative_name, ->(ids) { 
where(arel_table[:user_id].not_eq(ids)) }
# => Plan.declarative_name [3,4,7]

There is a railscast for promoting the Arel predicates to class level
methods on active_record objects:
http://railscasts.com/episodes/355-hacking-with-arel

So you could end up with:

Plan.match(user_id: { not_in: [3,4,7] })

The draw back is that your interface for all active_record objects is 
more
expansive, but it depends on the style of the app and your preferences 
on
this matter, I like to keep a minimal public interface personally.

And finally the squeel gem <https://github.com/ernie/squeel> sprung to 
mind
which automagically adds these predicates to active_record objects too:

Plan.where{ user_id.not_in [3,4,7] }

I've tried all of these approaches except the railscast version, and 
like
access the arel_table directly to build queries the best, as you learn 
more
that way :-)

On Monday, 25 February 2013 22:23:48 UTC-5, Bruno Santos wrote:
>
> Try something like this:
>
> Plan.where("user_id not in (#{[3,4,7].join(', ')})")
>
>
This easier, and won't risk SQL injection if that array has 
user-generated
content:

Plan.where('user_id NOT IN ?', [3,4,7])

--Matt Jones

On Tue, Feb 26, 2013 at 2:42 PM, Matt Jones <al2o3cr@gmail.com> wrote:
> Plan.where('user_id NOT IN ?', [3,4,7])

Slight correction:

Plan.where('user_id NOT IN (?)', [3,4,7])