How to Join 2 Tables

Joining 2 tables in Magento 2 is the usual operation you need to implement when working with Magento 2 project. In order to make you do that with ease, the developer team from Mageplaza recommend the topic Join Data Between 2 Tables in Magento 2. In this topic, I will guide you how to get all orders that created with a specify payment method such as “Check Money Order”.

Overview of joining data between 2 tables in Magento 2

Step 1: Set a Collection class that extends
Step 2: Set your own function to get data
Step 3: Get the collection and call to filterOrder function above
Step 4: Save

Step 1: Set a Collection class

In the first step, you will form the Collection class that extends \Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection

class Collection extends \Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection

In the Collection class, there are two parameters you need to understand:

The first is your module name
The second is Sale order resource model from Magento sales module

 protected function _construct()
    {
        $this->_init('Mageplaza\HelloWorld\Model\YourModel', 'Magento\Sales\Model\ResourceModel\Order');
    }

Step 2: Set your own function to get data

Use the code script to set the own function, then get data as need.

protected function filterOrder($payment_method)
{
    $this->sales_order_table = "main_table";
    $this->sales_order_payment_table = $this->getTable("sales_order_payment");
    $this->getSelect()
        ->join(array('payment' =>$this->sales_order_payment_table), $this->sales_order_table . '.entity_id= payment.parent_id',
        array('payment_method' => 'payment.method',
            'order_id' => $this->sales_order_table.'.entity_id'
        )
    );
    $this->getSelect()->where("payment_method=".$payment_method);
}

Step 3: Get the collection and call to filterOrder function above

In your model, you just need to get the collection and call to filterOrder function above.

$collection = $this->YourCollectionFactory->create();

$collection->filterOrder("checkmo");

foreach ($collection as $item) {
    //do what you want with the data here.
}