I often bump into this problem with Ecto when building queries dynamically.
Let’s say we have a Post schema that belongs_to a Blog schema and there’s a search web page where users can search for posts. Not for posts under a particular blog, but for post on the whole site, from all the blogs. I usually add where clauses to my query step by step as I discover what conditions the user supplied. So the show story starts with a dumb query, like:
query =
from p in Post,
join: b in Blog, on: b.id == p.blog_id,
preload: [:blog],
order_by: [desc: p.inserted_at]
When, for instance, the user entered some subtext for the post title, I refine the query like this:
query = from p in query, where: like(p.title, ^"%#{title_portion}%")
This serves me well until the point I want to filter Posts based on a column from the Blog schema, because I cannot reference any join-ed or preload-ed columns. This is wrong:
query = from post in query, where: post.blog.status == "verified" # FIXME please
Is there any way to do this? I mean I can do this in a single query, but I just cannot figure out how to make it work when the query is built in more than one steps.