How to build WHERE IN array clause with Ecto?

Question

How to find posts in given List of ids?

This isn't working:

posts = Post |> where(id: [1, 2]) |> Repo.all

Example in Rails:

Post.where({ id: [1, 2]})
# SELECT * FROM posts WHERE id IN (1, 2)

Answer 1

The following should work:

posts = Post |> where([p], p.id in [1, 2]) |> Repo.all

Answer 2

Accepted answer gives undefined function p/0 to me, so I came to this:

from(p in Post, where: p.id in [1, 2]) |> Repo.all

Answer 3

Other posters gave both the "keywords" and "expressions" patterns needed but I wanted to comment and point out that if you are interpolating the values from a list, you need the ^ operator before the variable. You also need to be importing the module which contains the macros (special because macros have different compilation needs) before trying either of these. This is all with ecto 2.1.4, btw. So:

import Ecto.Query
...

id_list = [1,2,4,5,6]


# "expressions"

Post
|> where([p], p.id in ^id_list)


# "keywords"

from(p in Post, where: p.id in ^id_list)