2

I have added a few custom columns to the sales order grid.

SKU and product name were successfully added, appearing as expected in their respective rows.

Now when I add a product attribute that doesn't come from the sales_flat_order_item table, I get double entries populating for every row for these columns.

=> For example : I add custom_attr from the catalog_product_entity_varchar table. Now all three extra columns are doubled up:

SKU: 123,123

Name: example, example

custom_attr: blue,blue

I think the problem comes from the following code in my Observer, if anyone can spot it?

    $collection = $observer->getOrderGridCollection();
    $select = $collection->getSelect();
    $select->joinLeft(array('payment' => $collection->getTable('sales/order_payment')), 'payment.parent_id=main_table.entity_id', array('payment_method' => 'method'));
    $select->join('m_sales_flat_order_item', '`m_sales_flat_order_item`.order_id=`main_table`.entity_id', array('sku' => new Zend_Db_Expr('group_concat(`m_sales_flat_order_item`.sku SEPARATOR ", ")'),'name'  => new Zend_Db_Expr('group_concat(`m_sales_flat_order_item`.name SEPARATOR ", ")') ));
    $select->join('m_catalog_product_entity_varchar', '`m_catalog_product_entity_varchar`.attribute_id=163 AND `m_catalog_product_entity_varchar`.entity_id = `m_sales_flat_order_item`.`product_id`', array('custom_attr'  => new Zend_Db_Expr('group_concat(`m_catalog_product_entity_varchar`.value SEPARATOR ",")')));
    $select->group('main_table.entity_id');

Update

Thanks to the below responses I was able to reach a solution with a bit modification. The two joins worked as below.

    $select->join('m_sales_flat_order_item','`m_sales_flat_order_item`.order_id=`main_table`.entity_id', array('sku' => new Zend_Db_Expr('`m_sales_flat_order_item`.sku'),'name'  => new Zend_Db_Expr('`m_sales_flat_order_item`.name') ))->where('`m_sales_flat_order_item`.parent_item_id IS NULL');
    $select->join(array('m_catalog_product_entity_varchar'),'(m_catalog_product_entity_varchar.entity_id = `m_sales_flat_order_item`.`product_id`) AND `m_catalog_product_entity_varchar`.attribute_id=163',
array('custom_attr' => 'm_catalog_product_entity_varchar.value'));
2

@snevet,you need add a condition to collection.

$select->where('`m_sales_flat_order_item`.parent_item_id IS NULL');

Because of when an configurable is ordered,two row insert into sales_flat_item table.One is save simple product id and another configurable product,but tow save same sku which is simple product sku

  • Hello Amit, where should I put this in relation to the other lines of code? – Michael Mar 18 '15 at 12:54
  • $select->join('m_sales_flat_order_item', 'm_sales_flat_order_item.order_id=main_table.entity_id', array('sku' => new Zend_Db_Expr('group_concat(m_sales_flat_order_item.sku SEPARATOR ", ")'),'name' => new Zend_Db_Expr('group_concat(m_sales_flat_order_item.name SEPARATOR ", ")') ))->where('sales_flat_order_item.parent_item_id IS NULL');; – Amit Bera Mar 18 '15 at 12:56
  • I have tried this but nothing changes. The double entries remain. When I remove the line starting $select->join('m_catalog_product_entity_varchar'... it gives me the correct entries for sku and name but of course a blank custom_attr column – Michael Mar 18 '15 at 13:01
1

Try out below below code instead of custom_attr join:

->joinLeft(
    array('m_catalog_product_entity_varchar'),
    '(m_catalog_product_entity_varchar.entity_id = `m_sales_flat_order_item`.`product_id`) AND `m_catalog_product_entity_varchar`.attribute_id=163',
    array('custom_attr' => m_catalog_product_entity_varchar.value)
)
  • I get the error Column not found: 1054 Unknown column 'm_catalog_product_entity_varchar.value' in 'field list' – Michael Mar 18 '15 at 13:08
  • I had to modify your code a bit, but it allowed me to show the custom_attr column with the required output. But then the sku and name were still giving me the double entries. I'll update my question to show the final code. Thanks – Michael Mar 18 '15 at 15:29

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.