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
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.