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