Magento 1 & 2: Join Collection to get Customer Fullname

This article shows how you can use the join query on any custom collection to get the customer firstname, lastname and contact firstname & lastname to get the customer full name.

I will be showing example code for both Magento version 1.x and Magento version 2.x.

Magento 1: Join Collection to get Customer Fullname

I suppose that you already have a collection from any table which contains the customer_id.

$collection = Mage::getModel('yourModule/yourModel')->getCollection();

Now, we fetch the attribute id for customer firstname and customer lastname.

$firstnameAttributeId = Mage::getModel('eav/entity_attribute')->loadByCode('customer', 'firstname')->getAttributeId();

$lastnameAttributeId = Mage::getModel('eav/entity_attribute')->loadByCode('customer', 'lastname')->getAttributeId();

And then, use those attribute ids in the join query.

Note: Your collection table should contain the column/field named as ‘customer_id’. If the customer id field has a different name then you need to update the below code “main_table.customer_id” with your main_table’s field name.

$collection->getSelect()
            ->join(array('ce1' => 'customer_entity_varchar'), 'ce1.entity_id=main_table.customer_id', array('firstname' => 'value'))
            ->where('ce1.attribute_id='.$firstnameAttributeId) 
            ->join(array('ce2' => 'customer_entity_varchar'), 'ce2.entity_id=main_table.customer_id', array('lastname' => 'value'))
            ->where('ce2.attribute_id='.$lastnameAttributeId) 
            ->columns(new Zend_Db_Expr("CONCAT(`ce1`.`value`, ' ',`ce2`.`value`) AS fullname"))
        ; 

Magento 2: Join Collection to get Customer Fullname

I tried the above join query code in Magento 2 but it didn’t work.

Here’s the working code on Magento 2:

I suppose that you already have a collection from any table which contains the customer_id.

$collection = $this->yourModel->getCollection();

Now, the join query on the collection:

$collection->getSelect()
            ->join(array('ce1' => 'customer_entity'), 'ce1.entity_id=main_table.customer_id', array('firstname' => 'firstname', 'lastname' => 'lastname'))
            ->columns(new \Zend_Db_Expr("CONCAT(`ce1`.`firstname`, ' ',`ce1`.`lastname`) AS fullname"))
            ;

Hope this helps. Thanks.