CODEWITHSUNDEEP
ruby-on-railsruby
Dev
Dev

Reputation: 467

Rails: find if a value exists inside a array column

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?

Upvotes: 0

Views: 1624

Answers (1)

Tarek N. Elsamni
Tarek N. Elsamni

Reputation: 1837

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'

Upvotes: 3

Related Questions