How to Join Multiple Tables in Magento 2?

Magento Tips | Magento 2 Guides | Tips | Magento Tutorial |

October 06, 2021

How to Join Multiple Tables in Magento 2?

Today, we’re going to teach you guys how to join multiple tables in your Magento 2 store.

By default, Magento 2 stores data in separate database tables like products, customers, orders, etc.

But oftentimes, you’ll need to join the data of two tables, especially when you’re working on product, order, or customer collection data.

For example, you might need to get the data of all customers from a specific country or get the data of all orders completed via Stripe.

This is where joining multiple tables’ data comes in handy.

Also read: How to Rename a Table Name Using Declarative Schema in Magento 2.3?

And in this tutorial, we’re going to show you exactly how you can join multiple data in your Magento 2 store.

Step-By-Step Process to Join Multiple Tables in Magento 2

Please follow the below steps to learn how to join multiple tables in your Magento 2 store.

Step - 1

First of all, we need to form a collection class that extends ‘AbstractCollection’ by executing the following code:

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

In the collection class, you should know about the following two parameters:

First is your module name
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

After that, you need to run the following code script set your own function to get data as per your requirement:

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

Next, you need to get the collection and call filterOrder function by executing below code:

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

$collection->filterOrder("checkmo");

foreach ($collection as $item) {

    //do what you want with the data here.

}

Lastly, save everything to finish the process.

Also read: How to Create a Custom Database Table in Magento 2?