# custom table left join with catalog/product

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

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  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. <?php$collection = Mage::getModel('catalog/product')
->getCollection()

• I want the flagged_products table to be on the left. – yd87 May 21 at 14:26