Dynamic Ecto queries referencing joined tables

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.

You can do like this:

query = from [post, blog] in query, where: blog.status == “verified”

2 Likes

Yep, and in the next ecto version you may even be able to ‘name’ the joins too so you don’t have to worry about order. :slight_smile: