Item with the same id already exist

Question

Argh! This is the error message:

Item (My_returns_model) with the same id "913" already exist

I need to not use GROUP BY because I am using a paginated grid. Here is my code:

$collection = Mage::getModel('pvxcloudbridge/returns')->getCollection()
        ->addFieldToSelect('id') //<-- this is the culprit
        ->addFieldToSelect('returnid')
        ->addFieldToSelect('orderid')
        ->addFieldToSelect('itemcode')
        ->addFieldToSelect('qtyreturned')
        ->addFieldToSelect('conditioncode')
        ->addFieldToSelect('updated_at')
        ->addFieldToSelect('processed');
$collection->getSelect()->joinLeft(array('sfo' => 'sales_flat_order'), 'main_table.orderid = sfo.increment_id', array('sfo.created_at', 'sfo.grand_total'));        
$collection->getSelect()->joinLeft(array('sfoa' => 'sales_flat_order_address'), 'sfo.entity_id=sfoa.parent_id', array('country_id'));       
$collection->getSelect()->joinLeft(array('sfs' => 'sales_flat_shipment'), 'sfoa.parent_id=sfs.order_id', array('sfs.created_at'));      
$collection->getSelect()->joinLeft(array('sfcg' => 'sales_flat_creditmemo_grid'), 'sfo.increment_id=sfcg.order_increment_id', array('sfcg.base_grand_total'));      

$collection->join(array('payment' => 'sales/order_payment'), 'sfo.entity_id=payment.parent_id', 'method');

$collection->getSelect()->columns(
array(
    'days' => new Zend_Db_Expr('DATEDIFF(`main_table`.`updated_at`,`sfs`.`created_at`)'),
    'refund' => new Zend_Db_Expr('sfcg.base_grand_total'),
));            

$collection->getSelect()->distinct(true);

$this->setCollection($collection);
return parent::_prepareCollection();

And here is the actual query being generated:

 SELECT DISTINCT `main_table`.`id`, `main_table`.`returnid`, `main_table`.`orderid`, 
`main_table`.`itemcode`, `main_table`.`qtyreturned`, `main_table`.`conditioncode`, 
`main_table`.`updated_at`, `main_table`.`processed`, `sfo`.`created_at`, 
`sfo`.`grand_total`, `sfoa`.`country_id`, `sfs`.`created_at`, `sfcg`.`base_grand_total`, 
`payment`.`method`, DATEDIFF(`main_table`.`updated_at`,`sfs`.`created_at`) AS `days`, sfcg.base_grand_total AS `refund`
FROM `pvxcloudbridge_returns` AS `main_table` LEFT JOIN `sales_flat_order` AS `sfo` ON main_table.orderid = sfo.increment_id 
LEFT JOIN `sales_flat_order_address` AS `sfoa` ON sfo.entity_id=sfoa.parent_id 
LEFT JOIN `sales_flat_shipment` AS `sfs` ON sfoa.parent_id=sfs.order_id LEFT JOIN `sales_flat_creditmemo_grid` AS `sfcg` ON sfo.increment_id=sfcg.order_increment_id INNER JOIN `sales_flat_order_payment` AS `payment` ON sfo.entity_id=payment.parent_id

Any help is appreciated.

score 6 · Accepted Answer · answered Nov 25 '15 at 13:51

The golden rule of magento collections is that you cannot have 2 elements with the same id.
So if you need a collection of objects retrieved from 2 joined tables you should start your collection from the lowest level.
This means if you have a parent-child relation between objects you need to start your collection from the child object and join the parent table.
So, in your case you should start from the creditmemo or shipments collection (not sure which one is).
But this can get a bit complicated.

You say you cannot use group by because you need a paginated list.
I say you can still use group by.
All you have to do is add this method in your collection model.

public function getSelectCountSql()
{
    $countSelect = parent::getSelectCountSql();
    $countSelect->reset(Zend_Db_Select::GROUP);
    return $countSelect;
}

This way your pagination will not break when using group by statements.

I hope that at least one of the solutions works for you.
I would start with the second one. It's easier to test.

You my friend are an absolute ledge. – beingalex Nov 25 '15 at 13:58 — beingalex, Nov 25 '15 at 13:58

Item with the same id already exist

1 Answers1