5

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)   
2
  • Try with return $collection in your function. Feb 22, 2017 at 11:19
  • 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
    – Abhishek
    Feb 22, 2017 at 12:41

4 Answers 4

19

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");
5
  • and how do I get the actual items, because there is no getItems after you call getSelect on a collection Nov 22, 2017 at 14:13
  • After return $collection, run foreach loop like foreach($collection as $data){ $data->getData(); $data->getName(); }
    – Abhishek
    Nov 22, 2017 at 16:42
  • 1
    yeah, that is just not true :) Nov 23, 2017 at 8:36
  • why collection does not have joinLeft but only join method, I can't explain as well... :D Whatever, don't bother to answer anything. Nov 23, 2017 at 8:43
  • perfect answer!!! made my day :) +1 Jun 1, 2022 at 17:21
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='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.

1
  • "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?
    – Stack user
    Oct 18, 2019 at 7:02
0

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.

0
        $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);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.