How to add fields of associated schema to fields of parent schema when querying with Ecto?

Phoenix Forum Questions / Help
, , ,
1

Context
I have several different types of books that are stored in my database. All books have some properties in common, but all book types have their own unique properties as well. Currently, I have a schema for the shared properties of books (books) and one for the non-shared properties of each type of book (type_a_books, type_b_books, etc).

  schema "books" do
    field :title, :string
    ...
    field :type, :string # "TypeABook", "TypeBBook", etc.

    has_many :publications, Publication
    many_to_many :authors, Author, join_through: "authors_books"

    has_one :type_a_book, TypeABook
    has_one :type_b_book, TypeBBook

    timestamps()
  end

  schema "type_a_books" do
    field :story, :string
    field :pictures, {:array, :string}
    belongs_to :book, Book
  end

  schema "type_b_books" do
    field :paths, {:array, :string}
    field :pictures, {:array, :string}
    field :videos, {:array, :string}
    belongs_to :book, Book
  end

I setup this model recently but I am now faced with the fact that I do not have a sufficient understanding of how to perform the needed queries. I have read the documentation of Ecto.Query, Ecto.Repo and Ecto.Schema, and learnt from several other sources.

Problem
I have not been able to figure out how to get the following data back from the database (N.B. the fields of TypeABook are joined to the fields of Book).

%{
  id: "qwerty",
  title: "A Purple Sky over Tokyo",
  ...
  type: "type_a_book",
  publications: [ #App.Publications.Publication<...> ],
  authors: [ #App.Authors.Author<...> ],
  story: "hello",
  pictures: ["url", "url", "url"],
  inserted_at: ~U[2023-02-20 13:06:54Z],
  updated_at: ~U[2023-02-20 13:06:54Z]
}

The closest I got to this result is a simple query = from d in Draft, preload: [:authors, :publications, :type_a_book]. This, however, does not add the fields of TypeABook to the fields of Book. I have also tried to use join in assoc() and join in TypeABook, but I could never get it to work fully.

Feel free to also comment on my model or the general approach I am taking.

2

Here is one approach that might work ?

And a previous forum post/pointer from @LostKobrakai - Data mapping and validation — Ecto v3.9.4

1 Like
3

I am glad I read this now. Very informative. Thank you.

I think for my use case (N.B. referring to a part of my use case that is not described in my question) I will end up with a ton of virtual fields, however. Might not be the best alternative then.

I might just preload the non-shared fields into the query and use the resulting Book struct in my application (see below). I find it hard, however, to oversee what potential disadvantages that has done the line – and whether there is a more sensible alternative.

%App.Books.Book{
  id: "qwerty",
  title: "A Purple Sky over Tokyo",
  ...
  type: "type_a_book",
  publications: [ #App.Publications.Publication<...> ],
  authors: [ #App.Authors.Author<...> ],
  type_a_book: #App.Books.TypeABook<...>,
  inserted_at: ~U[2023-02-20 13:06:54Z],
  updated_at: ~U[2023-02-20 13:06:54Z]
}
4

If your read and write side look rather different you can also go with different schemas fror them. Noone requires you do use the same ones.

1 Like
6

@milangupta Your reference about select_merge helped me get it to work. As I learn more about Ecto, and querying in general, I hope to find a better solution. But at least it was a instructive day today! Thanks.

    query =
      from(
        b in Book,
        join: t in subquery(TypeABook),
        on: t.book_id == book.id,
          select_merge: %{story: t.story},
          preload: [:authors, :publications]
      )
    Repo.get(query, id)

  schema "books" do
    field :title, :string
    ...
    field :type, :string # "TypeABook", "TypeBBook", etc.

    has_many :publications, Publication
    many_to_many :authors, Author, join_through: "authors_books"

    has_one :type_a_book, TypeABook
    has_one :type_b_book, TypeBBook

    field :story, :string, virtual: true
    field :pictures, {:array, :string}, virtual: true
    field :paths, {:array, :string}, virtual: true
    field :videos, {:array, :string}, virtual: true

    timestamps()
  end
7

Glad it was helpful. It is a learning journey. I’m on my 3rd refactoring / re-design. What I love about the stack is that it is easy for a newbie to create something and then as you learn, you can really create some elegant stuff.

Your choices will really depend on the complexity of your application. There is still more to do with Ecto, but for simple/medium complexity stuff, it is great ! An example where I am still learning … in designing the model with assocs (has_many/belongs_to etc.), the stuff that I still have to test thoroughly is around when a record is deleted (and the behavior of cascading deletes), specifically when a value is NULL. Postgres15’s addition of NULL not unique was a very very needed enhancement that was previously causing me a lot of workarounds etc.

Hope this helps …

1 Like