Update "sales_flat_order_item" table when making an order

Question

I have created a custom product attribute that needs to be inserted into sales_flat_order_item (there's a custom column already created) everytime an order is placed. Config.xml for the observer event:

<events>
    <sales_order_place_after>
        <observers>
            <test_modul_model_observer>
                <type>singleton</type>
                <class>Test_Modul_Model_Observer</class>
                <method>orderPlaceAfter</method>
            </test_modul_model_observer>
        </observers>
    </sales_order_place_after>
</events>

Observer.php:

<?php
class Test_Modul_Model_Observer
{
    public function orderPlaceAfter($observer)
    {
        $order = $observer->getEvent()->getOrder();

        foreach ($order->getAllItems() as $item) {
            //$item is an instance of Mage_Sales_Model_Order_Item
            $product = Mage::getModel('catalog/product')->load($item->getProductId());
        }

        $articleNumber = $product->getArticleNumber();

        $connection = Mage::getSingleton('core/resource')->getConnection('core_write');
        $lastInsertId = $connection->lastInsertId();

        $sql = "UPDATE sales_flat_order_item SET article_number = '" .   $articleNumber . "' where item_id = ". $lastInsertId;
        $connection->query($sql);

        Mage::log($sql, null, "order.log", true);
    }
}

The problems is... the $lastInsertId is 0 so it doesn't know what column to update. It supposed to be the last record inserted (after I complete the order).

Any help would be appreciated. Thanks!

Answer 1

Managed to get last item id with this solution. I know, it isn't the most efficient way but still

public function orderPlaceAfter($observer)
{
    $order = $observer->getEvent()->getOrder();

    foreach ($order->getAllItems() as $item) {
        $product = Mage::getModel('catalog/product')->load($item->getProductId());
    }
    $articleNumber = $product->getArticleNumber();

    $connection = Mage::getSingleton('core/resource')->getConnection('core_write');
    $collection = Mage::getModel('sales/order_item')->getCollection();
    foreach ($collection as $c) {
        $customOrder = Mage::getModel("sales/order")->load($c->getId());
        $ordered_items = $customOrder->getAllItems();
            foreach($ordered_items as $items) {
                $allItems = $items->getItemId();    
            }
    }

    $sql = "UPDATE sales_flat_order_item SET article_number = '" .   $articleNumber . "' where item_id = ". $allItems;
    $connection->query($sql);

}

Answer 2

Please use event sales_order_save_commit_after and in your observer $lastInsertId = $order->getId().

Edit: sorry, I should read better ...

If you want to add product attribute to sales_flat_order_item column you can use sales_convert_quote_item_to_order_item event.

1. add your product attribute to quote item to global node in config.xml

   <sales>
       <quote>
           <item>
               <product_attributes>
                   <article_number />
               </product_attributes>
           </item>
       </quote>
   </sales>
   

2. add your observer in config.xml

   <sales_convert_quote_item_to_order_item>
       <observers>
           <some_alias>
               <class>your_module/observer</class>
               <method>addArticleNumberToSalesFlatOrderItem</method>
           </some_alias>
       </observers>
   </sales_convert_quote_item_to_order_item>
   

3. and this method in Observer.php

   public function addArticleNumberToSalesFlatOrderItem(Varien_Event_Observer $observer)
   {
       $product = $observer->getItem()->getProduct();
       $orderItem = $observer->getOrderItem();
       $orderItem->setArticleNumber($product->getArticleNumber());
   }