How do I get a custom customer attribute value from collection issue in Magento 1

Question

I have a custom table and I want to join it with the customer table in order to get a custom customer attribute value. This is my collection:

$collection = Mage::getResourceModel('adyen_subscription/subscription_collection')
   ->addEmailToSelect()
   ->addNameToSelect();
$collection->getSelect()->joinLeft(
   ['cue' => 'customer_entity'],
    'cue.entity_id = main_table.customer_id');
$collection->getSelect()->join( array('ce2' => 'customer_entity_varchar'), 'ce2.entity_id=cue.entity_id', array('custom_customer_attribute_code' => 'value'));

Right now I m getting all of the values from all of the customer attributes. The idea should be

select * from table A left join table customers where A.customer_id = customers.entity_id where custom_customer_attribute_code = 'my_custom_code'

How can I do that ?

Answer 1

With current code you do not filter your collection by specific attribute, you just get all the customer attributes with the backend type "varchar".

To filter collection by specific attribute you need to specify this explicitly:

1. Create attribute instance and load required attribute:

   $entityType     = 'customer';
   $attributeCode  = 'dob';
   $attribute      = Mage::getModel('eav/entity_attribute')->loadByCode($entityType, $attributeCode);
   

2. Join attribute value to your collection:

   $resource = Mage::getSingleton('core/resource');
   $collection->getSelect()->join(
       array('ce2' => $resource->getTableName($entityType . '_entity_' . $attribute->getBackendType())),
       'ce2.entity_id = cue.entity_id AND ce2.attribute_id = ' . $attribute->getId(),
       array($attributeCode => 'ce2.value')
   );
   

---

Complete code may look as follows:

$entityType     = 'customer';
$attributeCode  = 'dob';
$attribute      = Mage::getModel('eav/entity_attribute')->loadByCode($entityType, $attributeCode);
$resource       = Mage::getSingleton('core/resource');

$collection = Mage::getResourceModel('adyen_subscription/subscription_collection')
   ->addEmailToSelect()
   ->addNameToSelect();

$collection->getSelect()
    ->joinLeft(
        array('cue' => 'customer_entity'),
        'cue.entity_id = main_table.customer_id',
        array()
    )
    ->join(
        array('ce2' => $resource->getTableName($entityType . '_entity_' . $attribute->getBackendType())),
        'ce2.entity_id = cue.entity_id AND ce2.attribute_id = ' . $attribute->getId(),
        array($attributeCode => 'ce2.value')
    );