How to Join 2 Tables in Magento 2

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 recommends the topic Join Data Between 2 Tables in Magento 2. In this topic, I will guide you on how to get all orders created with a specific payment method such as “Check Money Order”.

Overview of joining data between 2 tables in Magento 2

In Magento 2, when working on product collection data, orders, customers, category collection data, or payment, the store admin often requires to join data of two tables to acquire and view data at once. For instance, you need to join data between product collection and custom table.

Here’re some steps to join data of two 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

Now let’s see how it works with an example of getting all orders created with Check Money Order payment method. With this method of joining two tables, you can join data in any collection in Magento 2.

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 the 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.
}

Step 4: Save

Finally, save all to complete with the joining data between 2 tables in Magento 2.

Conclusion

This tutorial guides you through how to join data of two tables in Magento 2. In case you don’t have time to do this, our service team is always happy to help.

Looking for
Customization & Development Services?

8+ years of experiences in e-commerce & Magento has prepared us for any challenges, so that we can lead you to your success.

Get free consultant