Magento 2: join custom table with product collection while sql query is running fine Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern) Announcing the arrival of Valued Associate #679: Cesar Manara Unicorn Meta Zoo #1: Why another podcast?How to join the three tables eav_attribute, catalog_eav_attribute, eav_attribute_label using attribute_id in magento 2Magento 2.2.5: how to query in observer best practiceHow to add more attributes to product collection`getLastPageNumber()` returning wrong number of pagesMagento 2 - Product images from custom blockLayered navigation for custom collection on custom page - magento2Indexer query error empty valuesMagento 2 - Layered Navigation for Custom Product CollectionMagento more than 61 product attribute database tables in join queryMagento 2: Filter by custom product/category attribute returns 0 resultsMagento 2.2.6: Product collection does not apply the filterGet Top Rated Products and Order by rating

Do I really need recursive chmod to restrict access to a folder?

Check which numbers satisfy the condition [A*B*C = A! + B! + C!]

Diagram with tikz

What does '1 unit of lemon juice' mean in a grandma's drink recipe?

What LEGO pieces have "real-world" functionality?

I need to find the potential function of a vector field.

Models of set theory where not every set can be linearly ordered

How to motivate offshore teams and trust them to deliver?

Bonus calculation: Am I making a mountain out of a molehill?

How can I fade player when goes inside or outside of the area?

Antler Helmet: Can it work?

Do you forfeit tax refunds/credits if you aren't required to and don't file by April 15?

Can inflation occur in a positive-sum game currency system such as the Stack Exchange reputation system?

Are my PIs rude or am I just being too sensitive?

What is this single-engine low-wing propeller plane?

Stars Make Stars

What do you call a plan that's an alternative plan in case your initial plan fails?

What's the difference between `auto x = vector<int>()` and `vector<int> x`?

What's the purpose of writing one's academic bio in 3rd person?

Should I call the interviewer directly, if HR aren't responding?

How much radiation do nuclear physics experiments expose researchers to nowadays?

Is the address of a local variable a constexpr?

Is there a concise way to say "all of the X, one of each"?

How to do this path/lattice with tikz



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



Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern)
Announcing the arrival of Valued Associate #679: Cesar Manara
Unicorn Meta Zoo #1: Why another podcast?How to join the three tables eav_attribute, catalog_eav_attribute, eav_attribute_label using attribute_id in magento 2Magento 2.2.5: how to query in observer best practiceHow to add more attributes to product collection`getLastPageNumber()` returning wrong number of pagesMagento 2 - Product images from custom blockLayered navigation for custom collection on custom page - magento2Indexer query error empty valuesMagento 2 - Layered Navigation for Custom Product CollectionMagento more than 61 product attribute database tables in join queryMagento 2: Filter by custom product/category attribute returns 0 resultsMagento 2.2.6: Product collection does not apply the filterGet Top Rated Products and Order by rating



.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;








2















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)









share|improve this question
























  • 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

















2















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)









share|improve this question
























  • 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













2












2








2


1






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)









share|improve this question
















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)






magento2 product-collection join-table






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Feb 22 '17 at 12:40







Abhishek

















asked Feb 22 '17 at 10:53









AbhishekAbhishek

443823




443823












  • 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

















  • 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
















Try with return $collection in your function.

– Rakesh Jesadiya
Feb 22 '17 at 11:19





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





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










3 Answers
3






active

oldest

votes


















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





share|improve this answer























  • 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='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.






share|improve this answer






























    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.






    share|improve this answer

























      Your Answer








      StackExchange.ready(function()
      var channelOptions =
      tags: "".split(" "),
      id: "479"
      ;
      initTagRenderer("".split(" "), "".split(" "), channelOptions);

      StackExchange.using("externalEditor", function()
      // Have to fire editor after snippets, if snippets enabled
      if (StackExchange.settings.snippets.snippetsEnabled)
      StackExchange.using("snippets", function()
      createEditor();
      );

      else
      createEditor();

      );

      function createEditor()
      StackExchange.prepareEditor(
      heartbeatType: 'answer',
      autoActivateHeartbeat: false,
      convertImagesToLinks: false,
      noModals: true,
      showLowRepImageUploadWarning: true,
      reputationToPostImages: null,
      bindNavPrevention: true,
      postfix: "",
      imageUploader:
      brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
      contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
      allowUrls: true
      ,
      onDemand: true,
      discardSelector: ".discard-answer"
      ,immediatelyShowMarkdownHelp:true
      );



      );













      draft saved

      draft discarded


















      StackExchange.ready(
      function ()
      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fmagento.stackexchange.com%2fquestions%2f161243%2fmagento-2-join-custom-table-with-product-collection-while-sql-query-is-running%23new-answer', 'question_page');

      );

      Post as a guest















      Required, but never shown

























      3 Answers
      3






      active

      oldest

      votes








      3 Answers
      3






      active

      oldest

      votes









      active

      oldest

      votes






      active

      oldest

      votes









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





      share|improve this answer























      • 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















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





      share|improve this answer























      • 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













      9












      9








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





      share|improve this 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");






      share|improve this answer












      share|improve this answer



      share|improve this answer










      answered Mar 7 '17 at 13:32









      AbhishekAbhishek

      443823




      443823












      • 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

















      • 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
















      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





      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






      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





      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





      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='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.






      share|improve this answer



























        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='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.






        share|improve this answer

























          0












          0








          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='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.






          share|improve this answer













          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.







          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Jan 26 '18 at 9:28









          dudziodudzio

          1138




          1138





















              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.






              share|improve this answer





























                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.






                share|improve this answer



























                  0












                  0








                  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.






                  share|improve this answer















                  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.







                  share|improve this answer














                  share|improve this answer



                  share|improve this answer








                  edited 15 mins ago









                  Vishwas Bhatnagar

                  2,93422146




                  2,93422146










                  answered Feb 20 at 7:45









                  archana bahadurarchana bahadur

                  413




                  413



























                      draft saved

                      draft discarded
















































                      Thanks for contributing an answer to Magento Stack Exchange!


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid


                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.

                      To learn more, see our tips on writing great answers.




                      draft saved


                      draft discarded














                      StackExchange.ready(
                      function ()
                      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fmagento.stackexchange.com%2fquestions%2f161243%2fmagento-2-join-custom-table-with-product-collection-while-sql-query-is-running%23new-answer', 'question_page');

                      );

                      Post as a guest















                      Required, but never shown





















































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown

































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown







                      Popular posts from this blog

                      Disable / Remove link to Product Items in Cart Planned maintenance scheduled April 23, 2019 at 23:30 UTC (7:30pm US/Eastern) Announcing the arrival of Valued Associate #679: Cesar Manara Unicorn Meta Zoo #1: Why another podcast?How can I limit products that can be bought / added to cart?Remove item from cartHide “Add to Cart” button if specific products are already in cart“Prettifying” the custom options in cart pageCreate link in cart sidebar to view all added items After limit reachedLink products together in checkout/cartHow to Get product from cart and add it againHide action-edit on cart page if simple productRemoving Cart items - ObserverRemove wishlist items when added to cart

                      Helsingin valtaus Sisällysluettelo Taustaa | Yleistä sotatoimista | Osapuolet | Taistelut Helsingin ympäristössä | Punaisten antautumissuunnitelma | Taistelujen kulku Helsingissä | Valtauksen jälkeen | Tappiot | Muistaminen | Kirjallisuutta | Lähteet | Aiheesta muualla | NavigointivalikkoTeoksen verkkoversioTeoksen verkkoversioGoogle BooksSisällissota Helsingissä päättyi tasan 95 vuotta sittenSaksalaisten ylivoima jyräsi punaisen HelsinginSuomalaiset kuvaavat sotien jälkiä kaupungeissa – katso kuvat ja tarinat tutuilta kulmiltaHelsingin valtaus 90 vuotta sittenSaksalaiset valtasivat HelsinginHyökkäys HelsinkiinHelsingin valtaus 12.–13.4. 1918Saksalaiset käyttivät ihmiskilpiä Helsingin valtauksessa 1918Teoksen verkkoversioTeoksen verkkoversioSaksalaiset hyökkäävät Etelä-SuomeenTaistelut LeppävaarassaSotilaat ja taistelutLeppävaara 1918 huhtikuussa. KapinatarinaHelsingin taistelut 1918Saksalaisten voitonparaati HelsingissäHelsingin valtausta juhlittiinSaksalaisten Helsinki vuonna 1918Helsingin taistelussa kaatuneet valkokaartilaisetHelsinkiin haudatut taisteluissa kaatuneet punaiset12.4.1918 Helsingin valtauksessa saksalaiset apujoukot vapauttavat kaupunginVapaussodan muistomerkkejä Helsingissä ja pääkaupunkiseudullaCrescendo / Vuoden 1918 Kansalaissodan uhrien muistomerkkim

                      Adjektiivitarina Tarinan tekeminen | Esimerkki: ennen | Esimerkki: jälkeen | Navigointivalikko