2

I am trying to make a grid with all the orders containing the same product grouped together (all the orders only have one product in them) any ideas how to achieve this? this is what i have so far the last line is breaking my code if i group by city or company it works how do i get group by products to work?

    $collection = Mage::getResourceModel('sales/order_collection')
        ->join(array('a' => 'sales/order_address'), 'main_table.entity_id = a.parent_id AND a.address_type != \'billing\'', array(
            'company'    => 'company',
            'city'       => 'city',
            'country_id' => 'country_id'
        ))
        ->join(array('c' => 'customer/customer_group'), 'main_table.customer_group_id = c.customer_group_id', array(
            'customer_group_code' => 'customer_group_code'
        ))
        ->addExpressionFieldToSelect(
            'fullname',
            'CONCAT({{customer_firstname}}, \' \', {{customer_lastname}})',
            array('customer_firstname' => 'main_table.customer_firstname', 'customer_lastname' => 'main_table.customer_lastname'))
        ->addExpressionFieldToSelect(
            'products',
            '(SELECT GROUP_CONCAT(\' \', x.name)
                FROM sales_flat_order_item x
                WHERE {{entity_id}} = x.order_id
                    AND x.product_type != \'configurable\')',
            array('entity_id' => 'main_table.entity_id')
        );
        $collection->getSelect()->group('products');
1

ok i got it

$collection->getSelect()->group('status')->group('company')
->group('(SELECT GROUP_CONCAT(\' \', x.name) FROM sales_flat_order_item x WHERE main_table.entity_id = x.order_id AND x.product_type != \'configurable\')')
->columns(array('total_qty' => 'count(`main_table`.`grand_total`)','grand_total_sum' => 'sum(`main_table`.`grand_total`)'));

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.