Apr 28, 2013
kalpesh

Magento join EAV collection with Flat table

Joining tables in Magento when it comes to EAV with Flat table is quite complicated. Consider you want to join sales_flat_order table with customer EAV tables to get Customer’s firstname and lastname, it becomes difficult as customer’s name comes from customer_entity_varchar table.

Below code will join sales order flat table with customer EAV to get customer’s full name in the collection along with all the order details.

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

5 Comments

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

  • Thanks! Thanks! Thanks! Thanks!

  • 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 🙂

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

  • Awesome.. Thank you so much! 🙂

Leave a comment

 

Welcome to my Blog

Kalpesh MehtaHelping Magento developers in their day-to-day development problems since 2011. Most of the problems and solutions here are my own experiences while working on different projects. Enjoy the blog and don't forget to throw comments and likes/+1's/tweets on posts you like. Thanks for visiting!

Certifications