Ecto: compose join query via reusable functions on the join table

Questions / Help
, ,
1

I’m new to Ecto and Elixir, so sorry if this is something obvious.

I’m trying to filter on the join, e.g. get all authors with available books:

SELECT *
FROM "authors"
    INNER JOIN "books"
        ON "books"."author_id" = "authors"."id"
WHERE "books"."available" = TRUE;

Coming from Rails, this is something that’s fairy easy to achieve by merging a named scope into the join:

class Book < ApplicationRecord
  belongs_to :author
  scope :available, ->{ where(available: true) }
end

class Author < ApplicationRecord
  has_many :books
  scope :with_available_books, ->{ joins(:books).merge(Book.available) }      
end

Calling Author.with_available_books, produces the required query, and I can reuse Book#available for Book queries as well.

I was able to construct initial sql with Ecto successfully:

from a in Author,
  join: b in assoc(a, :books),
  where: b.availabile == true)

However, I now need to refactor the b.availabile == true into Book module, so I can reuse it both for Author joins and queries on the Books.

Following the Dynamic query docs, I was able to partly solve this:

defmodule Hello.Book do
  def available, do: dynamic([books: b], b.available == true)
end

# Works successfully:
Author
|> join(:inner, [a], assoc(a, :books), as: :books)
|> where(^Book.available)

However, getting available books (e.g. where(Book, ^Book.available)) throws:

** (Ecto.QueryError) unknown bind name `:books` in query:

from b0 in Hello.Book

…unless I remove the named binding from the composable function (in which case I can no longer use it on the join):

def available, do: dynamic([b], b.available == true)

Any pointers on how to get the composable query to play nice with both use cases would be appreciated!

2

TLDR; look into Named Bindings.

You can conditionally check and join in your required joins within a function using has_named_binding/2

e.g.

def base_query(), do: from(a in Author, as: :author)

def with_books(query \\ base_query()) do
  join(query, :inner, [author: a], assoc(a, :books), as: :books)
end

def where_books_available(query) do
  if has_named_binding?(query, :books) do
    where(query, [books: b], b.available == true)
  else
    query
    |> with_books()
    |> where([books: b], b.available == true)
  end
end

I usually try to avoid using Dynamic until the problem gets complex (dynamic field selection + joins + complex subquerying, etc) and prefer composing with pipe-able Ecto query functions when re-usability is desired.

2 Likes
3

Thanks for the reply and for sharing the example @MrDoops.

Unfortunately, the example keeps book’s query logic (i.e. b.available == true) within Authors#where_books_available, so I’m unable to reuse it to query Books.

The example also repeats the query bit b.available == true twice. In my real case I have many decorating functions similar to available/0 in Books that I need to chain and reuse together for either querying Books directly or as part of a join in Authors and other related tables. So the solution needs to be DRYer.

I now managed to use has_named_binding? to workaround my initial issue:

defmodule Hello.Book do
  def available(query) do:
    new_query = if has_named_binding?(query, :books) do
      dynamic([books: b], b.available == true)
    else
      dynamic([b], b.available == true)
    end

    where(query, ^new_query)
  end
end

This allows me to successfully use it for querying books:

Book |> Book.available |> last |> Repo.one

as well as within joins:

Author
|> join(:inner, [a], assoc(a, :books), as: :books)
|> Book.available

However, my implementation of available/1 seems like an overkill, as there’s again repetition of the query logic (b.available == true). Plus there are many other similar composable functions, so suddenly a 1-line function becomes 7.

So wondering if there might be a neater solution?