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]
CodePudding user response：
TL;DR
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] }