Rails: find if a value exists inside a array column

Question

I have a table named discounts with an array column named user_id.

 create_table "discounts", force: :cascade do |t|
 t.string "amount"
 t.string "name"
 t.string "from"
 t.string "to"
 t.integer "user_id", default: [], array: true

I want to search the entire table of discounts and find only the rows that have a user_id(current_user.id).

Any ideas how to implant this search?

relational approach would have made more sense I think

Andrey Deineko
– Andrey Deineko

2018-10-01 09:23:53 +00:00
Commented Oct 1, 2018 at 9:23 — Andrey Deineko
– Andrey Deineko, Commented Oct 1, 2018 at 9:23
Take a look at The has_and_belongs_to_many Association.

Stefan
– Stefan

2018-10-01 09:46:15 +00:00
Commented Oct 1, 2018 at 9:46 — Stefan
– Stefan, Commented Oct 1, 2018 at 9:46

Tarek N. Elsamni · Accepted Answer · 2018-10-01 09:27:02Z

3

Discount.where(":user_id = ANY(user_id)", user_id: current_user.id)

This should do it for you. I assume that user_id is an array field in a postgres database.

I'd recommend that you also index user_id to be more performant. To create an index for your array column, you must choose between GiST and GIN as strategies. The documentation covers the options nicely, but the distilled version is that GIN lookups are much (3x) faster, but take longer (10x) to build. If your data is read far more often than it is written, go for GIN.

Your migration could be something like:

create_table "discounts", force: :cascade do |t|
  t.string "amount"
  t.string "name"
  t.string "from"
  t.string "to"
  t.integer "user_id", default: [], array: true
end

add_index  :discounts, :user_id, using: 'gin'

edited Oct 1, 2018 at 9:27

answered Oct 1, 2018 at 9:21

Tarek N. Elsamni

1,8371 gold badge13 silver badges15 bronze badges

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

1 Comment

Tarek N. Elsamni Over a year ago

@Dev I also added a recommendation to add an index to your data to make it more performant. Could you mark the answer as correct? that helps to maintain the quality of the answers at Stackoverflow.

Collectives™ on Stack Overflow

Rails: find if a value exists inside a array column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related