buyerat notes

select carts.`order_number`, products.`shipping_itemsize`, count(products.`shipping_itemsize`)
from carts inner join products on carts.`product_id` = products.`id`
group by carts.order_number, products.`shipping_itemsize`;

carts = Arel::Table.new(:carts)
products = Arel::Table.new(:products)
orders = Arel::Table.new(:orders)

subquery = carts.join(products).project("carts.order_number, products.shipping_itemsize as size, count(products.shipping_itemsize) as size_count").group("carts.order_number, products.shipping_itemsize")

query = orders.project('*').join(subquery.as('carts')).on(carts[:order_number].eq(orders[:order_number]))

query.to_sql
 => "SELECT * FROM `orders` INNER JOIN (SELECT carts.order_number, products.shipping_itemsize as size, count(products.shipping_itemsize) as size_count FROM `carts` INNER JOIN `products`  GROUP BY carts.order_number, products.shipping_itemsize) carts ON `carts`.`order_number` = `orders`.`order_number`"