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

seva

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 comments

#ecto #troubleshooting #learning-elixir

0 1919 2

2022-10-21 19:16:56 UTC

Showing Posts 1 to 2

Show Best Posts
Show All (oldest first)
Show All (newest first)

MrDoops

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.

Post #1

seva

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?

Post #2

— All posts loaded —