Ecto: select fields from two tables

Question

I have following function:

  def get(fields_from_book, fields_from_author) do
    Books
    |> join(:left, [b], a in Author, on: b.id == a.book_id)
    |> select([b, a], fields_from_book, fields_from_author)
    |> Repo.all()

I am interested in line select([b, a], fields_from_book, fields_from_author). Is there any way in Ecto to specify two arrays of fields from two tables separately?

Answer 1

Yes, this is possible with a help of custom macro. The approach would be more or less the same as in Ecto.Query.dynamic/2.

def query(fields_from_book, fields_from_author) do
  from b in Book,
    join: a in Author,
    on: on: b.id == a.book_id,
    select: ??? # {b.title, a.name}

Now we need to build the select expression with a macro. Let’s see what do we expect to get back.

iex> quote do: {a.name, b.title, b.price}
#⇒ {:{}, [],
#   [
#     {{:., [], [{:a, [], Elixir}, :name]}, [no_parens: true], []},
#     {{:., [], [{:b, [], Elixir}, :title]}, [no_parens: true], []},
#     {{:., [], [{:b, [], Elixir}, :price]}, [no_parens: true], []}
#   ]}

OK, let’s implement this (below I assume the fields list is passed as a compile-time literal, e. g. the list.)

defmacrop select_many(books, authors) do
  all =
    Enum.map(books,
      &{{:., [], [{:b, [], Elixir}, &1]}, [no_parens: true], []}) ++ 
    Enum.map(authors,
      &{{:., [], [{:b, [], Elixir}, &1]}, [no_parens: true], []})

  quote do: {unquote_splicing(all)}
end

and use it as:

def query(fields_from_book, fields_from_author) do
  from b in Book,
    join: a in Author,
    on: on: b.id == a.book_id,
    select: select_many(fields_from_book, fields_from_author)

Untested, but it should work.

---

Excerpt from ecto docs: Defining custom functions using macros and fragment.