Gsyfuy

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.