Writing a Subquery in Ecto

Here's how to write a subquery in Ecto using the in operator.

Subqueries allow you to embed a query inside of another query. This is helpful for building up data for the purpose of your main/outer query. A classic example is, "Select me objects that match the object ids in this subquery," where that subquery defines any number of filters or other sources. In this example, the in operator is also used.

Let's say I have an sql query that uses a subquery like this:

select f.name
from floop f
where f.id in (
  select f.id
  from floop f
  join floop_pop fp on f.id = fp.floop_id
  join pop ph on ph.id = fp.pop_id
  where fp.status = 'hyped'
  and ph.slug = 'mega');

We can translated that to an Ecto query like this:

inner_query =
  from f in Floop,
    join: fp in assoc(f, :floop_pop),
    join: ph in assoc(fp, :pop),
    where: fp.status == :hyped and ph.slug == "mega",
    select: f.id

outer_query =
  from f in Floop,
    where: f.id in subquery(inner_query),
    select: f.name

Ecto.Repo.all(outer_query)

See, innies and outies can get a long. Now, how nested will you go?!