Express a CTE using Arel

Question

I have this snippet of ActiveRecord:

scope = Listing.where(Listing.arel_table[:price].gt(6_000_000))

The resulting sql:

SELECT listings.* FROM listings where listings.price > 6000000

I would like to add a CTE that would result in this sql:

WITH lookup AS (
    SELECT the_geom FROM lookup WHERE slug = 'foo-bar'
)

SELECT * from listings, lookup
WHERE listings.price > 6000000
AND ST_within(listings.the_geom, lookup.the_geom)

I would like to express this sql inclusive of the CTE using Arel and ActiveRecord.

I would also like to use the scope variable as a starting point.

Simmer

Answer 1

You can create the CTE like:

lookup = Arel::Table.new(:lookup) # Lookup.arel_table
cte = Arel::Nodes::As.new(lookup,
  lookup.where(lookup[:slug].eq('foo-bar')).project('the_geom'))

and then use it with your scope like:

scope.with(cte)

You can see an example for this in the Arel README, at the very bottom

Achitophel

Trying this, it results in a missing from. ie. just SELECT * FROM listings WHERE not SELECT * FROM listings, lookup WHERE – Simmer

Got this working by adding a joins: scope.joins('CROSS JOIN lookup').with(cte) – Simmer