How to use left join SQL query in Model class Magento 2?

Magento 2 Left Join SQL query in resource model class to get data from multiple tables.

Left Join is used to fetch all records from the left table and retrieve matching records from the second table.

We can use the joinLeft() method to use Left Join in Magento Collection.

A Demo to fetch customers with customer address details like city and postcode from the customer_address_entity table using the join query.

Defination of joinLeft():
joinLeft($name, $cond, $cols = ‘*’, $schema = null)

Here parameters data types,
* $name will be array|string|Zend_Db_Expr
* $cond string Join condition with other tables.
* $cols will be array|string. Columns to select from the joined table.
* $schema string. The database name to specify if any.

<?php declare(strict_types=1);

namespace Your\PathTo\Model;

use Magento\Customer\Model\ResourceModel\Customer\CollectionFactory;
use Magento\Framework\App\ResourceConnection;

class General
{
    private CollectionFactory $customerCollectionFactory;
    private ResourceConnection $resource;

    public function __construct(
        CollectionFactory $customerCollectionFactory,
        ResourceConnection $resource
    ) {
        $this->customerCollectionFactory = $customerCollectionFactory;
        $this->resource = $resource;
    }

    public function getCustomerData()
    {
        $customerCollection = $this->customerCollectionFactory->create();
        $customerCollection->addAttributeToFilter('email', 'rakesh@jesadiya.com');

        $customerCollection->getSelect()->joinLeft(
            ['ca' => $this->getTable('customer_address_entity')],
            'e.entity_id = ca.parent_id',
            ['city', 'postcode']
        );
        return $customerCollection->getData();
    }

    public function getTable(string $name) {
        return $this->resource->getTableName($name);
    }
}

You can write a join operation on multiple tables also.

You can do multiple joins between tables in a single query.

Some Brief explanation for the join query,

$customerCollection indicates the customer_entity table as the primary table.

$customerCollection->getSelect()->joinLeft(
[‘ca’ => $this->getTable(‘customer_address_entity’)],
‘e.entity_id = ca.parent_id’,
[‘ca.city’, ‘ca.postcode’]
);

The first parameter will be a table name to join with, ca is the alias for the customer_address_entity.
The second Parameter is the conditions to join with another table. //’e.entity_id = ca.parent_id’
The third parameter is the list of columns required from the second(Join) table.

Share this:

Related