CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecord
arthur
arthur

Reputation: 348

Matching array values in PostgreSQL with ActiveRecord

In my Rails 4 app I have a goal to see all contacts, where field visible_to in contacts table equal to 1. My visible_to is :integer, array: true.

However, I get the following exception:

PG::UndefinedFunction: ERROR: operator does not exist: integer[] = integer LINE 1: ....* FROM "contacts" WHERE "contacts"."visible_to" IN (1) OR... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.: SELECT "contacts".* FROM "contacts" WHERE "contacts"."visible_to" IN (1) ORDER BY created_at DESC

I searched for answers and as far as I see there is an issue with a type of visible_to. However, I couldn't find the solution. I also tried to get benefit from casts hint, but in vain.

My migration:

class AddVisibleToToContacts < ActiveRecord::Migration
    def change
      add_column :contacts, :visible_to, :integer, array: true, default: [], using: 'gin'     
    end 
end

Relevant variable from Controller:

@contacts_all_user_sorted = Contact.all.where(visible_to: [1]).order("created_at DESC")

Upvotes: 5

Views: 3809

Answers (2)

d34n5
d34n5

Reputation: 1318

From these two websites:

It seems that this syntax should work:

@contacts_all_user_sorted = Contact.all.where("visible_to @> ARRAY[?]", [1])

Does it work?

P.S: As @Quertie pointed out in the comments, you may want to cast the value in the case of a String array, by replacing ARRAY[?] by ARRAY[?]::varchar[]

Upvotes: 6

Ajit Singh
Ajit Singh

Reputation: 146

your migration seems pretty straight forward and correct.

can you please try this:

Contact.where('visible_to IN ?', ['1', '2'])

Upvotes: 0

Related Questions