Write nested query in rails activerecord style

Question

How do i write this query in rails active record style

SELECT COUNT(o.id) no_of_orders, 
       SUM(o.total) total, 
       SUM(o.shipping) shipping 
  FROM orders o JOIN 
(
    SELECT DISTINCT order_id
      FROM designer_orders
     WHERE state IN('pending', 'dispatched', 'completed')
) d 
    ON o.id = d.order_id

If ActiveRecord style is not a requirement you could use Arel (which is an AR dependency anyway): http://stackoverflow.com/questions/15789145/how-to-join-on-subqueries-using-arel — Teoulas, Feb 04 '14 at 11:08

damoiser · Accepted Answer · 2014-02-21T17:04:05.950

7

You can do with an explicit query, you have 2 manner to do that:

Model.where("MYSQL_QUERY")

or

Model.find_by_sql("MYSQL_QUERY")

http://apidock.com/rails/ActiveRecord/Base/find_by_sql/class

OR

In Rails Style with a little more steps (probably can be done with less):

order_ids = DesignerOrder.where("state IN (?)", ['pending', 'dispatched', 'completed']).select(:order_id).distinct

partial_result = Order.where("id IN (?)", order_ids)

no_of_orders = partial_result.count
total_sum = partial_result.sum(:total)
shipping_sum = partial_result.sum(:shipping)

edited Feb 21 '14 at 17:04

answered Feb 04 '14 at 07:52

damoiser

6,058
3
40
66

2

I wish there would be a better way to write this. – chirag7jain Feb 04 '14 at 08:14

score 5 · Answer 2 · edited Nov 19 '18 at 11:17

You can also do it like this

Order
  .select('
    COUNT(o.id) no_of_orders, 
    SUM(o.total) total, 
    SUM(o.shipping) shipping
  ')
  .from('orders o')
  .joins("
    (#{
      DesignerOrders
        .select("DISTINCT order_id")
        .where("state IN('pending', 'dispatched', 'completed')")
    }) d on o.id = d.order_id
  ")

I didn't actually run this but the concept is valid. You don't even need an active record model if you use 'from'. We've used techniques like this to do AR style queries for extremely complex SQL and it's made our lives a lot easier.

Write nested query in rails activerecord style

2 Answers2