Magento 2: join custom table with product collection while sql query is running fine Planned...
Super Attribute Position on Product Page Magento 1
What are the pros and cons of Aerospike nosecones?
How to deal with a team lead who never gives me credit?
If Jon Snow became King of the Seven Kingdoms what would his regnal number be?
How do I keep my slimes from escaping their pens?
Proof involving the spectral radius and the Jordan canonical form
Withdrew £2800, but only £2000 shows as withdrawn on online banking; what are my obligations?
How to do this path/lattice with tikz
What's the difference between `auto x = vector<int>()` and `vector<int> x`?
How do I mention the quality of my school without bragging
Why is black pepper both grey and black?
Disable hyphenation for an entire paragraph
How discoverable are IPv6 addresses and AAAA names by potential attackers?
Diagram with tikz
Output the ŋarâþ crîþ alphabet song without using (m)any letters
I need to find the potential function of a vector field.
What happens to sewage if there is no river near by?
How much radiation do nuclear physics experiments expose researchers to nowadays?
What LEGO pieces have "real-world" functionality?
Do you forfeit tax refunds/credits if you aren't required to and don't file by April 15?
How do I stop a creek from eroding my steep embankment?
Why was the term "discrete" used in discrete logarithm?
How can players work together to take actions that are otherwise impossible?
What is the longest distance a 13th-level monk can jump while attacking on the same turn?
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;
}
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
add a comment |
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
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
add a comment |
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
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
magento2 product-collection join-table
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
add a comment |
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
add a comment |
3 Answers
3
active
oldest
votes
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
add a comment |
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.
add a comment |
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.
add a comment |
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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
add a comment |
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
add a comment |
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");
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");
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
add a comment |
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
add a comment |
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.
add a comment |
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.
add a comment |
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.
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.
answered Jan 26 '18 at 9:28
dudziodudzio
1138
1138
add a comment |
add a comment |
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.
add a comment |
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.
add a comment |
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.
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.
edited 10 mins ago
Vishwas Bhatnagar
2,93422146
2,93422146
answered Feb 20 at 7:45
archana bahadurarchana bahadur
413
413
add a comment |
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
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