Dynamically defining schema in function with schema macro

You can use a single schema with multiple tables: Ecto.Schema — Ecto v3.14.0

Thank you for the reply. Im not sure this solves the problem for 2 reasons. Maybe Im missing it?

1. Assume the database names are not known at compile time but might be generated ad hoc (only partially true)
2. assume there are a lot of them where making a schema per table does not make sense.

Can you clarify on this? What specifically wouldn’t make sense?

A lot of what Ecto provides is at compile-time, so runtime-definition like you’re thinking of is going to be fighting with the tool quite hard.

Thank you for the question! Even if I define a master schema, creating a schema module per table and defining the associations does not make sense. There could be hundreds of them and new ones will be added as time goes on… but they all share the same structure.

Then you should go for a custom Ecto.Type that is aware of the polymorphic pieces of data and properly branches on them to create the appropriate (different) Elixir structs.

If you can give us an example of a master and e.g. two “child” associations I believe we would be able to give you a head-start.

It would look something like this, if Im understanding correctly. So Im clear about my understanding of the following code. This will create a shared instance between 2 tables with the same fields… If that understanding is misapprehended please let me know!

EDIT: Id like to add here there are neither nor will there ever be fields that deviate from the same structure.

defmodule FieldsInCommon do
  use Ecto.Schema
  schema "fields_in_common" do
    field(:date, :string)
      field(:t1, :integer)
      field(:t2, :integer)
      field(:t3, :integer)

      timestamps()
  end

 def changeset(line, attrs) do
    line
    |> cast(attrs, [:date, :t1, :t2, :t3])
    |> validate_required([:date, :t1, :t2, :t3])
  end
end

defmodule AdHocTable do
  use Ecto.Schema

  schema "ad_hoc_gen_table_name" do
    many_to_many :fields, FieldsInCommon, join_through: "ad_hoc_gen_table_name_fields"
  end
end

defmodule AdHocTable2 do
  use Ecto.Schema

  schema "ad_hoc_gen_table_name_2" do
     many_to_many :fields, FieldsInCommon, join_through: "ad_hoc_gen_table_name_2_fields"
  end
end

Still not quite clear. I don’t see the value add of the second table.

Can you also give a few examples of actual data inside the DB? Just do a few select queries in psql and paste us the result here (with anonymized / edited values if you like)?

Its time series data from different sources. Putting them all in one table only stands to make handling the data arduous. They will all look the same as below.

id  |        inserted_at         |   data_timestamp    | length | count 
-----+----------------------------+---------------------+--------+-------
   1 | 2022-03-11 20:23:06.186993 | 2022-03-11 20:15:00 |  60000 |    31
   2 | 2022-03-11 20:23:06.191469 | 2022-03-11 20:16:00 |  60000 |    46
   3 | 2022-03-11 20:23:06.19321  | 2022-03-11 20:17:00 |  60000 |    42
   4 | 2022-03-11 20:23:06.194651 | 2022-03-11 20:18:00 |  60000 |    32
   5 | 2022-03-11 20:23:06.195871 | 2022-03-11 20:19:00 |  60000 |    30
   6 | 2022-03-11 20:23:06.196954 | 2022-03-11 20:20:00 |  60000 |    26
   7 | 2022-03-11 20:29:07.706952 | 2022-03-11 20:21:00 |  60000 |    32
   8 | 2022-03-11 20:29:07.717481 | 2022-03-11 20:22:00 |  60000 |    31
   9 | 2022-03-11 20:29:07.719433 | 2022-03-11 20:23:00 |  60000 |    32
  10 | 2022-03-11 20:29:07.721166 | 2022-03-11 20:24:00 |  60000 |    25
  11 | 2022-03-11 20:29:07.723034 | 2022-03-11 20:25:00 |  60000 |    20
  12 | 2022-03-11 20:29:07.724765 | 2022-03-11 20:26:00 |  60000 |    33

Are these the contents of the “fields_in_common” table, or “ad_hoc_*”?

ad_hoc_* there is no table fields_in_common its just that all the tables share all the same fields in common… Your question leads me to believe Im using it wrong.. ? Am I missing it?

Thank you for working with me!

Im looking over the Ecto.Type docs to try to anticipate your solution..