Search code examples
sqlelixirecto

Passing raw SQL query as an argument to Repo.all() in Ecto

Ecto.Adapters.SQL.query(Repo, "Select * from table", []) seems to execute the query and returns the data. Is there a way to define a query based on raw sql such that it can be passed to Repo.all() as an argument?

I'm looking for something like,

qry = Ecto.Adapters.SQL.query("select * from table", [])  # This doesn't work
Repo.all(qry)
Solution

  • You cannot pass raw sql to Repo.all like this.
    Best you can do is pass some unsupported database function as fragment or find workaround.

    #UNION ALL
iex(1)> result1 = Model1 |> select([m], m.title) |> Repo.all
["a", "b"]
iex(2)> result2 = Model2 |> select([m], m.title) |> Repo.all
["a", "c"]
iex(3)> result1 ++ result2
["a", "b", "a", "c"]

#UNION
iex(1)> result1 = Model1 |> select([m], m.title) |> Repo.all
["a", "b"]
iex(2)> result2 = Model2 |> select([m], m.title) |> Repo.all
["a", "c"]
iex(3)> (result1 ++ result2) |> Enum.uniq
["a", "b", "c"]

#UNION USING RAW SQL
iex(1)> query = "select title from models1 union select title from model2"
...
iex(2)> {:ok, %Postgrex.Result{columns: columns, rows: rows}} = Ecto.Adapters.SQL.query(Repo, query, [])
...
iex(3)> rows |> Enum.map(&Enum.zip(columns, &1)) |> Enum.map(&Enum.into(&1, %{})) 
[%{"title" => "a"}, %{"title" => "b"}, %{"title" => "c"}]