Complex Ecto association

Questions / Help
1

How would you design this schema?

Note that the schema for SPI type to SPI type has a large amount of variation from schema to schema thus its not ideal to have just one db table. But at the end of the day they represent the same construct in my system in that they are all of a type of “SPI”

defmodule SPI1 do
  schema "spi_1s" do
    field(:foo, :string)
    field(:bar, :string)
    field(:baz, :string)
    belongs_to(:user, User)
  end
end

defmodule SPI2 do
  schema "spi_2s" do
    field(:X, :string)
    field(:Y, :string)
    field(:Z, :string)
    belongs_to(:user, User)
  end
end

defmodule USER do
  schema "users" do
    # Q: Is there a polymorphic way to handle the has_many side of multiple types of spis?
    # It would be ideal if I could query all spis (spi_1 and spi_2) in one list without rolling
    # that logic by hand. 
    has_many(:spi_1s, SPI_1)
    has_many(:spi_2s, SPI_2)
  end
end
1 Like
2

Maybe you can create a view (with a union for both spi tables) in the database, and then a schema for it?

2 Likes
3

Are there any performance concerns with that approach?

IE large amount of writes vs indexing ect…

4

Some say there are, some say there aren’t. https://dba.stackexchange.com/questions/151169/are-views-harmful-for-performance-in-postgresql

^^^ in that thread I trust more those who say there aren’t.

And I don’t think a view would affect indexing, since it’s just the same old query, but with a name.

5

So I would take it there is nothing at the ecto side of things to aid in this task.

6

I create a view in a migration, and then use a schema for it as it was a table.

Here’s an example of a “schema” view

Here’s an example of a migration

1 Like
7

Does that still allow for a way to declare their unique types? IE I can tell which are spi_1 vs. spi_2 though I joined their query into one list?

8

It would depend on how you write the query/view. If you use a UNION (or anything else, but I usually just use unions), you can add an extra field to each that would indicate the source of the row.

2 Likes
9

This is starting to shape out as being very informative, thank you :slight_smile:

10

You might also be interested in doing something like "abstract table: spis" in ecto https://hexdocs.pm/ecto/Ecto.Schema.html#belongs_to/3-polymorphic-associations to define schemas for tables that are very alike.

1 Like
11

I’m not sure how that would work since my associations are reversed on the has_many side.