Magento 2: join custom table with product collection while sql query is running fine

Question

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

namespace Vendor\Module\Block\Product;
use Magento\Catalog\Api\CategoryRepositoryInterface;
use Magento\Catalog\Model\ProductFactory;
use Magento\Framework\View\Element\Template\Context;
class ListProduct extends \Magento\Catalog\Block\Product\ListProduct
{
protected function _getProductCollection(){
 if ($this->_productCollection === null) {
 $layer = $this->getLayer();
 /* @var $layer \Magento\Catalog\Model\Layer */
 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) 

• Rakesh Jesadiya: Try with return $collection in your function.
• Abhishek: Hi Rakesh.. It was by mistake I have updated code. But It is not working. It seems like problem in joining custom table. But not able to figure it out

Answer 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");

• Lachezar Raychev: and how do I get the actual items, because there is no getItems after you call getSelect on a collection
• Abhishek: After return $collection, run foreach loop like foreach($collection as $data){ $data->getData(); $data->getName(); }
• Lachezar Raychev: yeah, that is just not true :)
• Lachezar Raychev: why collection does not have joinLeft but only join method, I can't explain as well... :D Whatever, don't bother to answer anything.
• SagarPPanchal: perfect answer!!! made my day :) +1

Answer 2

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='Vendor\Module\Observer\Catalog\Product\AddSmtToCollection'
 instance='Vendor\Module\Observer\Catalog\Product\AddSmtToCollection'
 />
 </event>
</config>

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

namespace Vendor\Module\Observer\Catalog\Product;
use Magento\Framework\Event\ObserverInterface;
class AddSmtToCollection implements ObserverInterface
{
 protected $logger;
 protected $customerSession;
 protected $helper;
 /**
 * @param \Psr\Log\LoggerInterface $logger
 * @param \Magedelight\Customerprice\Helper\Data $helper
 * @param \Magento\Customer\Model\Session $customerSession
 */
 public function __construct(
 \Psr\Log\LoggerInterface $logger
 ) {
 $this->logger = $logger;
 }
 /**
 * @param \Magento\Framework\Event\Observer $observer
 */
 public function execute(\Magento\Framework\Event\Observer $observer) {
 $ObjectManager = \Magento\Framework\App\ObjectManager::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.

• Stack user: "You should be aware of joining custom tables, if you use flat table for products." I did not get this. what is the issue in that?

Answer 3

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.

Answer 4

 $products->getSelect()->joinLeft(
 ['ot'=>$buySellTable],
 "ot.inv_id = e.entity_id",
 [
 'ot.sold_date','ot.listed_date','ot.purchase_price','ot.stock_number'
 ]
 )->where('ot.purchase_price <= ?', $priceToCheck);