I had to do it for a few slightly complex CTEs, your best friend is Ecto.Query.subquery that transforms query composition into subquery expressions.

For example this CTE

with fast as (
	SELECT c2.city, st_distance(st_transform(c2."geom", 4326), point.geom) d 
	FROM "shapes"."cities" AS c2, point 
	WHERE st_intersects(st_transform(c2."geom", 4326), point.geom)
),
slow as (
	SELECT c2.citiy, st_transform(c2."geom", 4326) <-> point.geom d 
	FROM "shapes"."cities" AS c2, point
	WHERE st_transform(c2."geom", 4326) <-> point.geom <= 50.0/110574
)
select * from fast
UNION all
select * from slow
WHERE NOT EXISTS (
	select * from fast
)  
order by d
limit 1

Can be converted to

fast_query =
  from(m in module,
    where: st_intersects(st_transform(m.geom, 4326), st_makepoint(^lon, ^lat)),
    select: map(m, ^fields),
    select_merge: %{
      distance: st_distance(st_transform(m.geom, 4326), st_makepoint(^lon, ^lat))
    }
  )

count_query = from(f in subquery(fast_query), select: %{count: count()})

slow_query =
  from(m in module,
    join: c in subquery(count_query),
    where: in_range(st_transform(m.geom, 4326), st_makepoint(^lon, ^lat), 50),
    select: map(m, ^fields),
    select_merge: %{
      distance: st_distance(st_transform(m.geom, 4326), st_makepoint(^lon, ^lat))
    },
    where: c.count == 0
  )

fast = from(fast in subquery(fast_query))
slow = from(slow in subquery(slow_query))

from(
  q in subquery(
    from(fast in fast,
      union: ^slow,
      select: fast
    )
  ),
  order_by: q.distance,
  limit: 1,
  select: q
)

to print the generated SQL you can use

Ecto.Adapters.SQL.to_sql(:all, RepoModule, query)

EDIT: Ecto 3.2 already added support for CTE, ecto/CHANGELOG.md at master · elixir-ecto/ecto · GitHub but I find subqueries more readable in code, while CTE are usually better in SQL

Heh, TIL that Ecto finally allows subqueries in the source. Great, I was missing that for some time now.

And to update @kminevskiy - the approach by @massimo will be better as in PostgreSQL 9-11 the CTE was optimisation fence. Now it still is, but it can be lifted sometimes. So this approach will be better as it allows to bubble the filters up and down which allows for better optimisations.

I guess the problem is you’re using a subquery in the FROM part with generate_series, but ecto queries only support subqueries as from and join sources. Trying to compose subqueries and fragments will quickly become unwieldy, so I’d suggest sticking with the raw SQL unless you can refactor the query to have the generate_series be called differently, so ecto’s subquery handling can apply.