Magento 2 – Join Custom Table with Product Collection

join-tablemagento2product-collection

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)   

Best Answer

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");
Related Topic