6

I am following Ivan's tutorial (Adding order attribute to the orders grid in Magento 1.4.1) to add an extra column to the sales_order_grid table (the Shipping Description text) and it's working except it's not bring old data across from sales_flat_order to the new column in sales_order_grid.

My SQL install script adds the column correctly and because I'm using the same field name as in sales_flat_order I don't think I need an observer, however the code to import all of the existing shipping description data into the shipping_description field isn't running.

What I am doing wrong?

My SQL install script:

<?php
/**
 * Setup scripts, add new column and fulfills
 * its values to existing rows
 *
 */
/* @var $this Mage_Sales_Model_Mysql4_Setup */
$this->startSetup();

// Add column to grid table
$this->getConnection()->addColumn(
    $this->getTable('sales/order_grid'),
    'shipping_description',
    "varchar(255) not null default ''"
);

// Add key to table for this field,
// it will improve the speed of searching & sorting by the field
$this->getConnection()->addKey(
    $this->getTable('sales/order_grid'),
    'shipping_description',
    'shipping_description'
);


// fill existing rows with data
$select = $this->getConnection()->select();
$select->join(
    array('order'=>$this->getTable('sales/order')),
    $this->getConnection()->quoteInto('order.entity_id = order_grid.entity_id',''),
    array('shipping_description' => 'shipping_description')
);

$this->getConnection()->query(
    $select->crossUpdateFromSelect(
        array('order_grid' => $this->getTable('sales/order_grid'))
    )
);

$this->endSetup();

I am using Magento 1.5.1 Community Edition!

Thanks for any assistance!

MagePal Extensions
  • 17,646
  • 2
  • 47
  • 62
simonyoung
  • 845
  • 3
  • 10
  • 22

1 Answers1

4

this should work:

$select = $this->getConnection()->select();
$select->join(
    array('order_shipping'=>$this->getTable('sales/order')),//alias=>table_name
    $this->getConnection()->quoteInto(
        'order_shipping.entity_id = order_grid.entity_id',
        Mage_Sales_Model_Quote_Address::TYPE_SHIPPING
    ),//join clause
    array('shipping_description' => 'shipping_description')//fields to get
);
$this->getConnection()->query(
    $select->crossUpdateFromSelect(
        array('order_grid' => $this->getTable('sales/order_grid'))
    )
);

If you want, take a look at my extension :)
HTH

OSdave
  • 8,538
  • 7
  • 45
  • 60
  • Perfect! I had looked at your extension whilst I was trying to put mine together and between Ivan's post and your extension I was so close - just getting it wrong populating the data :-) I will be sure to open source my super simple version too later on. Thanks ! – simonyoung Jun 26 '11 at 09:31
  • actually i need one field warehouse in sales order grid i created column name warehouse in sales_flat_order and sales_flat_order_grid table and created attribute : warehouse with values //workout $this->addColumn('warehouse', array( 'header' => Mage::helper('sales')->__('Warehouse'), 'index' => 'warehouse', 'type' => 'options', 'width' => '70px', 'options' => Mage::getSingleton('eav/config')->getAttribute('catalog_product', 'warehouse'), )); still not working, is any steps i need to follow? – Gem Oct 24 '17 at 12:40
  • The column name displayed on sales order grid, and values not display, and main thing after placed order i need maually change the warehouse values, initially the name should be display like "pick the order" my attribute: – Gem Oct 24 '17 at 12:42
  • could you pls help me what i need to do? – Gem Oct 24 '17 at 12:43
  • @Rathinam you should open your own question if you are looking for help – OSdave Oct 25 '17 at 12:47