How to Add Join Query in the Magento 2? Learn it with Example

Hello Friends,

In this blog, we will see how to use the join method with multiple tables.

In this example, we will use a join collection of product collection, like that you can also take the category, order or any custom table collection.

We have used two tables one is “catalog_product_entity_media_gallery_value” and the second one is “catalog_product_entity_media_gallery” table to get the image path for each product.

I assume that you already have a collection from any table which contains the entity_id.

$collection = $productCollection->create()->addAttributeToSelect('entity_id');

Now, the join query on the product collection is mentioned below:

$collection->getSelect()->join(
    array('value_entity' => $collection->getTable('catalog_product_entity_media_gallery_value')),
    'e.entity_id = value_entity.entity_id',
    array('value_id')
);

Let me explain the above code step by step:

• Step 1: Here, we used “value_entity” as allies of the “catalog_product_entity_media_gallery_value” table.
  
• Step 2: “e.entity_id” define as the entity_id of the collection object.
  
• Step 3: Now, “value_entity.entity_id” will show the entity_id of the “catalog_product_entity_media_gallery_value” table.
  
• Step 4: Condition, ‘e.entity_id = value_entity.entity_id’ define if both id are same then it will return value_id of the “catalog_product_entity_media_gallery_value” table.

Now, when you print the product collection variable you will see “value_id” of each product. Just print the product collection like as below example:

echo '<pre>';print_r($collection->getData());die('died');

In the above collection, we get the “value_id”, now time to get “value” from “catalog_product_entity_media_gallery” table using “value_id”.

I mean “catalog_product_entity_media_gallery” stores each product image path in the “value” column but if you want that image path then you should have the first “value_id” of each product which we get that from the above join collection.

$collection->getSelect()->join(
    array('value_data' => $collection->getTable('catalog_product_entity_media_gallery')),
    'value_entity.value_id = value_data.value_id',
    array('value')
);

Let me explain the above code step by step:

• Step 1: Here, we used “value_data” as allies of the “catalog_product_entity_media_gallery” table.
• Step 2: Now, “value_entity.value_id” represent the value_id of the ” catalog_product_entity_media_gallery_value” table.
• Step 3: “value_data.value_id” represent the value_id of the “catalog_product_entity_media_gallery” table.
• Step 4: Condition, “value_entity.value_id = value_data.value_id” it check if both tables value_id are same then it will return the value of the main table.

From the above code, you will get each product image path.

Once your join code is completed then just simply reset the column and use group function which returns only unique value from the collection.

$collection->getSelect()
    ->reset(\Zend_Db_Select::COLUMNS)
    ->columns('entity_id')        
    ->group(array('entity_id'));

echo '<pre>';print_r($collection->getData());die('died');

You can print the whole collection to check the proper output using the above code.

Thanks for reading this post.