How to Join Multiple Tables in Magento 2?

 How to Join Multiple Tables in Magento 2 When creating a report in Magento 2, you may need to combine data from multiple tables. For example, let’s say you want to create a report that shows the total number of orders placed by each customer over time. When creating a report in Magento 2, you may need to combine data from multiple tables. For example, let’s say you want to create a report that shows the total number of orders placed by each customer over time. Customer over time. Magento 2 allows you to join multiple tables in your query using the JOIN clause.

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('Yourmodule\HellosirWorld\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_item_table = $this->getTable("sales_order_item");

            $this->getSelect()

                ->join(array('e' =>$this->sales_order_item_table), $this->sales_order_table . '.entity_id= e.order_id',

                array('*'

                )

            );

            $this->getSelect()->where("sales_order_item_table=".order_id);

        }

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.

        CONCLUSION

        And that’s about it!

This is how you can join multiple tables in your Magento 2 store.

Here I saw the my collection file for a Example.

<?php
namespace Yourmodule\Vrts\Model\ResourceModel\VrtsGrid;
 
/* use required classes */
use Magento\Framework\Data\Collection\EntityFactoryInterface;
use Psr\Log\LoggerInterface;
use Magento\Framework\Data\Collection\Db\FetchStrategyInterface;
use Magento\Framework\Event\ManagerInterface;
use Magento\Store\Model\StoreManagerInterface;
use Magento\Framework\DB\Adapter\AdapterInterface;
use Magento\Framework\Model\ResourceModel\Db\AbstractDb;
 
class Collection extends \Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection
{
    /**
     * @var string
     */
    protected $_idFieldName = 'id';

    protected $_logger;
 
    /**
     * @param EntityFactoryInterface $entityFactory,
     * @param LoggerInterface        $logger,
     * @param FetchStrategyInterface $fetchStrategy,
     * @param ManagerInterface       $eventManager,
     * @param StoreManagerInterface  $storeManager,
     * @param AdapterInterface       $connection,
     * @param AbstractDb             $resource
     */
    public function __construct(
        EntityFactoryInterface $entityFactory,
        LoggerInterface $logger,
        FetchStrategyInterface $fetchStrategy,
        ManagerInterface $eventManager,
        StoreManagerInterface $storeManager,
        \Magento\Framework\DB\Adapter\AdapterInterface  $connection = null,
        AbstractDb $resource = null
    ) {
        $this->_logger = $logger;
        $this->_init('Yourmodule\Vrts\Model\VrtsGrid', 'Yourmodule\Vrts\Model\ResourceModel\VrtsGrid');
        parent::__construct($entityFactory, $logger, $fetchStrategy, $eventManager, $connection, $resource);
        $this->storeManager = $storeManager;
    }
    
    protected function _initSelect()
    {

        parent::_initSelect();

        $query = $this->getSelect()->reset(\Zend_Db_Select::COLUMNS)
            //my main table fields
            ->columns('main_table.entity_id')
            ->columns('main_table.increment_id')
            ->columns('main_table.customer_lastname')
            ->columns('main_table.status')    
            ->columns('main_table.created_at')
            //join main table with other tables
            ->join(
                [
                    'e'=>$this->getTable('sales_order_item')
                ],
                'main_table.entity_id = e.order_id',
                [
                    'statuslabel'=>'e.product_id'
                ]
            )->distinct(true);

        $this->_logger->error("Query: " . $query->__toString());
        return $this;
    }
}