How to query to check any array elements from left matches right array

Questions / Help
1

I have a table with a pgsql column existing_ids of type uuid[] – specified as {:array, Ecto.UUID} in the schema.

The below composable query function is to filter UUIDs from the existing_ids column.

I have a hard time trying to refactor the function to avoid using Ecto.UUID.dump to convert the string uuid into binary uuid before being used in the query.

Is there an easier way to use psql casting in the fragment and avoid the Ecto.UUID.dump?

def filter_ids(query, ids) when is_list(ids) do
  id_list =
    for id <- ids do
      {:ok, uuid} = Ecto.UUID.dump(id)
      uuid
   end

  from q in query,
    where: fragment("? && ?", existing_ids, ^id_list)
end
2

What is the error you get when you don’t dump them?

What about something like:

where: fragment("? && ?::uuid[]", existing_ids, ^id_list)

Or something like that, whatever casting to an array was like (pretty sure that is right? Array casting is not something I do often ^.^; ).

1 Like