Ecto Schema field with fragments

Questions / Help
1

Hi, is there any way to apply fragments or raw sql in a field Schema ?

I have some calculated data that prefer resolve inside Postgres without joins.

For example:

defmodule Table.Orders do
  use Ecto.Schema

  schema "orders" do
    sql,  :items_count, (select count(*) from items where items.id = this_Schema.id)
    field :client_name, :string
    field :discount, :float
    field :conditions, :float
    field :aproveed, :boolean

  end
end

Also need to apply fragment in a column of type polygon of Postgis:
(without geo_postgis)

I said fragment, but can be any macro with the desired behaviour.


defmodule Table.LandParcels do
  use Ecto.Schema

  schema "parcels" do
    field :name, :string
    fragment, :area, fragment("ST_Area(?::geography)/10000", "boundary")
    fragment, :boundary, fragment("ST_AsGeoJSON(?)", "boundary")
    fragment, :poly_count, fragment("ST_NumGeometries(?)", "boundary")

    and many more fields ....
    
  end
end

I know that it can be resolved with database views,
but the database administrator doesn’t let me touch anything.
And also i have others queries with more complexity.

Regards

2

Fragments and other sql things only work within queries so there’s no way to make this work with schemas.

If you want to query a specific table but don’t want its actual fields you can customize the select on your query.

i.e. instead of from l in LandParcels you can do:

  from l in LandParcels, 
    select: %{
      area: fragment("ST_Area(?::geography)/10000", "boundary"),
      boundary: fragment("ST_AsGeoJSON(?)", "boundary"),
      poly_count: fragment("ST_NumGeometries(?)", "boundary")
    }```
1 Like
3

Thank, and how will be resolved the next ?

defmodule Table.Orders do
  use Ecto.Schema

  schema "orders" do
    sql,  :items_count, (select count(*) from items where items.id = this_Schema.id)
    field :client_name, :string
    field :discount, :float
    field :conditions, :float
    field :aproveed, :boolean

  end
end

[/quote]

4

I resolve with a query:

query = from order in Orders,
left_join: item in assoc(order, :items),
select: %{order | items_count: count(item.id)},# items_count is a virtual field
group_by: order.id

Repo.all(query)
1 Like
5

Yeah the purpose of a schema is to essentially just describe your tables. To do transformations, aggregations, and similar you want to construct a query.

1 Like
6

A bit late and it needs migrations so may be a no go in your situation but for completness I think you may use a generated column and use it in your schema: