How to Join Product Tables with Custom Table in Magento 2?

There are 3 steps to join product tables with a custom table to get product data without product collection in Magento 2. The following steps are:

1. find your custom table collection
2. To load product data into your collection
3. To obtain other product data

Build your Amazon-like multi-vendor marketplace website @ $249/- now!!!

          Get your free live demo now: Click here

Join Product Tables with Custom Table in Magento 2

Step 1:

To find your custom table collection, you must have the custom table collection class early. $collection = $this->collectionFactory()->create();

$collection->getSelect()->joinLeft(

 Step 2:

To load product data into your collection, you must combine the custom table collection with the “catalog_product_entity” table

$productNameAttributeId = $this->_objectManager

  ['product_entity' => $collection->getTable('catalog_product_entity')],

 'main_table.product_id = product_entity.entity_id',

 ['entity_id', 'sku']

);

Step 3:

To obtain the product data, you must get them from the value table of the product but before that, you must get the attribute ids of product data. Example with:

$productPriceAttributeId = $this->_objectManager

 ->create('Magento\Eav\Model\Config')

 ->getAttribute(\Magento\Catalog\Model\Product::ENTITY, \Magento\Catalog\Api\Data\ProductInterface::NAME)

  ->getAttributeId();

$collection->getSelect()->joinLeft(

 [product_varchar => $collection->getTable(catalog_product_entity_varchar)],

 “product_varchar.entity_id = product_entity.entity_id AND product_varchar.attribute_id = $productNameAttributeId",

  []

)->columns(['product_name' => product_varchar.value']);

Step 4:

Example with product price and product status

 ->create('Magento\Eav\Model\Config')

  ->getAttribute(\Magento\Catalog\Model\Product::ENTITY, \Magento\Catalog\Api\Data\ProductInterface::PRICE)

  ->getAttributeId();

$collection->getSelect()->joinLeft(

[product_decimal => $collection->getTable(catalog_product_entity_decimal)],

 “product_decimal.entity_id = product_entity.entity_id AND product_decimal.attribute_id = $productPriceAttributeId",

  []

)->columns(['product_price' => product_decimal.value']);

$productStatusAttributeId = $this->_objectManager

->create('Magento\Eav\Model\Config')

  ->getAttribute(\Magento\Catalog\Model\Product::ENTITY, \Magento\Catalog\Api\Data\ProductInterface::STATUS)

  ->getAttributeId();

$collection->getSelect()->joinLeft(

  [product_int => $collection->getTable(catalog_product_entity_int)],  “product_int.entity_id = product_entity.entity_id AND product_int.attribute_id = $productStatusAttributeId",

  []

)->columns(['product_status' => product_int.value']);

These steps are used to join the Join Product Tables with Custom Table to Get Product Data without Product Collection.

Looking to launch an online marketplace business with the Magento platform? then click here and get started now!!!