postgres query involving regex array

Question

i've got this table:

create_table "packages", force: :cascade do |t|
  t.string   "sender_type"
  t.integer  "sender_account_id"
  t.integer  "sender_user_id"
  t.string   "address"
  t.text     "type"
  t.datetime "created_at",        null: false
  t.datetime "updated_at",        null: false
  t.string   "recipients",                     array: true
end

the recipient array takes the model type and id, separated by a comma, so like ["Manufacturer,4", "Retail,6"].

in my where query, i'm looking to find packages that include any recipients with the word "Manufacturer":

Package.where("sender_type = ? and recipients.include = ?", "school", regex).

just wondering if i'm anywhere close with this

Harakiri · Accepted Answer · 2017-01-31 09:15:55Z

0

If the string that you're searching for is always in the first place in your array, you could access it like that:

Package.where("sender_type = ? and recipients[0] LIKE = ?", "school", "Manufacturer").

Not tested so you may need to adapt.

answered Jan 31, 2017 at 9:15

Harakiri

7401 gold badge7 silver badges29 bronze badges

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

1 Comment

daveasdf Over a year ago

much appreciated! i ended up ditching the entire concept

Community · Accepted Answer · 2017-05-23 11:53:10Z

0

One of the ways is to convert recepients to text:

Package.where("sender_type = ? and recipients::text LIKE ?", "school", regex)

Or you can use unnest function to conver an array to set of rows. Check this answer: https://stackoverflow.com/a/7222285/7362128

edited May 23, 2017 at 11:53

CommunityBot

11 silver badge

answered Jan 31, 2017 at 10:04

Slava.K

3,0803 gold badges19 silver badges28 bronze badges

Collectives™ on Stack Overflow

postgres query involving regex array

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related