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?

1 Answer 1


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;


Then get order collection


get Order table


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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.