CODEWITHSUNDEEP
ruby-on-railsrubyactiverecord
chirag7jain
chirag7jain

Reputation: 1547

Write nested query in rails activerecord style

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

Upvotes: 1

Views: 10288

Answers (2)

bridiver
bridiver

Reputation: 1714

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.

Upvotes: 5

damoiser
damoiser

Reputation: 6238

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)

Upvotes: 7

Related Questions