Dec 31, 2011
kalpesh

Magento: Joining/group by – order, invoice, shipment tables

Ever tried to join multiple tables in Magento? Joining and grouping tables is not that easy in Magento ORM than in simple MySql way we did for years.

Here is a piece of code where I was required to grab all the data from orders, invoices, shipments to generate reports for warehouse people to make their life easier.

$collection = Mage::getResourceModel('sales/order_shipment_item_collection');
$collection->addFieldToSelect('section_id');
$collection->addFieldToSelect('shelf_id');
$collection->addFieldToSelect('rack_id');
$collection->addFieldToSelect('box_id');
//$collection->addFieldToSelect('GROUP_CONCAT(DISTINCT main_table.product_id SEPARATOR ",")','product_ids');</p>
$collection->getSelect()->joinLeft('sales_flat_shipment_grid', 'main_table.parent_id = sales_flat_shipment_grid.entity_id', array('increment_id as shipment_increment_id', 'created_at as shipment_created_at', 'order_increment_id','total_qty'));
$collection->getSelect()->joinLeft('sales_flat_shipment_track', 'sales_flat_shipment_grid.entity_id = sales_flat_shipment_track.parent_id', array('number as track_id', 'carrier_code as carrier_name'));
$collection->getSelect()->joinLeft('sales_flat_order', 'sales_flat_shipment_track.order_id = sales_flat_order.entity_id', array('created_at as order_created_at','grand_total as ord_grand_total'));
$collection->getSelect()->joinLeft('sales_flat_order_item', 'main_table.order_item_id = sales_flat_order_item.item_id', array('product_type'));
$collection->getSelect()->joinLeft('sales_flat_order_payment', 'sales_flat_shipment_track.order_id = sales_flat_order_payment.parent_id', array('method as payment_method'));
$collection->getSelect()->joinLeft('sales_flat_order_address', 'sales_flat_shipment_track.order_id = sales_flat_order_address.parent_id', array('firstname','lastname','street','city','postcode','region','telephone'));
$collection->getSelect()->joinLeft('sales_flat_invoice', 'sales_flat_shipment_track.order_id = sales_flat_invoice.order_id', array('grand_total'));
//$collection->addAttributeToFilter('main_table.price', array('gt' => 0));
if($pickup_ids!='' &#038;&#038; is_array($pickup_ids))
    $collection->addAttributeToFilter('sales_flat_shipment_grid.entity_id', array('in' => $pickup_ids));
$collection->getSelect()->columns(
    array('product_ids' => new Zend_Db_Expr(
        "IFNULL(GROUP_CONCAT(DISTINCT main_table.product_id SEPARATOR ';'), '')"
)));
$collection->getSelect()->columns(
    array('weight' => new Zend_Db_Expr(
        "IFNULL(SUM(main_table.weight)/2, '')"
)));
$collection->getSelect()->group('shipment_increment_id');
//$collection->getSelect()->group('track_id');
//Mage::log($collection->getSelect()->__toString());
$this->setCollection($collection);
//$this->getCollection()->getSelect()->limit();

Although the query is so expensive, I have not yet tried to optimize it due to lack of time.

5 Comments

  • Hi Kalpesh,

    Thanks for your article.

    I also need to generate such kind of report with details related to each order. Just like if you fetch any order i need the details e.g. Order no, Order date, Dispatch date, invoice no, shipment no,Customer Name, Address, Pincode… etc.

    Please let me know how to do the same in magento.

    Thanks,
    Nilesh

  • I was create my table “contact us” , in which I have a date of contact.
    another table is Magento’s ‘sales_flat_order_grid’ which also have created_at field.

    now My problem is that,

    I have to display data from both the tables whose date is between user’s entered date value.

    I m new in Magento.

  • Very nice…

  • Hi Kalpesh ,

    Thanx for sharing the great article.but I need to use this script in my model and not in the collection.
    Can you guide as to how can we use this same logic in the model of my custom module.
    I am a newbie to Magento.

    Thanks,
    Prerna

  • hi kalpesh

    kalpesh , you have crated any t -shirt design module please share the t shirt design module code and screen short for magento

    Thanks
    Ashvin Solanki

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

Honor

Recognition

Magento top 50 contributors

Magento top 50 contributors

Contributions