Simple query: group by customer & specific product attribute (shirt size) but how?

Question

we sell shirts. Now I would like to create quick query that results in the following

customer id | name | email | shirt size (grouped)

I think we can get the orders through collection sales/order_grid_collection

• but how can I then add the shirt size that is hidden in the simple products attribute belonging to the order
• and ideally group by customer id + the size (to get a working list)

many thanks, Sean

======================

I now have this code that I am trying to debug ... our table prefix SOF also makes it a little hard

$data->getSelect()->reset()
        ->from(
            array('order_items' => $this->getTable('sales/order_item')),
            array(
                'ordered_qty' => 'soforder_items.qty_ordered',
                'order_items_name' => 'soforder_items.name',
                'vendor' => 'sofattrval.value',
                'base_cost' => '(SUM(soforder_items.qty_ordered) * soforder_items.base_cost)',
                'sku' => 'soforder_items.sku'
            ))
        ->joinLeft(array('p' => 'sofcatalog_product_entity'), 'soforder_items.product_id = p.entity_id')
        ->joinLeft(array('eav' => 'sofeav_attribute'), 'p.entity_type_id = eav.entity_type_id')
        ->joinLeft(array('attr' =>'sofeav_attribute_option'), 'attr.attribute_id = eav.attribute_id')
        ->joinLeft(array('attrval' =>'sofeav_attribute_option_value'), 'attrval.option_id = attr.option_id')
        ->where("sofeav.attribute_code='shirt_size'")
        ->where('parent_item_id IS NULL')
        ->group('soforder_items.product_id')
        ->having('SUM(soforder_items.qty_ordered) > ?', 0)
        ->order(
            array(
                'SUM(soforder_items.qty_ordered) DESC'
            ));

Answer 1

A few hints on this:

1. I would just use simple SQL to solve this, it is easier and as long as you only want the information, make it easy.
2. You can use $collection->getTable('catalog/product') to get a table name, including prefix and everything
3. The information which size the customer chose is encoded in product_options and there is no easy way to get it from there, so I recommend to process the data afterwards