products = Product.arel_table
orders = Order.arel_table
average_purchase_time = Arel::Nodes::Case.new.when(orders[:finished_at].not_eq(nil))
.then(orders[:finished_at] - orders[:created_at])
purchased = Arel::Nodes::Case.new.when(orders[:finished_at].not_eq(nil))
.then(1).else(0)
statement = products
.outer_join(orders)
.on(orders[:product_id].eq(products[:id]))
.project(products[:id].count(true).as('total'))
.project(purchased.sum.as('purchased'))
.project(average_purchase_time.average.as('average_purchase_time'))
Product.find_by_sql(statement.to_sql)
Explaining:
products = Product.arel_table
orders = Order.arel_table
When you call arel_table for an ActiveRecord class, this will return an Arel::Table object.
average_purchase_time = Arel::Nodes::Case.new.when(orders[:finished_at].not_eq(nil))
.then(orders[:finished_at] - orders[:created_at])
purchased = Arel::Nodes::Case.new.when(orders[:finished_at].not_eq(nil))
.then(1).else(0)
With Arel::Nodes::Case, you can create an object that generates a CASE expression.
The method outer_join is used to left join a table, that returns an Arel::SelectManager object. Please remember to call the method on with the ON clause.
