Select Rails objects that include value in array attribute

Question

I have a Rails model called Box. Each Box object has a column :products, which is an array of strings that includes all the products that are being stored inside it at the time.

For each Box object, it is possible that the same value was stored in another Box.

Is there a query I can use to return all the Boxes that have value x stored in :products?

I know "where" works for finding objects with certain values, and with an array you might use "include?", but I'm having trouble working out a way to use either in this case, if it's at all possible.

jmarcs · Accepted Answer · 2014-10-08 15:20:17Z

6

There was an answer posted here before that worked well enough, but I looked around and found another query that was more succinct.

selected_boxes = Box.where("?=ANY(products)", x)

Where x is the value you are seeking in each object.

answered Oct 8, 2014 at 15:20

jmarcs

1151 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

calvin sugianto Over a year ago

I'm using Rails 4.2.5 and i cannot use this query. It said "wrong number of arguments (given 2, expected 1)"

Tony Beninate Over a year ago

Been trying to figure this out for an hour. Thank you. Not sure why this doesn't have more votes.

Dudo · Accepted Answer · 2014-10-07 00:50:27Z

0

Scope!

scope :contains, ->(items) { where("products LIKE ?", "%#{items.to_yaml}%") } # items is an array of your potential strings

So you'd call this as Box.contains(%w(foo bar)) or Box.contains(['some thing'])

Passing the array should let you search for multiple items at a time...

You can name the scope anything you want, obviously

LIKE for mySQL... ILIKE for postgreSQL

edited Oct 7, 2014 at 0:50

answered Oct 7, 2014 at 0:43

Dudo

4,1788 gold badges36 silver badges62 bronze badges

Collectives™ on Stack Overflow

Select Rails objects that include value in array attribute

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related