Magento2 Mysql Join Explained In Detail

In this article we will learn magento2 mysql join queries, most of the time I see people having difficulty in using magento2 mysql join and results in writing bad code for database operations.

We will see how to write join queries using magento2 ORM(Object Relational Mapping) methods.

Below I have explained some of the queries by imagining that I have a model that which is connected to a table which schema is below :

Column	Type	Comment
entity_id	int(11) Auto Increment	 primary key
product_id	int(11)	 product id
order_id	int(11)	 order id
customer_id	int(11)	 customer id
test_data       text

In the above table ‘my_table_join_test’ suppose we are saving product id, customer id and order id for some purpose, and want to get the information about product, order, and customer those id’s are present in our table generally, what people do they first write a query to get the data from ‘my_table_join_test’ table then they will write another query to get the data from magento2 product, order, or customer table to get desired data, which is wrong we can write a single join query to get all the data and create a proper collection, and it will also simlplify many issues that you have in creating admin grids where you want to show product, order or customer information, if you have not used join, grids filter and sorting will not work.

Below is our model assuming module name is Stripe and company name is Webkul, create the model class at this location app/code/Webkul/Stripe/Model/JoinModel.php :

<?php
/**
* model class to work with join
*/
namespace Webkul\Stripe\Model;

use Magento\Framework\DataObject\IdentityInterface;

/**
* JoinModel Model.
*/
class JoinModel extends \Magento\Framework\Model\AbstractModel
implements \Webkul\Stripe\Api\Data\JoinModelInterface, IdentityInterface
{
/**
* No route page id.
*/
const NOROUTE_ENTITY_ID = 'no-route';

/**
* Stripe JoinModel cache tag.
*/
const CACHE_TAG = 'my_table_join_test';

/**
* @var string
*/
protected $_cacheTag = 'my_table_join_test';

/**
* Prefix of model events names.
*
* @var string
*/
protected $_eventPrefix = 'my_table_join_test';

/**
* Initialize resource model.
*/
protected function _construct()
{
$this->_init('Webkul\Stripe\Model\ResourceModel\JoinModel');
}

/**
* Load object data.
*
* @param int|null $id
* @param string $field
*
* @return $this
*/
public function load($id, $field = null)
{
if ($id === null) {
return $this->noRouteReasons();
}

return parent::load($id, $field);
}

/**
* Load No-Route JoinModel.
*
* @return \Webkul\Stripe\Model\JoinModel
*/
public function noRouteReasons()
{
return $this->load(self::NOROUTE_ENTITY_ID, $this->getIdFieldName());
}

/**
* Get identities.
*
* @return array
*/
public function getIdentities()
{
return [self::CACHE_TAG.'_'.$this->getId()];
}

/**
* Get ID.
*
* @return int
*/
public function getId()
{
return parent::getData(self::ENTITY_ID);
}

/**
* Set ID.
*
* @param int $id
*
* @return \Webkul\Stripe\Api\Data\JoinModelInterface
*/
public function setId($id)
{
return $this->setData(self::ENTITY_ID, $id);
}
}

Now create resource model at app/code/Webkul/Stripe/Model/ResourceModel/JoinModel/JoinModel.php:

<?php
/**
* Resource Model
*/
namespace Webkul\Stripe\Model\ResourceModel;

/**
* Stripe JoinModel ResourceModel.
*/
class JoinModel extends \Magento\Framework\Model\ResourceModel\Db\AbstractDb
{
/**
* Store model
*
* @var null|\Magento\Store\Model\Store
*/
protected $_store = null;

/**
* Construct
*
* @param \Magento\Framework\Model\ResourceModel\Db\Context $context
* @param string $connectionName
*/
public function __construct(
\Magento\Framework\Model\ResourceModel\Db\Context $context,
$connectionName = null
)
{
parent::__construct($context, $connectionName);
}

/**
* Initialize resource model
*
* @return void
*/
protected function _construct()
{
$this->_init('my_table_join_test', 'entity_id');
}

/**
* Load an object using 'identifier' field if there's no field specified and value is not numeric
*
* @param \Magento\Framework\Model\AbstractModel $object
* @param mixed $value
* @param string $field
* @return $this
*/
public function load(\Magento\Framework\Model\AbstractModel $object, $value, $field = null)
{
if (!is_numeric($value) && is_null($field)) {
$field = 'identifier';
}

return parent::load($object, $value, $field);
}

/**
* Set store model
*
* @param \Magento\Store\Model\Store $store
* @return $this
*/
public function setStore($store)
{
$this->_store = $store;
return $this;
}

/**
* Retrieve store model
*
* @return \Magento\Store\Model\Store
*/
public function getStore()
{
return $this->_storeManager->getStore($this->_store);
}
}

now create collection class at app/code/Webkul/Stripe/Model/ResourceModel/JoinModel/Collection.php:

<?php
/**
* join model collection
*/

namespace Webkul\Stripe\Model\ResourceModel\JoinModel;

use Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection;

/**
* Webkul Stripe ResourceModel JoinModel collection
*/
class Collection extends AbstractCollection
{
/**
* @var string
*/
protected $_idFieldName = 'entity_id';

/**
* Store manager
*
* @var \Magento\Store\Model\StoreManagerInterface
*/
protected $_storeManager;

/**
* @param \Magento\Framework\Data\Collection\EntityFactoryInterface $entityFactory
* @param \Psr\Log\LoggerInterface $logger
* @param \Magento\Framework\Data\Collection\Db\FetchStrategyInterface $fetchStrategy
* @param \Magento\Framework\Event\ManagerInterface $eventManager
* @param \Magento\Store\Model\StoreManagerInterface $storeManager
* @param \Magento\Framework\DB\Adapter\AdapterInterface|null $connection
* @param \Magento\Framework\Model\ResourceModel\Db\AbstractDb|null $resource
*/
public function __construct(
\Magento\Framework\Data\Collection\EntityFactoryInterface $entityFactory,
\Psr\Log\LoggerInterface $logger,
\Magento\Framework\Data\Collection\Db\FetchStrategyInterface $fetchStrategy,
\Magento\Framework\Event\ManagerInterface $eventManager,
\Magento\Store\Model\StoreManagerInterface $storeManager,
\Magento\Framework\DB\Adapter\AdapterInterface $connection = null,
\Magento\Framework\Model\ResourceModel\Db\AbstractDb $resource = null
)
{
parent::__construct($entityFactory, $logger, $fetchStrategy, $eventManager, $connection, $resource);
$this->_storeManager = $storeManager;
}

/**
* Define resource model
*
* @return void
*/
protected function _construct()
{
$this->_init('Webkul\Stripe\Model\JoinModel', 'Webkul\Stripe\Model\ResourceModel\JoinModel');
$this->_map['fields']['entity_id'] = 'main_table.entity_id';
}
/**
* Add filter by store
*
* @param int|array|\Magento\Store\Model\Store $store
* @param bool $withAdmin
* @return $this
*/
public function addStoreFilter($store, $withAdmin = true)
{
if (!$this->getFlag('store_filter_added')) {
$this->performAddStoreFilter($store, $withAdmin);
}
return $this;
}
}

Create api interface file at app/code/Webkul/Stripe/Api/Data/JoinModelInterface.php:

<?php
/**
* Join table data interface
*/

namespace Webkul\Stripe\Api\Data;

/**
* Stripe JoinModel interface.
*
* @api
*/
interface JoinModelInterface
{
/**#@+
* Constants for keys of data array. Identical to the name of the getter in snake case
*/
const ENTITY_ID = 'entity_id';
/**#@-*/

/**
* Get ID.
*
* @return int|null
*/
public function getId();

/**
* Set ID.
*
* @param int $id
*
* @return \Webkul\Stripe\Api\Data\ReasonsInterface
*/
public function setId($id);
}

Your model is ready, we will see some join queries based on the above and magento 2 tables :

Simple Inner Join

/**
         * $orderTable name of order table
         */
        $orderTable = $this->_resource->getTableName('sales_order');
        
        /**
         * $customerTable name of customer table
         */
        $customerTable = $this->_resource->getTableName('customer_entity');

        /**
         * $joinCollection 
         * @var Webkul\Stripe\Model\ResourceModel\JoinModel\CollectionFactory
         */
        $joinCollection = $this->_joinFactory->create();
        $joinCollection
        ->join(
            ['ot'=>$orderTable],
            "main_table.order_id = ot.entity_id"
        );
        
        echo $joinCollection->getSelect();die;

In the above code we have called magento2 msql join function on our collection object which takes two parameters first one is an array with is used for table alias and second parameter is the condition at which it will join the tables, in the last I have printed the real sql query that is created, the output for above code is :

SELECT `main_table`.*, `ot`.* FROM `wk_my_table_join_test` AS `main_table` INNER JOIN `wk_sales_order` AS `ot` ON main_table.order_id = ot.entity_id

You can run this query in the mysql and check the output.

Left Join

   /**
         * $orderTable name of order table
         */
        $orderTable = $this->_resource->getTableName('sales_order');
        
        /**
         * $customerTable name of customer table
         */
        $customerTable = $this->_resource->getTableName('customer_entity');

        /**
         * $joinCollection 
         * @var Webkul\Stripe\Model\ResourceModel\JoinModel\CollectionFactory
         */
        $joinCollection = $this->_joinFactory->create();
        $joinCollection
        ->getSelect()
        ->joinLeft(
            ['ot'=>$orderTable],
            "main_table.order_id = ot.entity_id"
        );
        
        echo $joinCollection->getSelect();die;

The above code simple for left join just use joinLeft method and rest will be the same, left join is used to return all the rows from the left table and matched rows from right table and will set unmatched data to null.

Now we have seen how to create simple join queries, the above queries will fetch all the columns from both the tables. Now we will see how to get selected columns :

       /**
         * $orderTable name of order table
         */
        $orderTable = $this->_resource->getTableName('sales_order');
        
        /**
         * $customerTable name of customer table
         */
        $customerTable = $this->_resource->getTableName('customer_entity');

        /**
         * $joinCollection 
         * @var Webkul\Stripe\Model\ResourceModel\JoinModel\CollectionFactory
         */
        $joinCollection = $this->_joinFactory->create();
        $joinCollection
        ->getSelect()
        ->join(
            ['ot'=>$orderTable],
            "main_table.order_id = ot.entity_id",
            [
                'increment_id' => 'ot.increment_id'
            ]
        );
        
        echo $joinCollection->getSelect();die;

The above code will result is this query :

SELECT `main_table`.*, `ot`.`increment_id` FROM `wk_my_table_join_test` AS `main_table` INNER JOIN `wk_sales_order` AS `ot` ON main_table.order_id = ot.entity_id

When you will run the above query, it will return all the columns from the main table and only increment_id from order table because we have passed only one column in the third parameter of join function you can pass as many columns as you want .

Now we will see how to create conditions or where clause on the join result :

Use Where Clause:

       /**
         * $orderTable name of order table
         */
        $orderTable = $this->_resource->getTableName('sales_order');
        
        /**
         * $customerTable name of customer table
         */
        $customerTable = $this->_resource->getTableName('customer_entity');

        /**
         * $joinCollection 
         * @var Webkul\Stripe\Model\ResourceModel\JoinModel\CollectionFactory
         */
        $joinCollection = $this->_joinFactory->create();
        $joinCollection
        ->getSelect()
        ->join(
            ['ot'=>$orderTable],
            "main_table.order_id = ot.entity_id",
            [
                'increment_id' => 'ot.increment_id',
                'status' => 'ot.state'
            ]
        )
        ->where("status = 'pending'");
        
        echo $joinCollection->getSelect();die;

The above code will result in this query :

SELECT `main_table`.*, `ot`.`increment_id`, `ot`.`state` AS `status` FROM `wk_my_table_join_test` AS `main_table` INNER JOIN `wk_sales_order` AS `ot` ON main_table.order_id = ot.entity_id WHERE (status = 'pending')

in the above code for adding where clause we just called the where method on joined collection which accepts one parameter string .

Now we will see how to use aggregate function like GROUP_CONCAT , COUNT etc :

        /**
         * $orderTable name of order table
         */
        $orderTable = $this->_resource->getTableName('sales_order');
        
        /**
         * $customerTable name of customer table
         */
        $customerTable = $this->_resource->getTableName('customer_entity');

        /**
         * $joinCollection 
         * @var Webkul\Stripe\Model\ResourceModel\JoinModel\CollectionFactory
         */
        $joinCollection = $this->_joinFactory->create();
        $joinCollection
        ->getSelect()
        ->join(
            ['ot'=>$orderTable],
            "main_table.order_id = ot.entity_id",
            [
                'increment_id' => 'ot.increment_id',
                'status' => 'GROUP_CONCAT(ot.state)'
            ]
        )
        ->where("status = 'pending'")
        ->group("main_table.order_id");
        
        echo $joinCollection->getSelect();die;

In the above code I have used group function which accepts one parameter as string. You can pass multiple columns as comma separated. I have used GROUP_CONCAT  aggregate function on column  ‘status’ and grouped the result on ‘order_id’ column. The above code will result in this query :

SELECT `main_table`.*, `ot`.`increment_id`, GROUP_CONCAT(ot.state) AS `status` FROM `wk_my_table_join_test` AS `main_table` INNER JOIN `wk_sales_order` AS `ot` ON main_table.order_id = ot.entity_id WHERE (status = 'pending') GROUP BY `main_table`.`order_id`

The above query will result combining all the same order_id in one row. And will concat all the order statuses in one column as comma separated. If you want to combine distinct values use below code :

        /**
         * $orderTable name of order table
         */
        $orderTable = $this->_resource->getTableName('sales_order');
        
        /**
         * $customerTable name of customer table
         */
        $customerTable = $this->_resource->getTableName('customer_entity');

        /**
         * $joinCollection 
         * @var Webkul\Stripe\Model\ResourceModel\JoinModel\CollectionFactory
         */
        $joinCollection = $this->_joinFactory->create();
        $joinCollection
        ->getSelect()
        ->join(
            ['ot'=>$orderTable],
            "main_table.order_id = ot.entity_id",
            [
                'increment_id' => 'ot.increment_id',
                'status' => 'GROUP_CONCAT(DISTINCT ot.state)'
            ]
        )
        ->where("status = 'pending'")
        ->group("main_table.order_id");
        
        echo $joinCollection->getSelect();die;

Let’s look at the one last example of join multiple tables :

/**
         * $orderTable name of order table
         */
        $orderTable = $this->_resource->getTableName('sales_order');
        
        /**
         * $customerTable name of customer table
         */
        $customerTable = $this->_resource->getTableName('customer_entity');

        /**
         * $joinCollection 
         * @var Webkul\Stripe\Model\ResourceModel\JoinModel\CollectionFactory
         */
        $joinCollection = $this->_joinFactory->create();
        $joinCollection
        ->getSelect()
        ->join(
            ['ot'=>$orderTable],
            "main_table.order_id = ot.entity_id",
            [
                'increment_id' => 'ot.increment_id',
                'status' => 'GROUP_CONCAT(DISTINCT ot.state)'
            ]
        )
        ->join(
            ['ct' => $customerTable],
            "main_table.customer_id = ct.entity_id",
            [
                "customer_name" => "ct.firstname"
            ]
        )
        ->group("main_table.order_id");
        
        echo $joinCollection->getSelect();die;

In the code above, we created the another join to the customer table to fetch the customer’s first name. And the above code will output with this query:

SELECT `main_table`.*, `ot`.`increment_id`, GROUP_CONCAT(DISTINCT ot.state) AS `status`, `ct`.`firstname` AS `customer_name` FROM `wk_my_table_join_test` AS `main_table` INNER JOIN `wk_sales_order` AS `ot` ON main_table.order_id = ot.entity_id INNER JOIN `wk_customer_entity` AS `ct` ON main_table.customer_id = ct.entity_id GROUP BY `main_table`.`order_id`

Hope this will help you in understanding how to write code for join queries in Magento2. Try the code above and in case you are not able to understand anything above please comment below.

Thanks 🙂 .