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)

Solution 1

The following should work:

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

Solution 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

Solution 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)

Comments

[denis.peplin]

Returns undefined function p/0

[dcarneiro]

@denis.peplin, you have to import Ecto.Query. (don't forget to algo alias MyApp.Repo in order to do Repo.app

[pagep]

I do have import Ecto.query but getting variable "p" does not exist