Magento2 Invoice – How to Get Invoice Item List Using SQL Query

There isn't a built-in way to do this, but there are many paths to the information you want.

This is one of the many ways to do this.

/** @var Mage_Sales_Model_Order $order */
$order = Mage::getModel('sales/order')->load($orderId);

/** @var Mage_Sales_Model_Resource_Order_Item_Collection $items */
$items = Mage::getSingleton('sales/order_item')->getCollection()->setOrderFilter($order);
$items->getSelect()->joinLeft(
    array('oii' => $items->getTable('sales/invoice_item')),
    'main_table.item_id = oii.order_item_id',
    array('invoice_id' => 'parent_id')
);
$items->getSelect()->group('main_table.item_id');

foreach ($items as $item) {
    /** @var Mage_Sales_Model_Order_Item $item */
    $item->setOrder($order);
    if ($item->getInvoiceId()) {
        // Invoiced and this is the invoice number
    } else {
        // Not invoiced (or missing invoice item)
    }
}

You are basically left-joining the invoice items to the order items so you can add the parent_id column from the invoice item as 'invoice_id' when you hydrate the order item record. The 'invoice_id' data will now exist on the order item directly. If there was no matching invoice item then the 'invoice_id' will be null.

I had to directly access the select object as Magento collections do not support a joinLeft directly and if you use the join method on the collection you are doing an inner join which would exclude any order items without matching invoice items.

Lastly, I used the group method on the select to prevent the possibility, however remote, that you get duplicate order items due to the join.

Edit: I updated the way the item collection is gathered because the getItemCollection method on an order is braindead and iterates the order items internally before returning, preventing you from making changes to the collection SQL without doing a reset.

James As you want you works in on invoice creation time you can use getOrigData('entity_id')

On creation on invoice it always give the null value and you can use sales_order_invoice_save_after events

so you try use

$invoiceId = $observer->getEvent()->getInvoice()->getOrigData('entity_id');
if(is_null($invoiceId )){
// do your logic
}

an for getting item qty try this

   foreach ($invoice->getAllItems() as $item){
                if ($item->getOrderItem()->getParentItem()) {
                    continue;
                }
                /* Draw item */

        $name = $item->getName();
        $type = $item->getSku();
        $id = $item->getProductId();
        $qty = $item->getQty();

        }