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

There are 3 steps to join product tables with 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 a product data in to your collection
  3. To obtain other product data

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 a product data in to your collection, you must combine the custom table collection with “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 value table of 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.

Last Update: April 9, 2018  

February 5, 2018   149   Nandini R    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
Google+
http://www.webnexs.com/blog/kb/join-product-tables-custom-table-get-product-data-without-product-collection
Twitter
LinkedIn