Join tables programmatically in Magento 2
Today we talk about How to join tables programmatically in Magento 2. Sometimes you need to get join collection with product collection data or category collection data or order collection or custom table collection. In order to make you do that with ease, the developer team from Magenest recommends the topic join tables in Magento2. Here we go.
How to join tables in SQL ?
A SQL Join statement is used to combine data or rows from two or more tables based on a common field between them. Different types of Joins are:
- INNER JOIN: Returns records that have matching values in both tables.
- LEFT JOIN: Returns all records from the left table, and the matched records from the right table.
- RIGHT JOIN: Returns all records from the right table, and the matched records from the left table.
- FULL JOIN: Returns all records from both tables. ( we don’t use this much)
For this sample, we have the following tables:
Director_idName1Magenest director2Magenest3Son Tung4Cris5Magento2Magenest_director
movie_idnamedescriptionratingdirector_id1Harry PotterFantasy112PassengerAction223InsidiousHorror434PokemonAnime44Magenest_movie
actor_idname1Rowan2Tung3Satoshi4MagenestMagenest_actor
movie_idactor_id112223334441Magenest_movie_actor
And their relationship:
We want to get all records that have matching values from Magenest_movie, Magenest_director, and Magenest_actor. The query would be as follows:
SELECT `main_table`.name AS `movie`, `main_table`.description ,`main_table`.rating , `magenest_director`.`name` AS `director`, `magenest_actor`.name AS `actor` FROM `magenest_movie` AS `main_table` INNER JOIN `magenest_director` ON main_table.director_id=magenest_director.director_id INNER JOIN `magenest_movie_actor` ON main_table.movie_id=magenest_movie_actor.movie_id INNER JOIN `magenest_actor` ON magenest_actor.actor_id=magenest_movie_actor.actor_id;
Result:
moviedescriptionratingdirectorActorHarry PotterFantasy1Magenest directorRowanPassengerAction2MagenestTungPassengerAction2MagenestSatoshiInsidiousHorror4Son TungSatoshiPokemonAnime4CrisMagenestPokemonAnime4CrisRowan
Next, get the all director’s names and the matched movie’s names:
SELECT `main_table`.name as `movie`,`magenest_director`.name AS `director` FROM `magenest_movie` AS `main_table` RIGHT JOIN `magenest_director` ON main_table.director_id=magenest_director.director_id
moviedirectorHarry PotterMagenest directorPassengerMagenestInsidiousSon TungPokemonCrisNullMagento2
By using LEFT JOIN in this case, the result is:
moviedirectorHarry PotterMagenest directorPassengerMagenestInsidiousSon TungPokemonCris
Translate SQL join table queries to Magento 2 codes
We assume the mentioned tables already exists in Magento with their Model, Resource Model, and Collection classes:
We start with the collection class of movie table:
<?php
namespace Vendor\Namespace\Model\ResourceModel\Movie;
class Collection extends \Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection
{
protected function _construct()
{
$this->_init('Vendor\Namespace\Model\Movie', 'Vendor\Namespace\Model\ResourceModel\Movie');
}
}
The query below:
SELECT `main_table`.name AS `movie`, `main_table`.description ,`main_table`.rating , `magenest_director`.`name` AS `director`, `magenest_actor`.name AS `actor` FROM `magenest_movie` AS `main_table` INNER JOIN `magenest_director` ON main_table.director_id=magenest_director.director_idINNER JOIN `magenest_movie_actor` ON
main_table.movie_id=magenest_movie_actor.movie_id INNER JOIN `magenest_actor` ON magenest_actor.actor_id=magenest_movie_actor.actor_id;
Can be executed in a custom function in Collection class as:
public function joinTable(){
$actorTable = $this->getTable('magenest_actor');
$actormovieTable = $this->getTable('magenest_movie_actor');
$directorTable = $this->getTable('magenest_director');
$result = $this
->addFieldToSelect('name','movie')
->addFieldToSelect('description')
->addFieldToSelect('rating')
->join($directorTable, 'main_table.director_id='.$directorTable.'.director_id',['director' => 'name'])
->join($actormovieTable,'main_table.movie_id='.$actormovieTable.'.movie_id',null)
->join($actorTable,$actorTable.'.actor_id='.$actormovieTable.'.actor_id',['actor' => 'name']);
return $result->getSelect();
}
Note: Inside Magenest_movie’s collection, Magenest_movie auto return main_table.
Therefore, Here’s the query and the results:
LEFT JOIN and RIGHT JOIN can be executed similarly. Here’s an example using RIGHT JOIN
SELECT `main_table`.name as `movie`,`magenest_director`.name AS `director`
FROM `magenest_movie` AS `main_table`
RIGHT JOIN `magenest_director` ON main_table.director_id=magenest_director.director_id
ublic function testJoinRight(){
$directorTable = $this->getTable('magenest_director');
$join = $this->addFieldToSelect('name','movie')
->getSelect()
->joinRight($directorTable,
'main_table.director_id='.$directorTable.'.director_id',
['director' => 'name']);
return $join;
}
Note: More directives can be found under Magento\Framework\Db\Select
Source: https://magenest.com/en/how-to-join-tables-programmatically-in-magento2/
#join_tables_programmatically_in_magento2
#magenest
#onestopsolution