Doing a Repo.all/1 on query with multiple unrelated structs doesn’t seem to come out of the box for Ecto. But we still might want to put numerous entities into a list while keeping the possibility to order, paginate and filter over it. Which can be useful in a search or a general index page.

Doing this when there is a parent entity isn’t that difficult anymore. We take the parent. Do a few left joins on the child entities we want to list and order, paginate and filter away.

0
1
2
3
4
5
6

Parent
|> join(:left, [p], c1 in FirstChild, on: p.id == c1.parent_id)
|> join(:left, [p], c2 in SecondChild, on: p.id == c2.parent_id)
|> order_by(inserted_at: :desc)
|> where(^filter)
|> preload([:first_child, :second_child])
|> Repo.paginate(page)

But this is harder when we don’t have a parent, and introducing one seems weird. To help here, we can implement a database view with an Ecto schema to list the entities. We can use an example with Books and Movies that can be shown in one list. First, let’s create a database view called entertainment:

defmodule MyApp.Repo.Migrations.CreateEntertainmentView do
  use Ecto.Migration

  def change do
    execute("""
      CREATE VIEW entertainments AS
        SELECT
          coalesce(books.id, movies.id) as id,
          movies.id AS movie_id,
          books.id AS book_id
        FROM (VALUES ('movie'), ('book')) AS t (view_table)
        LEFT JOIN movies ON t.view_table = 'movie'
        LEFT JOIN books ON t.view_table = 'book'
        WHERE id IS NOT NULL;
      """,
      """
      DROP VIEW entertainments;
      """
    )
  end
end

The view entertainments has been created with the values statement as the from table. This will start us off with a column called view_table and two rows: book and movie.

0
1
2
3
4
5

SELECT * FROM (VALUES ('movies'), ('book')) AS t (view_table);

 view_table
---------
 movies
 book

With this column, we join the existing tables movies and books by adding:

0
1

 LEFT JOIN movies ON t.view_table = 'movie'
 LEFT JOIN books ON t.view_table = 'book'

Now we have selected the tables we want and we can look at the select statement. In this view, we only need the ID of the schema and a foreign key ID that can be used for the belongs_to/3 in the Ecto Schema. If you want this in a materialized view for search, titles and external IDs can be added with coalesce (since each row will be either a book or a movie). The following lines could be added for example.

0
1
2
3
4
5

SELECT
  coalesce(books.title, movies.title) AS title,
  coalesce(books.description, movies.description) AS description,
  coalesce(books.isbn_10, movies.isan) AS external_id,
  coalesce(books.inserted_at, movies.inserted_at) AS inserted_at
/*...*/

The last part of the query in the migration is the WHERE id IS NOT NULL line. This has to do with our FROM clause that starts with a row for each entity that we define. If there aren’t any rows from the joined tables these rows stay empty and are hereby removed.

Now that the migration is done and the view is created we can add an Ecto Schema with the original query. We only need to define the schema with the two relations to book and movie.

0
1
2
3
4
5
6
7
8

defmodule MyApp.Entertainments.Entertainment do
  use Ecto.Schema
  import Ecto.Query

  schema "entertainments" do
    belongs_to(:book, MyApp.Entertainments.Book)
    belongs_to(:movie, MyApp.Entertainments.Movie)
  end
end

This schema can now be used to query for both books and movies and the query will look the same as the parent-child query. To return the books and movies from our queries we preload them both.

0
1
2
3
4
5
6

Entertainment
|> join(:left, [e], b in Book, on: e.book_id == b.id)
|> join(:left, [e], m in Movie, on: e.movie_id == m.id)
|> order_by([b, m], coalesce(b.inserted_at, m.inserted_at)
|> where([b, m], coalesce(b.title, m.title))
|> preload([:book, :movie])
|> Repo.paginate(page)