CODEWITHSUNDEEP
ruby-on-railsrubypostgresql
carlos nuñez
carlos nuñez

Reputation: 61

Query for array type field on Ruby on Rails and PostgreSQL

I'm not sure how to do write a query. The issue:

I have two tables, authorities and notices each one with this column:

t.string "tags",   default:[],   array:true

I need to check if at least one element from the authorities array is in the notices array and save it on a class variable. So far I've tried this line of code in my controller:

@noticias = Notice.where('tags @> ARRAY[?]',current_authority.tags)

I tried something like this in the view:

<% @noticias.each do |notice| %>
    <% notice.nombre %>
<% end %>

EDIT

Thanks for you answers but my problem was 

ERROR:  operator does not exist: character varying[] @> text[]

the solution is:

@noticias = Notice.where('tags && ARRAY[?]::varchar[]',current_authority.tags)

As explained here If array contains value

Upvotes: 6

Views: 3363

Answers (1)

AmitA
AmitA

Reputation: 3245

You probably want to use the overlap operator rather than contain. The contain operator A @> B means A includes all element of B. If I understand what you're trying to do, you want to check whether the tag array of Notice contains any of the tags of the current authority.

You can do this like this:

@noticias = Notice.where('tags && ARRAY[?]', current_authority.tags)

Here is a link on all array functions in Postgres: http://postgresql.org/docs/current/static/functions-array.html

Upvotes: 6

Related Questions