WITH query (postgres) on ActiveRecord

Question

Postgres has great support for WITH queries.

I wonder if I can somehow us WITH operation on active record?

Something like:

User.with("user_sales as (select user_id, sum(amount) from sales)")
    .joins("LEFT JOIN user_sales on users.id = user_sales.user_id"

I know that I could achieve the same without WITH query, however queries usually becomes way more complicated without it.

Answer 1

Arel, the engine below ActiveRecord defines several useful methods, including the WITH query.

User.joins('...').tap do |relation|
  relation.arel.with(Sale.select('...').arel.as('user_sales'))
end

However, do be aware that Arel is officially considered private api and subject to change.

Just make sure you do all of your ActiveRecord things before .arel