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.

3 Answers 3


use this way:

$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

  • Is there any way to add the AND clause to the JOIN clause so that i filter when the JOIN occurs vs after when the join occurs? this makes a big performance difference on larger tables. Oct 9, 2019 at 15:29

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()
            ->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();
  • I got error in main_table.entity_id.
    – larp
    Jun 5, 2018 at 7:18

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

    $customerCollection = Mage::getModel('customer/customer')->getCollection();
    ->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();


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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