How to do 'where model.field contains array' in Ecto?

Question

Let's say I have a model with a field sizes that is an array (e.g. sizes: ['S', 'M', 'L']). What I want to accomplish, building an API, is for a user to be able to filter those models based on sizes. So a GET request to this path:

.../products?sizes=['S','M']

Should return all the products for which the given array is a subarray of their sizes field. So I don't want nor need an exact match but I want the users to be able to filter as explained above. How would I go about accomplishing this in my Phoenix API ?

I could only accomplish filtering the ones that contained a specific value (where: this in that), but if I pass in an array and I wanna check if that array is contained in that model field, I'm a bit lost.

Thanks in advance for any help, let me know if you need any additional information.

EDIT

I am trying to use fragment("? @> ?::varchar[]", p.sizes, ^params["sizes'] ) and it works, but it fails if I add any simple filter like [color: "red"] on top of the existing one, this means I can't create a set of filters and then add it to the where clauses like ... and ^filters

filters = Ecto.Changeset.cast(%Product{}, params, [], [:color])
  |> Map.fetch!(:changes)
  |> Map.to_list

# Do I need to actually do this check like this ? (It fails otherwise)
sizes = if is_nil(params["sizes"]) do [] else params["sizes"] end

products_query = from(
  p in Product,
  where: fragment("? @> ?::varchar[]", p.sizes, ^sizes) and
         ^filters
)

This doesn't currently work.

Answer 1

As you've figured out already, you need to use @> with fragment for the "array contains array" operation. For your second question, to chain where expressions with and, you can just add another where:

products_query = from(
  p in Product,
  where: fragment("? @> ?::varchar[]", p.sizes, ^sizes),
  where: ^filters
)