Ecto left in right

from p in Post, where: in [1, 2, 3]

if is :integer it works, but if i get {:array, :integer} i get error, how i can compare two integer arrays?

Do you know what the SQL equivalent of what you want to do is? i.e. are you looking for a subset or overlap of the two arrays?

I need check all values from list in where query.

My Schema:

  schema "accounts" do
    field :groups, {:array, :integer}

group = [1, 2, 3]

query = from a in Accounts, where: ^group in a.groups #i knowe, i can't pass list..

Solution is enum list, example;

query = from a in Accounts, where: ^1 in a.groups, where: ^2 in a.groups, where: ^3 in a.groups

But i don’t have any idea how do it :smiley:

query = from a in Accounts, where: fragment("? @> ?", a.groups, ^group)


PostgreSQL: Array Functions and Operators


As another option to using the postgres array functions (which will be more performant) if you instead wanted a more direct translation of your code you could incrementally build up the query using Enum.reduce:

group_ids = [1, 2, 3]
queryable = from a in Accounts
Enum.reduce(group_id, queryable, fn group_id, queryable ->
  |> where([a], ^group_id in a.groups)