:::: MENU ::::

Magento 2 join multiple tables

Magento 2 join multiple tables

Today we talk about how in Magento 2 join multiple tables. Sometime you need to get join collection with product collection data or category collection data or order collection or custom table collection. So today we focus on how to join two magento 2. Then we talk about multiple table join. Now taking a example of we need to join product collection with custom table. Here we go.

protected $_productCollectionFactory;  


      public function __construct(

            \Magento\Catalog\Model\ResourceModel\Product\CollectionFactory $productCollectionFactory

        ) {


            $this->_productloader = $_productCollectionFactory;

        }

    public getmyCollection(){
    $collection = $this->productCollectionFactory->create();
    $collection->getSelect()->join(
                ['table1join'=>$collection->getTable('table1join')],
                'main_table.entity_id = table1join.product_id',
                ['column1'=>'table1join.column1','column2'=>'table1join.column2']);
    return $collection;
    }

Now we try the example of multiple table join

public getmyCollection(){
    $collection = $this->productCollectionFactory->create();
    $collection->getSelect()->join(
                ['table1join'=>$collection->getTable('table1join')],
                'main_table.entity_id = table1join.product_id',
                ['column1'=>'table1join.column1','column2'=>'table1join.column2']);
    $collection->getSelect()->join(
                ['table1join'=>$collection->getTable('table1join')],
                'main_table.entity_id = table1join.product_id',
                ['table2column1'=>'table1join.column1','table2column1'=>'table1join.column2']);
       $collection->getSelect()->join(
            ['table2join'=>$collection->getTable('table2join')],
            'main_table.entity_id = table2join.product_id',
            ['table3column1'=>'table2join.column1','table3column1'=>'table2join.column2']);
      return $collection;
    }

You can use same example to join in any collection in magento 2.

Give A Rating

Average Rating / 5. Vote Count

Author: Qaisar Satti
Category: Magento 2
Last Modified: July 12, 2018


2 Comments

  • Reply Mudasser Hayat |

    Please tell me that where should I make this method getmyCollection()
    1. Model Class
    2. Resource Model
    3 . Collection Class

    Waiting for your reply.

    public getmyCollection(){
    $collection = $this->productCollectionFactory->create();
    $collection->getSelect()->join(
    [‘table1join’=>$collection->getTable(‘table1join’)],
    ‘main_table.entity_id = table1join.product_id’,
    [‘column1’=>’table1join.column1′,’column2’=>’table1join.column2’]);
    return $collection;
    }

So, what do you think ?