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

Questions / Help
1

Right now I’m doing this:

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

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:

%Postgrex.Result{
  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.

2

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: [e.id] |> Repo.all

1 Like
3

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.

4

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.

5

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
6

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
7

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
    end
  end

  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
    end
  end

But I’ll check out with_cta.

8

If you’re only using sql for geo calculations you could also try an see if this one can do it in elixir: https://github.com/yltsrc/geocalc

2 Likes
9

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

10

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


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

types = %{type: :string, count: :integer}

Enum.map(result.rows, &Repo.load(types, {result.columns, &1}))

This would spit out a format like

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