How to Join Product Tables with Custom Table to Get Product Data without Product Collection

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
Get your free Magento store development consultation today!!!

Join Product Tables with Custom Table to Get Product Data 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.How To Join Product Table with Custom Table to Get Product data In Magento 2?

Last Update: February 10, 2021  

February 5, 2018   1810   Nandini Ramachandran    Magento 2 Tutorial, Products    
Total 1 Votes:
0

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Leave a Reply

Your email address will not be published. Required fields are marked *

Facebook
Twitter
INSTAGRAM
LinkedIn