Ecto Schema field with fragments

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

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

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]

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

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