1

I'm trying to add subscriber status to sales_flat_order_grid. I already got postcode working, but now I'm kind of stuck. This is what I've tried so far:

startSetup();

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

// 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'),
    'postcode',
    'postcode'
);
$this->getConnection()->addKey(
    $this->getTable('sales/order_grid'),
    'subscriber_status',
    'subscriber_status'
);

This works fine, it adds the columns to the sales_flat_order_grid table. Now I need to fill the newly added columns, like this:

// Now you need to fullfill existing rows with data from address table
$select = $this->getConnection()->select();
$select->join(
    array(
        'address' => $this->getTable('sales/order_address'),
    ),
    $this->getConnection()->quoteInto(
        'address.parent_id = order_grid.entity_id AND address.address_type = ?',
        Mage_Sales_Model_Quote_Address::TYPE_SHIPPING
    ),
    array('postcode' => 'postcode')
)->join(
    array('subscriber' => $this->getTable('newsletter_subscriber')),
    $this->getConnection()->quoteInto(
        'subscriber.subscriber_email = address.email AND address.address_type = ?',
        Mage_Sales_Model_Quote_Address::TYPE_SHIPPING
    ),
    array('subscriber_status' => 'subscriber_status')
);

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

But unfortunately it doesn't work, the column gets added, but they don't get filled. Can someone get me back on track? I got no errors, except that the transaction gets locked.

  • 2
    Can you be more specific on whats not working? Are you getting an error or something like that – Sander Mangel Sep 22 '15 at 10:53
  • @SanderMangel I improved it – Timon de Groot Sep 22 '15 at 12:36
  • Check magento.stackexchange.com/questions/45572/… saying: The sales_flat_order_address address table can have 2 rows for each order so you can't directly join this table with the sales_flat_order_grid table, you need to join against the sales_flat_order table first and then join the sales_flat_order_address table using the shipping_address_id or the billing_address_id... mind the second answer stating this is bad practice. – Nicolas Sep 22 '15 at 17:25
0

Take a look at Add new column to sales_flat_order_grid

The sales_flat_order_grid table is updated at every order save action (sales_flat_order). To accomplish what you are trying to do, you could also hose fields to sales_flat_order_grid (i.e postcode and subscriber_status).

Then use an observer to keep your grid up to date

$order = Mage::getModel('sales/order')->load(xyz);
$order->setData('subscriber_status', 1)
        .....
       ->save();

Please remember to clear cache after adding the fields

|improve this answer|||||
  • That sounds like a good fallback plan! However, isn't there a way to have them imported right away when you run the setup script? I already have an observer which adds a virtual grid column for the postcode. Am I missing a step right there? – Timon de Groot Sep 22 '15 at 13:44

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.