Insert Variable in Ecto.Adapters.SQL.query

I have a big issue inserting a variable in a SQL query. Apparently the insertion method is using the $ sign, however I keep getting the Unknown column '$1' in 'where clause'.

What is the right way to insert this variable?

  def show(conn, %{"id" => department_id}) do
    query = "SELECT COUNT(*) FROM ticket t LEFT JOIN reply r ON t.id = r.ticket_id WHERE (r.id IS NULL OR r.id  = (SELECT id FROM reply ORDER BY id DESC LIMIT 1)) AND t.department_id = $1"
    convolutedOpenTickets = Ecto.Adapters.SQL.query(Repo, query, [department_id])
    openticket = Enum.at(Enum.at(convolutedOpenTickets, 0), 0)
    render(conn, "show.html", openticket: openticket)
  end

It looks good to me, I even tried it out on my machine, seems to work. Is this the exact same code as on your machine?

Yes, I am trying it using Elixir 1.5.0 and Phoenix v1.3.0-rc.3

mix phoenix server

And also

iex -S mix phoenix.server

I get this error in terminal

[debug] QUERY OK source="user" db=2.8ms decode=4.8ms queue=1.0ms
SELECT u0.`id`, u0.`name`, u0.`email`, u0.`password_hash`, u0.`is_admin`, u0.`inserted_at`, u0.`updated_at` FROM `user` AS u0 WHERE (u0.`id` = ?) [2]
[debug] Processing by UpdevsJs.DepartmentController.show/2
  Parameters: %{"id" => "1"}
  Pipelines: [:browser, :with_session, :login_required]
[debug] QUERY ERROR db=0.5ms queue=0.6ms
SELECT COUNT(*) FROM ticket t LEFT JOIN reply r ON t.id = r.ticket_id WHERE (r.id IS NULL OR r.id  = (SELECT id FROM reply ORDER BY id DESC LIMIT 1)) AND t.department_id = $1 ["1"]

Could be the error due to Phoenix Version?

I fonund the answer, the problem was that it shouldn’t be $1, it should be ?, in that way everything works ok!.

Thanks Tuxified for the support.

What’s strange is that $1 works for PostgreSQL, but only ? works for MySQL.

Yes, that’s strange @michael_teter