I have setup a PostgreSQL array field identical to this example on edgeguides. I am querying on these fields like:
Book.where("'fantasy' = ANY (tags)")
But what I need is to query the inverse of this; all records where tags does not include 'fantasy' (in this example).
Anyone have any guidance? I cannot find much documentation on working with a PostgreSQL array field outside of the aforementioned guide.
You can negate your condition
Book.where("NOT('fantasy' = ANY (tags))")
So you can modify query to get records with NULL records also:
Book.where("NOT('fantasy' = ANY (tags)) or tags IS NULL")
Also you can run these queries in psql and check results
SELECT * FROM book WHERE NOT('fantasy' = ANY (tags));
SELECT * FROM book WHERE NOT('fantasy' = ANY (tags)) OR tags IS NULL;
SELECT * FROM book WHERE 'fantasy' = ANY (tags)
Maybe there is no records without tag 'fantasy'?
archived_by which contains an array value; however, I have plenty records where the equivalent "tag" is blank: user.conversations.last(10).map(&:archived_by) => [nil, nil, nil, nil, nil, nil, nil, nil, nil, [359034]]Try this:
Book.where.not('tags @> ARRAY[?]', "fantasy")
