Rails 4 Active Record array query

Question

I'm using rails 4 with postgres db, and im trying out array. But I can't get my query to work.

I have this so far:

@contacts = current_firm.contacts.order(:name)

and would like to add a params[:show] to the query, so it only shows the records with e.g. 'place' in the contactType column. if the url is contacts?show=place.

I really hope you can help, because I have tried to figure this out for hours now.

Schema:

create_table "contacts", force: true do |t|
  t.integer  "firm_id"
  t.string   "contactType",                 array: true
  t.string   "name"
  t.string   "adress"
  t.integer  "zipcode"
  t.string   "town"
  t.string   "country",     default: "DK"
  t.integer  "cvr"
  t.string   "email"
  t.string   "contact"
  t.string   "phone"
  t.string   "cellPhone"
  t.string   "website"
  t.datetime "created_at"
  t.datetime "updated_at"
  t.boolean  "deleteFlag",  default: false
end

Console dump of Contact.all

#<ActiveRecord::Relation 
  [#<
    Contact id: 7, 
    firm_id: 5, 
    contactType: ["customer", "place"], 
    name: "PA Syd", 
    adress: "", 
    zipcode: nil, 
    town: "", 
    country: "DK", 
    cvr: nil, email: "", 
    contact: "Lars Opstrup", 
    phone: "", 
    cellPhone: nil, 
    website: "", 
    created_at: "2013-06-28 13:29:18", 
    updated_at: "2013-06-28 13:29:18", 
    deleteFlag: false
  >, 
  #<
    Contact id: 1, 
    firm_id: 5, 
    contactType: ["place"], 
    name: "Mads Ellesgaard", 
    adress: "", 
    zipcode: 6400, 
    town: "Soenderborg", 
    country: "DK", 
    cvr: nil, 
    email: "[email protected]", 
    contact: "", 
    phone: "", 
    cellPhone: nil, 
    website: "", 
    created_at: "2013-06-28 11:58:58", 
    updated_at: "2013-06-29 09:35:39", 
    deleteFlag: false
  >
]>

kwyoung11 · Accepted Answer · 2013-06-30 05:35:46Z

6

You want to use the postgresql operator && (array overlap operator) to see if any values overlap between the params hash and the data in the table.

@contacts = current_firm.contacts.order(:name).where("ARRAY[?]::varchar[] && contactType", params[:show])

The params hash should be of type text[], while the contactType record is of type varchar[], so you need to cast the incoming params hash to match that of the contactType record for postgres to not throw an error.

See the docs here: http://www.postgresql.org/docs/9.2/static/functions-array.html

You may also want to check out the postgres_ext gem if you have many of these types of queries, although for this use case it might be excessive overhead: https://github.com/dockyard/postgres_ext/blob/master/docs/querying.md

answered Jun 30, 2013 at 5:35

kwyoung11

9781 gold badge11 silver badges31 bronze badges

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

1 Comment

Mads E Over a year ago

Thank you so much! It was a great help, I just had to rename my column from contactType to contact_type and it works.

Mikhail Nikalyukin · Accepted Answer · 2013-06-29 18:03:06Z

3

@contacts = current_firm.contacts.where("'?' = ANY (contactType)", params[:show]).order(:name)

I didn't test it, but it should work.

edited Jun 29, 2013 at 18:03

answered Jun 29, 2013 at 10:25

Mikhail Nikalyukin

12k1 gold badge49 silver badges72 bronze badges

7 Comments

Mads E Over a year ago

It turns this out: PG::Error: ERROR: column "customer" does not exist LINE 1: ...eteFlag" = 'f' AND "contacts"."firm_id" = $1 AND (customer =... ^ : SELECT "contacts".* FROM "contacts" WHERE "contacts"."deleteFlag" = 'f' AND "contacts"."firm_id" = $1 AND (customer = ANY (contactType)) ORDER BY "contacts".name ASC

Mads E Over a year ago

if i do @contacts = current_firm.contacts.where("(contactType) = ANY '#{params[:show]}'").order(:name)

Mads E Over a year ago

i get: PG::Error: ERROR: syntax error at or near "'customer'"

house9 Over a year ago

where("#{params[:show]} = ANY (contactType)") looks like it is susceptible to sql injection? maybe where("? = ANY (contactType)", params[:show]) would be better

house9 Over a year ago

in the generated sql AND (customer = ANY (contactType)) i believe customer needs to be single quoted? i.e. AND ('customer' = ANY (contactType)) - see coderwall.com/p/sud9ja

|

Jean Uchôa · Accepted Answer · 2015-12-30 14:59:21Z

1

@contacts = current_firm.contacts.where.contains(contactType: [params[:show]]).order(:name)

see: https://viget.com/extend/searching-serialized-fields-in-rails-using-postgres-arrays

answered Dec 30, 2015 at 14:59

Jean Uchôa

111 bronze badge

Collectives™ on Stack Overflow

Rails 4 Active Record array query

3 Answers 3

1 Comment

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related