首页>Magento>2019-12-05 21:44source

magento 1.9:自定义表左连接目录/产品

我有一个名为 flagged_products的自定义表列出了这样的标记产品：

+-----------+------------+---------------------+
| entity_id | product_id |     flagged_on      |
+-----------+------------+---------------------+
|         1 |     356534 | 2019-03-01 12:45:20 |
|         2 |     654657 | 2019-01-07 05:52:09 |
|         3 |     345477 | 2019-03-23 09:34:07 |
|         4 |     114673 | 2019-04-11 15:45:17 |
+-----------+------------+---------------------+

我希望将其与目录/产品模型结合起来，列出已标记为这样的产品的所有名称：

+-----------+--------------+---------------------+
| entity_id | product_name |     flagged_on      |
+-----------+--------------+---------------------+
|    356534 | Product 1    | 2019-03-01 12:45:20 |
|    654657 | Product 2    | 2019-01-07 05:52:09 |
|    345477 | Product 3    | 2019-03-23 09:34:07 |
|    114673 | Product 4    | 2019-04-11 15:45:17 |
+-----------+--------------+---------------------+

但是，由于产品名称是EAV表的一部分，我不确定如何获取此信息。

非常感谢Magento 1如何实现这一目标

243

magento1.9

magentodatabase

magentocollection

magentocatalog

magentoeav

最新回答

2019-12-5

1 #

如果您想尝试"Magento方式"，您可以尝试以下方式：

$flaggedProducts = Mage::getModel('your_module/your_model')->getCollection();
// Join with product table
$flaggedProducts->getSelect()->joinLeft(
        array('cp' => $flaggedProducts->getTable('catalog/product'),
        'cp.entity_id=main_table.product_id',
        array(
            'product_entity_id' => new Zend_Db_Expr('cp.entity_id')
        )
    );
// Join to get the name
    $nameAttribute = Mage::getSingleton('eav/config')->getAttribute('catalog_product', 'name');
    $nameTable = $nameAttribute->getBackendTable();
    $nameId = $nameAttribute->getAttributeId();
    // Get default store name value
    $flaggedProducts->getSelect()->joinLeft(
        array('nt' => $nameTable),
        'nt.attribute_id=' . $nameId . ' AND nt.entity_id = cp.entity_id AND nt.store_id = 0',
        array(
            'product_name' => new Zend_Db_Expr('nt.value')
        )
    );

如果你只是想做一些sql查询，可能是这样的：

SELECT fp.*, cpev_name.value FROM  flagged_products AS fp
LEFT JOIN catalog_product_entity_varchar AS cpev_name
             ON fp.product_id = cpev_name.entity_id AND cpev_name.attribute_id = 'put-here-the-id-of-the-attribute-name' 
AND cpev_name.store_id = 0

2019-12-5

2 #

请尝试此查询，它会为您提供预期的输出。

select fp.*, cpev.value as product_name from flagged_products as fp
left JOIN catalog_product_entity_varchar as cpev on cpev.entity_id = fp.product_id
WHERE cpev.attribute_id=71

如果您有任何疑问，请告诉我。

2019-12-5

3 #

<?php
$collection = Mage::getModel('catalog/product')
    ->getCollection()
    ->addAttributeToSelect('name')
    ->getSelect()
    ->joinLeft(
        array('flagged_products' => $this->getTable('flagged_products')),
        'e.entity_id = flagged_products.product_id'
    );

Codebug

magento 1.9:自定义表左连接目录/产品

magento1.9

magentodatabase

magentocollection

magentocatalog

magentoeav

相关问题