Query in rails with where array clause

Question

I have an array field, author_ids in books table, I need to write a query to find the array data.

Is there any way i can get, even if author_ids has [1,3] in its value, but when I check with array [1,5], I still could get the data because it has 1 common ? here('author_ids @> ARRAY[?]::integer[]',[1,5])

where('author_ids @> ARRAY[?]::integer[]',[1,5])

this doesnot return data, where('author_ids @> ARRAY[?]::integer[]',[1,3]) whis does because It has [1,3]. Iwant to get the data where [auther_ids] ? any [passed_aray]

Answer 1

TL;DR

Postgres documentation

Problem

anyarray @> anyarray → boolean

Does the first array contain the second, that is, does each element appearing in the second array equal some element of the first array? (Duplicates are not treated specially, thus ARRAY[2] and ARRAY[1,1] are each considered to contain the other.)

ARRAY[1,4,3] @> ARRAY[3,1,3] → t

That means if you have [1,3,7,9] for authors_ids and you query that with [1, 5] it's not going to return anything. Why?

@> checks if your query array is a subset of the column and [1,5] isn't a subset of [1,3,7,9] cause 5 is missing.

Solution

The operator you need is &&:

anyarray && anyarray → boolean

Do the arrays overlap, that is, have any elements in common?

ARRAY[1,4,3] && ARRAY[2,1] → t

Using that your query would be the following:

where('author_ids && ARRAY[?]::integer[]',[1,5])

Refactor

I think the correct would be to use a has_many association on your model for the Author class.

class CurrentModel < ApplicationRecord
  has_many :authors
end

And then your query would be the following:

joins(:authors).where(authors: { id: [1,5] }