How to write raw sql with in parameters?

Questions / Help
,
1

I have code like this:

    query = """
    select from_user_id, to_user_id from account_relationships ar
    where from_user_id in $1
    and to_user_id in $2
    and state in('avoid','block')
    """

    results = Ecto.Adapters.SQL.query!(Repo, query, [player_ids, player_ids])

player_ids is a list of ids.

I get the error:

** (Postgrex.Error) ERROR 42601 (syntax_error) syntax error at or near “$1”

What should I do differently? This is postgres query.

2

Hey @joshua.aug if you look at what Ecto generates for in it’s actually = ANY($1) which is what you should use here too:

    select from_user_id, to_user_id from account_relationships ar
    where from_user_id = ANY($1)
    and to_user_id = ANY($2)
    and state in('avoid','block')
1 Like
3

@joshua.aug, you don’t need to build the string yourself, you can use Ecto to do so (even if you don’t have schema modules):

player_ids = [1, 2]
from(ar in "account_relationships",
  select: [:from_user_id, :to_user_id],
  where:
    ar.from_user_id in ^player_ids and ar.to_user_id in ^player_ids and
      ar.state in ^[:avoid, :block]
)
|> Repo.all()
1 Like