How to join tables programmatically in Magento 2

Mai Xuan Truong

Today we talk about How to join tables programmatically in Magento 2. Sometimes you need to get a 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 Magento 2.

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)
How to join tables in SQL

For this sample, we have the following tables:

Director_idName
1Magenest director
2Magenest
3Son Tung
4Cris
5Magento2

movie_idnamedescriptionratingdirector_id
1Harry PotterFantasy11
2PassengerAction22
3InsidiousHorror43
4PokemonAnime44

actor_idname
1Rowan
2Tung
3Satoshi
4Magenest

movie_idactor_id
11
22
23
33
44
41

And their relationship:

Relationship of tables in SQL

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:

moviedescriptionratingdirectorActor
Harry PotterFantasy1Magenest directorRowan
PassengerAction2MagenestTung
PassengerAction2MagenestSatoshi
InsidiousHorror4Son TungSatoshi
PokemonAnime4CrisMagenest
PokemonAnime4CrisRowan

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 
moviedirector
Harry PotterMagenest director
PassengerMagenest
InsidiousSon Tung
PokemonCris
NullMagento2

By using LEFT JOIN in this case, the result is:

moviedirector
Harry PotterMagenest director
PassengerMagenest
InsidiousSon Tung
PokemonCris

Translate SQL Join Table Queries to Magento 2 Codes

We assume the mentioned tables already exist 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_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;

Can be executed in a custom function in the 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:

Movie List

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

In Conclusion,

That will be the end of our guide on how to join tables programmatically in Magento 2. Hopefully, you guys have got the needed information. If not, feel free to leave us your questions here, or if you need help building/optimizing your Magento 2 stores, our experienced & certified Magento 2 developer team and a comprehensive package of Magento 2 development services is more than ready to assist.

Latest Insights

How to Start a Shopify Store in Just Over an Hour Course

Don't want to miss out on our latest insights?
Subscribe to our newsletter.

Disclaimer: By clicking submit, you agree to share your information with us to receive news, announcements, and resources when they are available.