CODEWITHSUNDEEP
elixirphoenix-frameworkecto
wintermeyer
wintermeyer

Reputation: 8318

Fill virtual fields from a SQL query

I have to deal with a database setup I can not change and I have to use a specific SQL query which calculates values which are not fields in the table. How can I make this work in Ecto? Here is my approach and the problem I ran into:

The Setup

$ mix phx.new testapp
$ cd testapp
$ mix ecto.create
$ mix phx.gen.html Shops Product products name price:float
$ mix ecto.migrate

After that I create a couple of products.

The x

I add a virtual x field to the product:

lib/testapp/shops/product.ex

defmodule Testapp.Shops.Product do
  use Ecto.Schema
  import Ecto.Changeset

  schema "products" do
    field :name, :string
    field :price, :float
    field :x, :integer, virtual: true  # <-----

    timestamps()
  end

  @doc false
  def changeset(product, attrs) do
    product
    |> cast(attrs, [:name, :price])
    |> validate_required([:name, :price])
  end
end

And I add the following functions to Testapp.Shops:

def execute_and_load(sql, params, model) do
  result = Ecto.Adapters.SQL.query!(Repo, sql, params)
  Enum.map(result.rows, &Repo.load(model, {result.columns, &1}))
end

def list_products_with_x do
  sql = "SELECT *, 1 AS x FROM products;" # <- simplified
  execute_and_load(sql, [], Testapp.Shops.Product)
end

1 AS x and the whole SQL query is just a simplified example! In the real application I have to use a SQL query which calls stored procedures to make a calculation which will store the value in x. So there will be some sort of SQL which I can not create with Ecto itself. In case you are interested in the SQL: Overlapping gaps and islands in a school vacation setup

The Problem

The SQL query delivers the value for x for each entry but the product lists the x as nil. How can I solve this problem? How can I fill virtual fields in execute_and_load/3?

iex(1)> Testapp.Shops.list_products_with_x
[debug] QUERY OK db=1.3ms queue=2.2ms idle=8177.7ms
SELECT *, 1 AS x FROM products; []
[
  %Testapp.Shops.Product{
    __meta__: #Ecto.Schema.Metadata<:loaded, "products">,
    id: 1,
    inserted_at: ~N[2020-02-12 07:29:36],
    name: "Apple",
    price: 0.5,
    updated_at: ~N[2020-02-12 07:29:36],
    x: nil
  },
  %Testapp.Shops.Product{
    __meta__: #Ecto.Schema.Metadata<:loaded, "products">,
    id: 2,
    inserted_at: ~N[2020-02-12 07:29:47],
    name: "Orange",
    price: 0.75,
    updated_at: ~N[2020-02-12 07:29:47],
    x: nil
  }
]

I am open for alternative solutions for the given problem. I can not calculate the value of x within my Elixir program. I have to use SQL to calculate it and I want to use Ecto.

Upvotes: 3

Views: 1222

Answers (3)

Marc
Marc

Reputation: 582

I was able to do this using :

  result = Ecto.Adapters.SQL.query!(Repo, sql, params)
  x_index = Enum.find_index(result.columns, fn x -> x == "x" end)
  Enum.map(result.rows, fn row ->
     Repo.load(Product, {result.columns, row})
     |> Map.put(:x, Enum.at(row, x_index))
  end)

Upvotes: 0

Brett Beatty
Brett Beatty

Reputation: 5963

In my opinion you'd be better off getting your SQL to work with fragments.

Repo.all from p in Product, select: %{p | x: 1}

If you can't get that to work, Repo.load/2 can take a map instead of a schema.

data =
  :load
  |> Product.__schema__()
  |> Enum.into(%{x: :integer})
  |> Repo.load({columns, row})

struct(Product, data)

If you want to simplify that, you could override Product.__schema__(:load) and use your existing &Repo.load(model, {result.columns, &1}):

schema "products" do
  ...
end

# WARNING: This could have unintended effects
# You're probably better off not poking around in Ecto internals
defoverridable __schema__: 1
def __schema__(:load), do: [{:x, :integer} | super(:load)]
def __schema__(arg), do: super(arg)

Upvotes: 3

Daniel
Daniel

Reputation: 2554

A better approach to yours is to select everything you need to a struct and then move it to Ecto.Struct. My approach is the following:

def get_products() do
    query = from p in Products,
            select: %{name: p.name, price: p.price, x: fragment("1")}
    query
    |> Repo.all()
    |> Enum.map(fn el -> struct(Products, el) end)
  end

The advantage of this approach is the fact that I don't use raw string query. Your calculations should go inside of the fragment part.

Upvotes: 0

Related Questions