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:

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
development service
Image Description
Hello, I'm the Chief Technology Officer of Mageplaza, and I am thrilled to share my story with you. My deep love and passion for technology have fueled my journey as a professional coder and an ultra-marathon runner. Over the past decade, I have accumulated extensive experience and honed my expertise in PHP development.

People also searched for

  • magento 2 data between 2 tables
  • How to Join 2 Tables Magento 2
  • inner join 2 tables mysql magento 2
  • left join 2 tables mysql magento 2
  • how to join 2 tables without common fields magento 2
  • joining 2 tables oracle magento 2
  • joining 2 tables on sql magento 2
  • join 2 tables on different servers magento 2
  • joining 2 fact tables obiee magento 2
  • sql join 2 tables on 1 field magento 2
  • 2.3.x, 2.4.x
x