Raw SQL with Ecto

10
votes

I'm very new in the world of Elixir and Phoenix Framework. I'm trying to follow TheFireHoseProject tutorial, but having problems with querying raw SQL with Ecto. The tutorial 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'm getting a runtime error that the Ecto.Adapters.Postgres.query doesn't exist (undefined function).

I tried to search the Ecto documentation and found that there might be a function called run_query, but it doesn't work either.

I think I'm using Ecto 1.1.4 and I didn't find any good (up-to-date) samples of how can I query raw SQL with Ecto.

The link to the firehoseproject is: http://phoenix.thefirehoseproject.com/

2
elixirphoenix-framework

2 Answers

11
votes
alias Ecto.Adapters.SQL

querystring = "..."

result = SQL.query(Repo, querystring , [])

And then you can add the result to a list for example like this:

list = []

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

If all you need is just to add a raw SQL to otherwise normal Ecto query you can use fragment/1:

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

It's often enough and it's much easier to handle. You can generally use fragment/1 in any part of Ecto query. For even nicer use, you can define a macro that will allow you to fit nicely into Ecto's 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