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 Magento Development Company?

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
Vinh Jacker
With over a decade of experience crafting innovative tech solutions for ecommerce businesses built on Magento, Jacker is the mastermind behind our secure and well-functioned extensions. With his expertise in building user-friendly interfaces and robust back-end systems, Mageplaza was able to deliver exceptional Magento solutions and services for over 122K+ customers around the world.
Previous Get Value of Custom Attribute via Rest API
Next Send Order Email to A Custom Email Address
One Step Checkout for Magento 2 by Mageplaza

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