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!='' && 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
Leave a comment
Welcome to my Blog
Certifications
Honor
Recognition
Contributions
Categories
- Apache (2)
- ChatGPT (1)
- Domain name (2)
- eCommerce (2)
- htaccess (1)
- Humor (3)
- Instagram API (1)
- jQuery (4)
- JSON (1)
- Linux (10)
- Magento (142)
- Magento admin (58)
- Magento Certification (5)
- Magento error (13)
- Magento frontend (68)
- Magento Imagine (2)
- Magento Interview (5)
- Magento Master (2)
- Magento2 (10)
- Mobile (1)
- MySQL (7)
- OpenAI (1)
- OroCRM (2)
- Performance (2)
- PHP (8)
- Prototype JS (3)
- Security (4)
- Wordpress (3)
- XML (2)
Tag Cloud
500 internal server error admin answers attribute bug category checkbox checkout cookie customer difference domain name EAV error event extension interview invoice jquery linux magento magento2 magento admin magento error magento interview questions magento orm mysql observer order pinterest product products questions redirect register remove script session simplexml to array state status study guide tax url wordpress
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