Elixir + Ecto: How to do WHERE NOT IN [array]?

Question

I am trying to look for all Users that don't have a certain string element in their match_history field. I took a guess with this:

matched_user = User |> where([u], ^device_id not in u.match_history) |> limit(1) |> VideoChat.Repo.one

But it seems to break at the not part. Is there a way to do this?

Comenius

Answer 1

Try

User |> where([u], not ^device_id in u.match_history)

Greenery

Answer 2

For those who are looking for a "array does not contain any" behaviour.
For example "match_history does not contain device_1 device_2, device_3"

Given you're using PostgreSQL, can use a fragment with the array contains @> operator.

from t in queryable, where: not fragment("? @> ?::varchar[]", t.match_history, ^device_ids)

Or

from t in queryable, where: fragment("NOT ? @> ?::varchar[]", t.match_history, ^device_ids)

Summitry

Answer 3

As of Elixir 1.7+ you should use:

User |> where([u], ^device_id not in u.match_history).

If you use the accepted answer you will get a compile time warning.

Cleanlimbed