CODEWITHSUNDEEP
elixirecto
bigpotato
bigpotato

Reputation: 27537

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

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?

Upvotes: 18

Views: 7852

Answers (3)

Mitchell Jeppson
Mitchell Jeppson

Reputation: 306

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.

Upvotes: 0

Dennis
Dennis

Reputation: 59587

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)

Upvotes: 1

thousandsofthem
thousandsofthem

Reputation: 1365

Try

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

Upvotes: 26

Related Questions