getSku using item_id in custom table

I am creating a custom module. There are two new tables.

Table1: t1_id(PK), order_id(FK)
Table2: t2_id(PK), t1_id(FK), item_id(FK).

Table2.item_id is equivalent to sales_flat_order_item.item_id. I am creating a custom report and in the collection need to show the SKU. At first I tried the following:

$collection = Mage::getModel('custom/two')->getCollection();

$tbl_product_collection = Mage::getSingleton('core/resource')->getTableName('catalog/product');
$tbl_one = Mage::getSingleton('core/resource')->getTableName('custom/one');
$tbl_two = Mage::getSingleton('core/resource')->getTableName('custom/two');

$collection->getSelect()  
->from(array('tbl_one' => $tbl_one))  
->join(array('tbl_two' => $tbl_two),  
 'tbl_two.t1_id = tbl_one.t1_id')  
// Join with Item ID on Simple Product  
// Showing wrong SKU   
->join(array('product' => $tbl_product_collection),  
     'tbl_two.item_id = product.entity_id');

However, the product.entity_id is actually the product_id of sales_flat_order_item.item_id. How can I get the SKU in the collection, by relating to the item_id in table2?

Thanks for any help or suggestions!

One thought on “getSku using item_id in custom table”

  1. It took a little bit of digging. It helps to go to the config.xml for the core model you are looking at. This way you know which table in the database getTableName goes to.

    $tbl_product_collection = Mage::getSingleton('core/resource')->getTableName('sales/order_item');
    $tbl_one = Mage::getSingleton('core/resource')->getTableName('custom/one');
    $tbl_two = Mage::getSingleton('core/resource')->getTableName('custom/two');

    $collection->getSelect()
    ->from(array('tbl_one' => $tbl_one))
    ->join(array('tbl_two' => $tbl_two),
    'tbl_two.t1_id = tbl_one.t1_id')
    // Join Item ID
    ->join(array('order_item' => $tbl_order_item),
    'tbl_two.item_id = order_item.item_id');

Leave a Reply

Your email address will not be published. Required fields are marked *