0

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'
            ));
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.