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

That's not a complete Active Record query. Please supply the whole thing. — the Tin Man, Dec 27 '13 at 23:07
So you want to express an INNER JOIN to a derived table in the usual "pile of AR method calls" form? — mu is too short, Dec 27 '13 at 23:20
in cases like this the raw query might be preferred to one generated by an ORM — bjhaid, Dec 28 '13 at 03:21
@Strawberry, I just added missing info. Thanks for your attention. — Luca Anceschi, Dec 28 '13 at 19:48
@bjhaid, I've to admit I unsuccessfully searched online AR documention regarding how to extend a join on a derived-table. — Luca Anceschi, Dec 28 '13 at 19:52
I'm with @bjhaid here. For anything this complicated I'd just bypass AR and write the SQL myself. The result will be clearer than a big pile of method calls and if someone can't understand the SQL then they will understand the "big pile of method calls" even less. AR is pretty terrible for this sort of thing though, you end up having to jump through stupid hoops to get proper parameter quoting/escaping/sanitization and you end up having to de-string-ify the outputs yourself. Look into the other database interfaces for this sort of thing. — mu is too short, Dec 28 '13 at 23:46

score 0 · Answer 1 · answered Dec 27 '13 at 23:12

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.

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

1 Answers1