Magento join EAV collection with Flat table

1
2
3
4
5
6
7
8
9
10
11
12
13

$coll = Mage::getModel('sales/order')->getCollection();

$fn = Mage::getModel('eav/entity_attribute')->loadByCode('1', 'firstname');
$ln = Mage::getModel('eav/entity_attribute')->loadByCode('1', 'lastname');

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

print_r($coll->getData());

6 Comments

• 
  Andrew May 5, 2013 at 8:24 pm

  Great! I was searching something like this since last 2 hours.
  Thanks!

  Reply
• 
  Denis October 23, 2013 at 2:55 pm

  Thanks! Thanks! Thanks! Thanks!

  Reply
• 
  Matt Dilley June 4, 2014 at 10:27 am

  great post there Kalpesh!
  I spend the whole day yesterday trying to compose a wishlist collection, and join the customer and product eav elements to it. it was driving me crazy
  I’m just so glad you posted such a useful solution.

  Many many thanks Kalpesh
  btw, I have shared your page with the G+ groups, can I have you permission to reshare the contents of your post onto my own blog? it will help me tremendously 🙂

  Reply
  • 
    kalpesh June 5, 2014 at 3:35 am

    Yes Matt, you can reshare with a backlink to this post.

    Reply
• 
  Bebo June 2, 2015 at 3:13 pm

  Awesome.. Thank you so much! 🙂

  Reply
• 
  irshad November 18, 2018 at 8:53 am

  i am new as a Magento developer. your post is absolutely great to learn and understand its awesome for the project issue.

  Reply