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

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:

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) }

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

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)

# Works successfully:
|> 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!

TLDR; look into Named Bindings.

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


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)

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

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.


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)
      dynamic([b], b.available == true)

    where(query, ^new_query)

This allows me to successfully use it for querying books:

Book |> Book.available |> last |>

as well as within joins:

|> 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?