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;
}
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;
}
$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.