Magento – How to JOIN TABLES with WHERE clause in Magento 1.9

collection;join-tablemagento-1.9

I want to join two tables, the customer_entity, and customer_entity_varchar. but I have no idea how to do it. This is my current query:

Let's assume I the entity_id is 5 and attribute_id is 139.

$customerCollection = Mage::getModel('customer/customer')->getCollection()->addAttributeToSelect('*')->getSelect()->join( array('varchar'=> 'customer_entity_varchar'), 'varchar.entity_id = main_table.entity_id', array('varchar.attribute_id'))->addAttributeToFilter('entity_id', array('eq' => '5'))->addAttributeToFilter('attribute_id',array('eq' => '139'));

Below is the query I want to achieve.

SELECT attribute_id, entity_id FROM customer_entity INNER JOIN customer_entity_varchar ON customer_entity.entity_id = customer_entity_varchar.entity_id WHERE customer_entity.entity_id = 5 AND customer_entity_varchar.attribute_id = 139

I am stuck with this. I am using Magento 1.9.

Best Answer

use this way:

$customer=Mage::getModel('customer/customer')->getCollection();
$customer->getSelect()->join(array('cev' => Mage::getSingleton('core/resource')->getTableName('customer_entity_varchar')), 'cev.entity_id = e.entity_id')
->where("e.entity_id =5 AND cev.attirbute_id=139");

As I think you cannot use addAttributeToFilter here

Related Topic