0

I have a custom table called flagged_products which lists flagged products like this:

+-----------+------------+---------------------+
| 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 |
+-----------+------------+---------------------+

I want to join this with the catalog/product model to list all the names of the products which have been flagged like this:

+-----------+--------------+---------------------+
| 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 |
+-----------+--------------+---------------------+

However, since the product name is a part of the EAV tables, i'm not really sure how to get this information.

Would appreciate help on how this can be achieved with Magento 1

1

If you want to try the "Magento way", you could try something like :

$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')
        )
    );

If you just want to do some sql query, maybe something like :

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
1

Please try this query it will give you expected output.

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

Let me know if you have any query.

0
<?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'
    );
  • I want the flagged_products table to be on the left. – yd87 May 21 at 14:26
  • Code updated, please check – Saphal Jha May 21 at 16:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.