2

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)   
  • Try with return $collection in your function. – Rakesh Jesadiya Feb 22 '17 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 '17 at 12:41
9

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");
  • and how do I get the actual items, because there is no getItems after you call getSelect on a collection – Lachezar Raychev Nov 22 '17 at 14:13
  • After return $collection, run foreach loop like foreach($collection as $data){ $data->getData(); $data->getName(); } – Abhishek Nov 22 '17 at 16:42
  • yeah, that is just not true :) – Lachezar Raychev Nov 23 '17 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. – Lachezar Raychev Nov 23 '17 at 8:43
0

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.

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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