5

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

Community
  • 1
  • 1
carlos nuñez
  • 51
  • 1
  • 3
  • don't store arrays in DB. ever – Andrey Deineko Nov 13 '15 at 21:48
  • @AndreyDeineko: Why not? SQL supports arrays, PostgreSQL supports arrays, and so does the Rails4 PostgreSQL interface. It isn't like he's using the `serialize` abomination. – mu is too short Nov 13 '15 at 21:53
  • @muistooshort I never tried to refute it. I just have experience, which showed me that there is always (there should have been written 'almost always', but in my case it is always) a better way, namely associations. – Andrey Deineko Nov 13 '15 at 21:56
  • Denormalization is a common practice when you want to optimize for query time. There is nothing wrong with it, you're just trading off the benefits of normalization. Querying is easy and fast, but deleting / updating is hard - If you want to delete or update a tag, you'll have to go over all parent records and update them. If you want to know usage statistics of tags, this would be hard too. But if you just want to see the tags for a parent and nothing more, storing it in an array is not bad. – AmitA Nov 13 '15 at 21:58

1 Answers1

5

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

AmitA
  • 3,239
  • 1
  • 22
  • 31