Active Record query with complex join on a derived-table?

Question

I'm working with Ruby, Sinatra and MySQL. I have four tables.

Contacts (customers):

class Contact < ActiveRecord::Base
  attr_accessible :id, :company_id, :name, :address, ...

  has_many :orders, :dependent => :destroy
  has_many :ordered_products
  ...
end

Orders:

class Order < ActiveRecord::Base
  attr_accessible :id, :name, :contact_id, ...

  belongs_to :contact  

  has_many :ordered_products, :dependent => :destroy
  has_many :products, :through => :ordered_products
  ...
end

OrderedProducts:

class OrderedProduct < ActiveRecord::Base
  attr_accessible :quantity, :price, :contact_id, :product_id, :order_id ...

  belongs_to :contact
  belongs_to :product
  belongs_to :order
  ...
end

and Products:

class Product < ActiveRecord::Base
  attr_accessible :id, :name, :producer, :region, :size ...

  has_many :ordered_products, :dependent => :destroy
  has_many :orders, :through => :ordered_products
  ...
end

I wrote the following SQL query for getting stats centered around the contacts.

SELECT `contacts`.`name`,
  MIN(orderTotals) AS min,
  AVG(orderTotals) AS avg,
  MAX(orderTotals) AS max,
  SUM(`ordered_products`.`price` * `ordered_products`.`quantity`) AS total,
  CAST(COUNT(DISTINCT `orders`.`id`) AS UNSIGNED) AS ordersNumber,
  CAST(SUM(`ordered_products`.`quantity`) AS UNSIGNED) AS productsNumber
FROM `contacts`
INNER JOIN `orders` ON `orders`.`contact_id` = `contacts`.`id`
INNER JOIN `ordered_products` ON `ordered_products`.`order_id` = `orders`.`id`
INNER JOIN `products` ON `products`.`id` = `ordered_products`.`product_id`
INNER JOIN
  ( 
    SELECT contact_id as identifier,
    SUM(`ordered_products`.`price` * `ordered_products`.`quantity`) as orderTotals
    FROM `ordered_products`
    GROUP BY `ordered_products`.`order_id`
  ) `sumTable`
  ON `sumTable`.`identifier` = `contacts`.`id`
WHERE `contacts`.`company_id` = 74
  AND (`orders`.`updated_at` >= '2013-01-01 00:01:59')
  AND (`orders`.`updated_at` <= '2013-12-31 23:59:59')
  AND (`orders`.`order_state_id` = '100')
GROUP BY `contacts`.`id`
ORDER BY `contacts`.`name` ASC
LIMIT 20
OFFSET 0;

I would like to translate it to Active Record format entirely. I was able to convert and run it properly, but the following inner-join eludes me, and I'm obliged to paste SQL directly:

.joins('INNER JOIN (SELECT contact_id as identifier,
SUM(ordered_products.price * ordered_products.quantity) as orderTotals
FROM `ordered_products` GROUP BY ordered_products.order_id) `sumTable`
ON `sumTable`.`identifier` = `contacts`.`id`').

I'm reporting only a fragment of the translated SQL query because the translation of the join on a derived-table is what matters to me most. I find it far less elegant and readable for instance of something like:

Contact.joins(orders: [{ordered_products: :product}]). ...

Any help would be appreciated!

Thanks, Luca

Answer 1

Can't fully tell what you're trying to do since the rest of the code isn't there, but if you're using Postgres you may want to look into the pg_search gem since it might save you some SQL work and it looks like that might become a pain to update/maintain.