CODEWITHSUNDEEP
joinselectormelixirecto
Rudziankoŭ
Rudziankoŭ

Reputation: 11251

Ecto: select fields from two tables

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?

Upvotes: 1

Views: 1670

Answers (2)

Rudziankoŭ
Rudziankoŭ

Reputation: 11251

Finally, it seems, I come up with a simple solution:

fields_book =  [:code, :title_code, :publication_date,]
fields_author = [:name]

Book
|> join(:left, [b], a in assoc(b, :author))
|> select([b, a], merge(map(b, ^fields_book), map(a, ^fields_author)))
|> Repo.all()

Upvotes: 1

Aleksei Matiushkin
Aleksei Matiushkin

Reputation: 121000

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 docs: Defining custom functions using macros and fragment.

Upvotes: 1

Related Questions