How to Join Sales Order and Order Item Collections in Magento 2

join;magento2

I want to join the sales order collection and sales order item collection

How to join the two collection

I have collections like 

order collection

>   $ordercollection=$this->orderCollection->create();

Item collection

>  $orderDatamodel = $this->itemFactory->create()->getCollection()
>             ->addAttributeToFilter('created_at', array('from'=>$fromDate, 'to'=>$toDate));

I want to join these two collections.

How could i join these two?

Best Answer

You can join order collection and item collection as follows

use following namespaces like

use \Magento\Sales\Model\Order\ItemFactory;
use \Magento\Sales\Model\ResourceModel\Order\CollectionFactory;

write __construct function 

 public function __construct(Config $helper,ItemFactory $itemFactory,CollectionFactory $orderCollection) {
        $this->helper = $helper;
        $this->itemFactory =$itemFactory;
        $this->orderCollection=$orderCollection;

    }

Then get order collection 

 $ordercollection=$this->orderCollection->create();

get Order table

 $orderTable=$ordercollection->getTable('sales_order');

Item collection

 $orderDatamodel = $this->itemFactory->create()->getCollection();

Write Join Query

 $addToquery = $orderDatamodel->getSelect()->joinLeft(array('sales_flat_order'=>$orderTable), 'main_table.order_id = sales_flat_order.entity_id',array('sales_flat_order.status','sales_flat_order.customer_group_id'));

I had left join on entity_id and order_id and added two columns from the order table you can add your own conditions and add columns you want.

thanks

Related Topic