Filtering across many different queries

I’m building a book website. I have a variety of pages that display a collection of books. For example, books about friendship, books written by Sandra Boynton, books rated by user John Doe, books featuring dinosaur characters. What I want to do now is let the user further refine these book collections through filters and sorts.

Currently, my queries are in several context modules (or controllers). Most make use of preloads to load the books while others query the books table directly.

# MyApp.Reader

def query_book_reviews(reader_id) do
  BookReview
  |> where([q], q.reader_id == ^id and q.rating > ^rating)
  |> BookReview.preload_book()
end

# MyAppWeb.CreativeController
def show(conn, params) do
  People.get_creative(id) 
  |> Creative.preload_books()
  ...
end

# MyApp.Library
def get_books_published_between(start_date = %Date{}, end_date = %Date{}, type, params) do
    Book
    |> Book.approved()
    |> Book.with_type(type)
    |> Book.published_between(start_date, end_date)
    |> Book.highest_rated_first()
    |> Book.preload_creatives()
    |> Repo.paginate(params)
  end

I want to support queries like:

  • All chapter [Books] books
    about friendship [BookTag.Tag]
    with a dinosaur [BookCharacter.Tag]
    written by a Black author [Creative.Tag]
    order by publication_date (Books)
    and don’t include more than one book from a series (Books)

Are there any established patterns for structuring my queries so that the books returned satisfy the defined filter?

Some of the tables I use are listed below to help you understand their relations

Books
- format (board, picture, chapter)
- series_id

BookTags
- book_id
- tag_id

Tags
- name

BookCharacters
- name
- gender

BookCharacterTags
- book_character_id
- tag_id

Creatives
- name

BookCreatives
- book_id
- creative_id

CreativeTags
- creative_id
- tag_id

Series
- name

Reader
- name

BookReview
- book_id
- reader_id
- rating

Is my question confusing or is there just not a well-known solution for this?

Hello and welcome,

It’s usually easier to provide some kind of MVP for question with complex environment to reproduce…

Anyway, Ecto is quite close to SQL, and your queries do not look complicate.

I hope You are not doing one context per controller, or schema…

Also queries are highly composable, how do You compose them?

I use a reduce method, to pack all queries into one… that can be processed.

There are some examples on the site on how to proceed.

Thanks! The reduce method looks like it could work well for simple queries made directly on the books table. How would that work with queries that preload the books either with a one to one or one too many join?

My contexts are bordering on too large rather than too small. I probably need to break them up. Right now I compose queries using helper functions that I have located in schema files.

# MyApp.Library.Book
def visible(query \\ __MODULE__), do: from(q in query, where: not q.hidden)
def approved(query \\ __MODULE__), do: from(q in query, where: q.approved)

You can see how I use them in my first post. I also default to adding additional functions in my contexts for different queries I want.

# MyApp.Library
def get_books_from_creatives, do: ...
def get_books_with_videos, do: ...
def get_books_recently_updated, do: ...
1 Like

You can compose with complex queries using joins… it is not only for simple queries.

Please provide something more concrete, because I have no clue about the queries You have difficulties with…

I would start by writing raw Sql queries, then I would translate to Ecto.

Let’s say I want to filter to books that have a particular tag. Something like below.

query = Books
  |> where([b], b.approved)
  |> join(:inner, [b], bt in BookTag, on: b.id == bt.book_id)
  |> join(:inner, [b, bt], t in Tag, on: bt.tag_id == t.id and t.slug == ^slug)

Now I want to do the same thing (get books with a particular tag), but this time, starting with the creative and doing a preload on their books.

People.get_creative!(slug)
|> Repo.preload(books: ???)

In another example, I want to get all books reviewed by a user, but again only those that have particular tag. This one confuses me a bit because the preload needs to also act like a filter to remove results with books that don’t have the required tag.

BookReview
|> where([br], br.user_id == ^id)
|> Ecto.Query.preload(book: ???)

Does this help?

1 Like

I would starts by book, joining book_review and user to make the query.

If I wanted all books reviewed by a user, I would start with…

from b in Book,
  join: br in BookReview, on...
  join: u in User, on...
  where u.id=^...

And not the other way around. (AKA loading user, then preloading books)

Then, I would build filter helpers… like reviewed_by, with_tags etc.

1 Like

This section of the ecto docs may be of interest.

Adapted to your use case it might look like the following:

  def filter_books(params) do
    Books
    |> join([b], assoc(b, :creatives), as: :creatives)
    |> join([b], bt in BookTag, as: :book_tag, on: b.id == bt.book_id)
    |> join([b, bt], t in Tag, as: :tag, on: bt.tag_id == t.id)
    |> join([b], br in BookReview, as: :book_review, on: br.book_id == b.id)
    |> order_by(^filter_order_by(params["order_by"]))
    |> where(^filter_where(params))
  end

  def filter_order_by("tag_desc"),
    do: [desc: dynamic([tag: t], t.name)]
    
  def filter_where(params) do
    Enum.reduce(params, dynamic(true), fn
      {"tag", name}, dynamic ->
        dynamic([tag: t], ^dynamic and t.name == ^name)
  
      {"reviewed_by", user_id}, dynamic ->
        dynamic([book_review: br], ^dynamic and br.user_id == ^user_id)
  
      {_, _}, dynamic ->
        # Not a where parameter
        dynamic
    end)
  end
1 Like

This looks like exactly what I need, thanks!

Is there anything fundamentally different between a compound where and multiple where clauses? For example:

Books
|> where([b], b.approved and b.rating > 4.0)
# vs 
Books
|> where([b], b.approved)
|> where([b], b.rating > 4.0)

Mostly I’m curious because I was going to use a where function inside of the reducer rather than dynamic where possible.

Not that I’m aware of. Multiple wheres are just joined with an AND clause so your two examples should be equivalent.