Is is possible to use Ecto.Query `from` without a model? I just want to run a SQL statement

Right now I’m doing this:

geom = %Geo.Polygon{...}
    c in Chat, 
    limit: 1,
    select: fragment("ST_Area(?::geography)", ^geom)

This works fine, as long as there’s at least 1 Chat model in the DB (which breaks in the test env with a sandboxed DB), however I don’t really care about Chat in this case, I’m really just running some SQL in the select statement.

I could do this:

q = "SELECT ST_Area($1::geography);"
Ecto.Adapters.SQL.query!(Repo, q, [geom])

But this doesn’t give me this nice result/nil response, instead it gives me:

  columns: ["st_area"],
  command: :select,
  connection_id: 27533,
  messages: [],
  num_rows: 1,
  rows: [[40083.15847147331]]

Yes, I know I can just grab the data out of the row property, but I was just curious if there’s a way to do this with an Ecto.Query or if I have to go the raw SQL route.

You don’t need Ecto schemas to query the db. But you will have to replace the schema name with a table name in quotes like this and also provide select clause:

from e in "expenses", limit: 2, select: [] |> Repo.all

1 Like

This doesn’t really fix the issue, because even here, the "expenses" needs to be a valid table, which has to have rows in the table to get a response.

If I did from(e in "expenses", select: "1"), i’d only get a 1 back, if there was at least 1 expense, I don’t want my query to be tied to the model/schema at all, I really just want to execute the code in the select.

To be honest I’d just use MyApp.Repo.query and wrap it in some helper (can even be on MyApp.Repo) to get the response in a format you want it to be. Any solution based on Ecto.Query will be more complex as you’re trying to do something it wasn’t exactly meant to provide.

That’s what I figured was gonna be the outcome, but I was hoping there was an Ecto.Query solution so I could use all it’s fancy helpers. Oh well, I only need to do it for a few queries. Thanks.

1 Like

You could look into Ecto.Query.with_cta, which allows fragments to define a source to query from. from/2 doesn’t support fragments in it’s first parameter.

1 Like

I just went ahead and wrote them using Repo.query, it wasn’t too bad:

def get_poly_area(geom) do
    q = "SELECT ST_Area($1::geography);"

    Ecto.Adapters.SQL.query!(Repo, q, [geom])
    |> case do
      %{rows: [[val]]} when is_float(val) -> val
      _ -> nil

  def get_poly_centroid(geom) do
    q = "SELECT ST_Centroid($1::geometry);"

    Ecto.Adapters.SQL.query!(Repo, q, [geom])
    |> case do
      %{rows: [[%Geo.Point{} = val]]} -> val
      _ -> nil

But I’ll check out with_cta.

If you’re only using sql for geo calculations you could also try an see if this one can do it in elixir:


maybe you can select “from dual” ? The postgres docs tells that it is defined as a view to help portability.

You could also look into using Repo.load. Heres an example:

result = Ecto.Adapters.SQL.query!(Repo, query, params)

types = %{type: :string, count: :integer}, &Repo.load(types, {result.columns, &1}))

This would spit out a format like

    %{type: "a", count: 2},
    %{type: "b", count: 7},
1 Like