Mryntu

I trying to join custom table with product collection but I am not getting result. But printed SQL query is running fine

namespace VendorModuleBlockProduct;
use MagentoCatalogApiCategoryRepositoryInterface;
use MagentoCatalogModelProductFactory;
use MagentoFrameworkViewElementTemplateContext;

class ListProduct extends MagentoCatalogBlockProductListProduct
{

protected function _getProductCollection()


 if ($this->_productCollection === null) 
 $layer = $this->getLayer();
 /* @var $layer MagentoCatalogModelLayer */
 if ($this->getShowRootCategory()) 
 $this->setCategoryId($this->_storeManager->getStore()->getRootCategoryId());
 

 // if this is a product view page
 if ($this->_coreRegistry->registry('product')) 
 // get collection of categories this product is associated with
 $categories = $this->_coreRegistry->registry('product')
 ->getCategoryCollection()->setPage(1, 1)
 ->load();
 // if the product is associated with any category
 if ($categories->count()) 
 // show products from this category
 $this->setCategoryId(current($categories->getIterator()));
 
 

 $origCategory = null;
 if ($this->getCategoryId()) 
 try 
 $category = $this->categoryRepository->get($this->getCategoryId());
 catch (NoSuchEntityException $e) 
 $category = null;
 

 if ($category) 
 $origCategory = $layer->getCurrentCategory();
 $layer->setCurrentCategory($category);
 
 
 $this->_productCollection = $layer->getProductCollection();

 $this->prepareSortableFieldsByCategory($layer->getCurrentCategory());

 if ($origCategory) 
 $layer->setCurrentCategory($origCategory);
 
 

$collection = $this->_productCollectionFactory->create();
$collection->addAttributeToSelect('*');
$joinConditions = 'e.entity_id = s.product_id';
$collection->addAttributeToSelect('*');
$collection->getSelect()->join(
 ['s' => 'store_price'],
 $joinConditions,
 []
 )->columns("s.product_price")->where("s.store_id=1");


 //$this->_productCollection->printlogquery(true) 

 return $collection;

 

 /**
 * Retrieve loaded category collection
 *
 * @return AbstractCollection
 */
 public function getLoadedProductCollection()
 
 return $this->_getProductCollection();
 

SQL Query //$this->_productCollection->printlogquery(true)

 SELECT 
 `e`.*, `cat_index`.`position` AS `cat_index_position`, 
 `stock_status_index`.`stock_status` AS `is_salable`, 
 `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, 
 IF(price_index.tier_price IS NOT NULL, 
 LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, 
 `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `s`.`product_price` 
FROM 
 `catalog_product_entity` AS `e` 
INNER JOIN 
 `catalog_category_product_index` AS `cat_index` 
ON 
 cat_index.product_id=e.entity_id AND cat_index.store_id=1 
 AND cat_index.visibility IN(2, 4) AND cat_index.category_id='2' 
 AND cat_index.is_parent=1 
LEFT JOIN 
 `cataloginventory_stock_status` AS `stock_status_index` 
ON 
 e.entity_id = stock_status_index.product_id AND stock_status_index.website_id = 0 AND stock_status_index.stock_id = 1 
INNER JOIN 
 `catalog_product_index_price` AS `price_index` 
ON 
 price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0 
INNER JOIN 
 `store_price` AS `s` ON e.entity_id = s.product_id WHERE (s.store_id = 1) 

I finallly found solution. Actually collection making alias for join table. You don't have to set alias

$joinConditions = 'e.entity_id = store_price.product_id';
$collection->addAttributeToSelect('*');
$collection->getSelect()->join(
 ['store_price'],
 $joinConditions,
 []
 )->columns("store_price.product_price")
 ->where("store_price.store_id=1");

Magento will be much more friendly if you stop using unnecessary rewrites and use Observers more often. For your problem you need to add:

Vendor/Module/etc/frontend/events.xml:

<config xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xsi:noNamespaceSchemaLocation='urn:magento:framework:Event/etc/events.xsd'>
 <event name='catalog_block_product_list_collection'>
 <observer
 name='VendorModuleObserverCatalogProductAddSmtToCollection'
 instance='VendorModuleObserverCatalogProductAddSmtToCollection'
 />
 </event>
</config>

Vendor/Module/Observer/Catalog/Product/AddSmtToCollection.php

namespace VendorModuleObserverCatalogProduct;

use MagentoFrameworkEventObserverInterface;

class AddSmtToCollection implements ObserverInterface

 protected $logger;
 protected $customerSession;
 protected $helper;

 /**
 * @param PsrLogLoggerInterface $logger
 * @param MagedelightCustomerpriceHelperData $helper
 * @param MagentoCustomerModelSession $customerSession
 */
 public function __construct(
 PsrLogLoggerInterface $logger
 ) 
 $this->logger = $logger;
 

 /**
 * @param MagentoFrameworkEventObserver $observer
 */
 public function execute(MagentoFrameworkEventObserver $observer) 
 $ObjectManager = MagentoFrameworkAppObjectManager::getInstance();

 $joinConditions = 'e.entity_id = store_price.product_id';
 $observer->getCollection()->getSelect()->join(
 ['store_price'],
 $joinConditions,
 []
 )->columns("store_price.product_price")
 ->where("store_price.store_id=1");

 

Important note: You should be aware of joining custom tables, if you use flat table for products.

Suppose you want two custom attributes by raw query,

LEFT JOIN catalog_product_entity_varchar AS custom1 ON
(custom1.entity_id = maintable.product_id) AND (custom1.attribute_id =
'370')

LEFT JOIN catalog_product_entity_varchar AS custom2 ON
(custom2.entity_id maintable.product_id) AND (custom2.attribute_id =
'158')

Where as maintainable provides you the product id, and attribute_id are ids of custom attributes you want to fetch.