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