Writing ecto queries

Using Macro API for ecto queries

from(p in Post,
      join: bp in BranchPost,
      on: bp.branch_id == p.id,
      join: bl in BranchLocation,
      on: bl.location_id == bp.location_id,
      order_by: [p.name, p.type],
      where: bl.status_id == ^status_id,
      distinct: true
    |> Repo.all(prefix: account)

This I tried to write in form of Macro API (Expressions)

|> join(:inner, [p], bp in BranchPost, on: bp.post_id == p.id)
|> join(:inner, [bp], bl in BranchLocation, on: bl.location_id == bp.location_id)
|> where([bl], bl.status_id == ^status_id) 
|> order_by([p], [p.name, p.type])
|> distinct(true)
|> put_query_prefix(account)
|> Repo.all()

This doesn’t seem to work and I know that it is because of my second join and where clause. How to do multiple joins? As far as I see in the docs, there is example for joining two tables but not three tables using macro API. Any guidance is appreciated.

There are examples in docs. Please read about positional bindings and named bindings