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 Q. [email protected]
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)”)

Try something like this:

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

2013/2/25 tamouse mailing lists [email protected]

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

soichi

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 :slight_smile:

On Tue, Feb 26, 2013 at 2:42 PM, Matt J. [email protected] wrote:

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

Slight correction:

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

On Monday, 25 February 2013 22:23:48 UTC-5, Bruno S. 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 J.

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs