5

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")
arthur
  • 348
  • 4
  • 16
  • Hi. Is it normal that you wrote "defalult" instead of "default" in your migration file ? – d34n5 Aug 08 '14 at 13:14
  • Thx, it was just a typo here. In a migration everything is fine. – arthur Aug 11 '14 at 12:15
  • Short preliminary debrief from me on the reasons of the problem. (1) Again and again and again RTFM and not only ActiveRecord but [PostgreSQL](http://www.postgresql.org/docs/9.3/static/functions-array.html) as well (2) I was too heavy dependent on similar question and [similar solution](http://stackoverflow.com/questions/1441791/how-to-select-where-id-in-array-rails-activerecord-without-exception), that was not relevant for me (_because_ _of_ _PostgreSQL_ _specific?_). – arthur Aug 12 '14 at 07:04

2 Answers2

6

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[]

d34n5
  • 1,308
  • 10
  • 18
  • 1
    This doesn't work on string arrays and gives the error `ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR: operator does not exist: character varying[] @> text[] `. Fixed it by changing `ARRAY[?]` to `ARRAY[?]::varchar[]` – Qwertie Dec 07 '16 at 05:05
  • Thanks for your comment @Qwertie. I will add a note to my answer. – d34n5 Dec 07 '16 at 22:56
0

your migration seems pretty straight forward and correct.

can you please try this:

Contact.where('visible_to IN ?', ['1', '2'])
Ajit Singh
  • 146
  • 5
  • Ajit, thx for help. Here is an outcome from a console: `Contact Load (0.3ms) SELECT "contacts".* FROM "contacts" WHERE (visible_to IN '1','2') Contact Load (0.3ms) SELECT "contacts".* FROM "contacts" WHERE (visible_to IN '1','2') PG::SyntaxError: ERROR: syntax error at or near "'1'" LINE 1: ... "contacts".* FROM "contacts" WHERE (visible_to IN '1','2') ......` – arthur Aug 11 '14 at 12:18
  • 1
    P.S. And the output error at the end is `output error: # – arthur Aug 11 '14 at 12:31
  • 1
    seems like your visible_in column is integer. is that correct? \n please try Contact.where('visible_to in (?)', [1,2]) – Ajit Singh Aug 11 '14 at 18:02
  • I was thinking about that as well, but as far I understand from manuals I need to define a type of elements in array, right? I tried your suggestion and in this case I got: `output error: # – arthur Aug 12 '14 at 06:24
  • Contact.where(:visible_to => [1,2,3]) try this – Ajit Singh Aug 12 '14 at 18:49
  • Ajit, the result is `# – arthur Aug 13 '14 at 06:45
  • whats the datatype on for your visible_to field? – Ajit Singh Aug 13 '14 at 17:16
  • As I posted above it's `:visible_to, :integer, array: true` – arthur Aug 14 '14 at 07:12