How to JOIN TABLES with WHERE clause in Magento 1.9?

Question

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.

Answer 1

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

Answer 2

You need to define table alias in addAttributeToFilter for your query, like, main_table.entity_id and varchar.attribute_id

 $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('main_table.entity_id', array('eq' => '5'))
            ->addAttributeToFilter('varchar.attribute_id', array('eq' => '139'));
    echo $customerCollection->getSelect();

Answer 3

Try to execute the same and debug. you will get to know.

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

   echo $customerCollection->getSelect(); exit();