Deconstructing CTE into an Ecto query

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.

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.

Thanks for your detailed example, will definitely give mine a try later today!

OK, here’s an update:

The query I managed to compose is this:

from c in company_report_date, 
  right_join: dates in fragment("select date(d) from generate_series((?)::date, current_date, interval '1 day') d where extract('ISODOW' from d) < 6", ^min_date), 
  on: c.report_date == dates.date, 
  where: is_nil(c.report_date),
  select: %{missing_date: dates.date}

However, the resulting execution returns an empty set. To test it, I converted my original SQL query to the one matching that new Ecto query and it returns the same result. So there has to be something wrong with my new Ecto query.

Just a quick update. Here’s how the final query looks like in my case. Not sure if that’s the best / most efficient way of doing it, but hey, it works and makes sense when I read the code

scoped_historical_reports_query =
  from hr in HistoricalReport, where: hr.company_id == ^company_id, select: [:report_date]

final_query =
      from hr in subquery(scoped_historical_reports_query),
        right_join:
          dates in fragment(
            "select date(d) from generate_series((?)::date, current_date, interval '1 day') d where extract('ISODOW' from d) < 6",
            ^min_date
          ),
        on: hr.report_date == dates.date,
        where: is_nil(hr.report_date),
        where: dates.date not in ^holiday_days,
        where: dates.date != fragment("current_date"),
        select: %{missing_date: dates.date}