Magento – Join 3 tables in Magento 2

collection;databasejoin;magento2magento2.1.0

I am new to magento 2, And want to join 4 tables.

TABLE 1) market_p_userdata (columns -> seller_id, image)
TABLE 2) market_product (columns -> seller_id, magepro_id)
TABLE 3) catalog_category_product (columns -> product_id)
TABLE 4) customer_entity (columns-> entity_id)

TABLE 3) product_id (primary key) which is as a foriegn key in TABLE 2) named as magepro_id,

TABLE 4) enity_id(primary key) which is foreign key in TABLE 3) AND 2) named as seller_id,

TABLE 2) seller_id(primary key) which is foreign key in TABLE 1) named as seller_id,

I want to get Image of seller and name of seller, I have searched many links, But still I am not getting how to create join.

Best Answer

Here is the simple example how you can join multiple tables in Magento

   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')],
                'e.entity_id = table1join.product_id',
                ['column1'=>'table1join.column1','column2'=>'table1join.column2']);
    $collection->getSelect()->join(
                ['table1join'=>$collection->getTable('table1join')],
                'e.entity_id = table1join.product_id',
                ['table2column1'=>'table1join.column1','table2column1'=>'table1join.column2']);
       $collection->getSelect()->join(
            ['table2join'=>$collection->getTable('table2join')],
            'e.entity_id = table2join.product_id',
            ['table3column1'=>'table2join.column1','table3column1'=>'table2join.column2']);
}
Related Topic