Back an ecto model with custom sql instead of a table

Question

I've got this situation where I generate an intermediate table with some custom sql. In my app I have a model that points at that intermediate table. I've got this requirement to not use an intermediate table, so I'm trying to figure out how to write an Ecto model in such a way that it uses that custom sql on loading data for that model.

IMPORTANT: this model is only used for reading from that table or sql select results, so I don't have to support insert/update/delete. That should vastly simplify what I'm trying to do.

Here's a fake model for the purposes of what I want to do:

defmodule EventBridge.C3poEvent do
  use Ecto.Schema
  import Ecto
  import Ecto.Query, only: [from: 1, from: 2]

  schema intermediate_table_name do
    field :id, :integer
    field :access_dates, :string
    field :action, :string                 
    field :counter, :integer
  end
end

Let's assume this sql to get the data:

select id, access_dates, action, counter from some_other_table 
  where some_conditions = true;

What I need to be able to do is load models using that sql rather than from the table my example is backed by.

In my head I was thinking that I'd put a function in the model like:

def get_model(Model, some_conditions) do
  ...
end

and in that function just manually load the model with the sql in there. But I'm not convinced this a) makes sense or b) will result in a model I can use to access the fields.

Perhaps I shouldn't even be using a model? Just a custom struct that has the get_model method in it and not worry about backing it with the schema?

again, please not that I'm only reading this data ever.

Ecto.Schema is perfectly living without any real table behind; I often use Ecto.Schema for handy validations of structs. Besides that, I am not sure I follow what the issue is. Do you need it to be fully compliant with Ecto so that you might use it in complicated queries? — Aleksei Matiushkin
No, I just need to be able to load a set of rows and access them like I would any struct. (mystruct.id for example) I'm wondering if perhaps I just create a struct unrelated to the ecto model/schema mechanisms and have a method that executes the sql and maps it into the struct — jaydel
also, what does the code for the model schema look like if you don't back it with a real table? — jaydel
You can use embedded_schema if you want a schema not backed by a table hexdocs.pm/ecto/Ecto.Schema.html#embedded_schema/1 — nico

Aleksei Matiushkin Aleksei Matiushkin · Accepted Answer · 2018-06-28T05:13:34

If I properly understood your need, you just need an “upgraded” struct of a kind. That is easily achievable with Ecto.Schema.embedded_schema/1. I would provide an adopted from my production code example showing validations and other Ecto goodness:

defmodule EventBridge.C3poEvent do
  use Ecto.Schema
  import Ecto.Changeset

  @required_fields ~w|id access_dates action|
  @fields ["counter" | @required_fields]

  @primary_key false
  embedded_schema do
    field :id, :integer
    field :access_dates, :string
    field :action, :string                 
    field :counter, :integer
  end

  def new(data) when is_map(data) do
    %__MODULE__{}
    |> cast(data, @fields)                 # free from Ecto
    |> validate_required(@required_fields) # free from Ecto
    |> apply_changes()                     # free from Ecto
  end

  ## `to_string` / interpolation support
  defimpl String.Chars, for: EventBridge.C3poEvent do
    def to_string(term) do
      "🎫<[📅#{access_dates} 🎬#{action}(#{counter})]>"
    end
  end

  ## `inspect` support
  defimpl Inspect, for: EventBridge.C3poEvent do
    import Inspect.Algebra

    def inspect(%{
        id: id,
        access_dates: access_dates,
        action: action,
        counter: counter}, opts) do
      inner = [id: id, access_dates: access_dates,
               action: action, counter: counter]
      concat ["#EventBridge.C3poEvent<", to_doc(inner, opts), ">"]
    end
  end
end

The above is ready-to-use scaffold for any “upgraded” struct backed by Ecto. Once you have decided to store it in the database, just switch from embedded_schema to schema and you are all set.

Back an ecto model with custom sql instead of a table

1 Answers