Magento – How to get a custom customer attribute value from collection issue in Magento 1

collection;magento-1

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 ?

Best Answer

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:

Create attribute instance and load required attribute:

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

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')
    );

Related Solutions

Magento – Join Customer Address custom attribute to Order Address Collection

Once you call getSelect on a collection, you will be working with a Zend_Db_Select object. You can read about it here:

http://framework.zend.com/manual/1.12/en/zend.db.select.html#zend.db.select.building.join

Now regarding your question, you didn't mention how you've installed the gmap_lat and gmap_lng attributes. I'm assuming you've done so as an EAV attribute. As such, EAV makes it hard for us to "simply" join with the sales_flat_order_address info. Here's what it takes:

$orderAddresses = Mage::getResourceModel('sales/order_address_collection');
$read           = Mage::getSingleton('core/resource')->getConnection('core_read');
$gmapLat        = Mage::getModel('eav/entity_attribute')->loadByCode('customer_address', 'gmap_lat'); 
$gmapLng        = Mage::getModel('eav/entity_attribute')->loadByCode('customer_address', 'gmap_lng'); 

$orderAddresses->getSelect()
    ->joinLeft(
        array('caev_gmap_lat' => 'customer_address_entity_varchar'),
        '(caev_gmap_lat.entity_id = main_table.customer_address_id) AND ' . 
        '(' . $read->quoteInto('caev_gmap_lat.attribute_id = ?', $attribute->getId()) . ')',
        array('gmap_lat' => 'caev_gmap_lat.value')
    )
    ->joinLeft(
        array('caev_gmap_lng' => 'customer_address_entity_varchar'),
        '(caev_gmap_lng.entity_id = main_table.entity_id) AND ' . 
        '(' . $read->quoteInto('caev_gmap_lng.attribute_id = ?', $attribute->getId()) . ')',
        array('gmap_lat' => 'caev_gmap_lng.value')
    );

Here's a breakdown of what it does:

Get the sales order address collection
Get an instance of a DB read connection
Load the gmap_lat and gmap_lng attribute models
Join them with the address collection

Fetching the attribute models allows you to consistently get the right attribute ID, which you'll need to left join the data to the main table.

Note that I'm assuming your attributes were created as varchar in type. Adapt the tables if you store them as another type.

Magento – Filtering an order collection by a custom customer attribute

If it works it is ok. You have the customer_id in the order and you can join into the varchar table. I think it is fine.

Best Answer

Related Solutions

Magento – Join Customer Address custom attribute to Order Address Collection

Magento – Filtering an order collection by a custom customer attribute

Related Topic