Raw SQL with Ecto

Question

I am very new to the world of Elixir and Phoenix Framework. I am trying to follow TheFireHoseProject tutorial, but have problems querying raw SQL with Ecto. The manual says this should work:

defmodule Queries do def random do query = Ecto.Adapters.Postgres.query( Repo, "SELECT id, saying, author from quotes ORDER BY RANDOM() LIMIT 1", []) %Postgrex.Result{rows: [row]} = query {id, saying, author} = row %Splurty.Quote{id: id, saying: saying, author: author} end end 

I get a runtime error that Ecto.Adapters.Postgres.query does not exist (function undefined).

I tried searching the Ecto documentation and found that there might be a run_query function, but it also does not work.

I think I am using Ecto 1.1.4 and I have not found any good (modern) samples of how I can query the source SQL with Ecto.

Link to the firehose project: http://phoenix.thefirehoseproject.com/

Answer 1

 alias Ecto.Adapters.SQL querystring = "..." result = SQL.query(Repo, querystring , []) 

And then you can add the result to the list, for example:

 list = [] case result do {:ok, columns} -> list = for item <- columns.rows do List.first(item) end _ -> IO.puts("error") end 

Answer 2

If you just need to add raw SQL to your regular normal Ecto query, you can use fragment / 1 :

 from q in Quote, order_by: fragment("RANDOM()"), limit: 1 

This is often enough, and it is much easier to handle. You can usually use the / 1 snippet in any part of an Ecto request. For a more enjoyable use, you can define a macro that allows you to fit well in Ecto DLS:

 defmodule QueryHelpers do defmacro random() do quote do fragment("RANDOM()") end end end 

And later use it:

 import QueryHelpers from q in Quote, order_by: random(), limit: 1