How to Join Tables in Magento 2

Joining table in Magento 2 is similar to magento 1, which we normally need to perform our various operation. In this blogpost we will see how to join tables in Magento 2.

Step 1

Setup a collection class as we have did in our last blog post

W3solver\Helloworld\Model\ResourceModel\Hello\Collection.php
<?php 
namespace W3solver\Helloworld\Model\ResourceModel\Hello;

class Collection extends \Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection
{
    /**
     * @var string
     */
    protected $_idFieldName = 'id';

    /**
     * Define resource model
     *
     * @return void
     */
    protected function _construct()
    {
        $this->_init('W3solver\Helloworld\Model\Hello', 'W3solver\Helloworld\Model\ResourceModel\Hello');
    }

}

Step 2

Now we will place function that will join two of our tables and will give us the query to join. We will be injecting our hello model in block class constructor to get the collection and will join it with another table in getJoinData function.

<?php

namespace W3solver\Helloworld\Block;

class Hello extends \Magento\Framework\View\Element\Template {

    protected $_helloModelFactory;
    protected $_resource;
    
    public function __construct(
    \Magento\Framework\View\Element\Template\Context $context,
    \W3solver\Helloworld\Model\HelloFactory $helloModelFactory,
    \Magento\Framework\App\ResourceConnection $Resource
    ) {
        $this->_helloModelFactory = $helloModelFactory;
        $this->_resource = $Resource;
        
        parent::__construct($context);
    }

    protected function _prepareLayout()
    {
        $text = $this->getJoinData();
        $this->setText($test);
    }
    
    public function getJoinData(){
        $collection = $this->_helloModelFactory->create()->getCollection();
        $second_table_name = $this->_resource->getTableName('second_table_name'); 
        
        $collection->getSelect()->joinLeft(array('second' => $second_table_name),
                                               'main_table.id = second.customer_id');
        echo $collection->getSelect()->__toString(); 
        exit();
        
    }
} 

Above we have injected hello model of first table and resourceConnection class to get table name. Then we get the collection of first table which is by default alias as “main_table” in magento. This will give you the query which you can verify as per your requirement.

Leave a Comment.