magento2 Item (Magento\Sales\Model\Order) with the same id "x" already exist

Question

I am trying to join sales_flat_order and sales_order_item table.

protected function _initSelect()
{
    parent::_initSelect();

    $this->getSelect()              
            ->joinLeft(
                ['secondTable' => $this->getTable('sales_order_item')],
                'main_table.entity_id = secondTable.order_id',
                array('*')
            ) 
            ->group('secondTable.item_id');         
}

If I print the query (echo $this->getSelect()) , I got the the output as "SELECT main_table., secondTable. FROM sales_order AS main_table LEFT JOIN sales_order_item AS secondTable ON main_table.entity_id = secondTable.order_id GROUP BY secondTable.item_id ". I executed this query in the database and got the correct output.

But when I tried to display it in my grid I'm getting the error as "Item (Magento\Sales\Model\Order) with the same id "4" already exist"

My actual question was, I need to display all items in an order. If I had only one item in an order it's working properly but fails if there is more than one item in an order.

Answer 1

I got the output, First I had set the sales_order_grid as the main table, now I just set sales_order_item as the main table,so the code goes as like below.

protected function _initSelect()
{
    parent::_initSelect();
     $this->getSelect() 
            ->joinLeft(
                ['secondTable' => $this->getTable('sales_order_grid')],
                'main_table.order_id = secondTable.entity_id',
                array('*')
            ) 
            ->where('secondTable.status = ?','complete')
            ->group('main_table.item_id');
}