Custom Collection Issue

Question

This is my query:

SELECT `main_table`.* ,
  CONCAT (`main_table`.`name`, " (", count(*), "x)") as `total`
FROM `sales_flat_order_item` AS `main_table`
WHERE (`order_id` = '145375')
GROUP BY `main_table`.`name`

This is my collection right now:

$orderItems = Mage::getModel('sales/order_item')->getCollection()
            ->addFieldToFilter('order_id', $row->getData('entity_id'));

I want to add the concat condition and group by condition. How can I do that ? thx

Answer 1

You can do it this way, after your collection code add:

$orderItems->getSelect()->columns(array('total' => 'concat(name, " (", count(*), "x)")'));
$orderItems->getSelect()->group('name');