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