My experience with Magento Collections so far.

Ok, let me star off by saying I’ve barely scratched the surface of Magento collections but I’ve found them to be a little confusing, and very powerful. So the other day for my job at Discount Party Supplies I had to come up with a solution that would pull tracking numbers that were shipped only by one carrier. Fairly simple and once you grasp how powerful the collections systems are it becomes fairly easy.

First issue tracking down some solid information on Magento Collections. From my experience documentation for Magento is lacking to put it nice. Alan Storm has some good articles and as does Allan MacGregor. Allan MacGregor also has a nice book out, Magento PHP Developer’s Guide. The other places I look frequently are the Magento StackExchange, Magento subreddit, and of course the Magento documentation.

So browsing around I finally find some nice place to start thanks to the IRC room #magento-reddit. There are some awesome people in there that know loads, trust me check it out some time. Starting with the ‘Sales Order Shipment Collection’ I find a way to get tracking numbers and get most of the data I want.

This is what it looks like at first:

	$shipments = Mage::getResourceModel('sales/order_shipment_collection')
		->addAttributeToSelect('*')
		->addAttributeToFilter('main_table.created_at', array(
    		'from' => '2013-09-11 00:00:00',
    		'to' => '2013-09-11 23:59:59'));

Now if you don’t quite understand this I’ll break it down. First we need to tell Magento what resources we are working with, and that is the sales/order_shipment_collection line. Then we have to tell Magento what data we want to select, for this I just wanted all, so I used the ->addAttributeToSelect(‘*’) operator.

Then came the first hurdle for me, getting the filter correct. If you don’t know when you use collections to filter out products, orders, categories, or shipments it gets kinda weird when you want to do a join later on. First thing the first table is main_table inside the SQL statement that will get sent, don’t know why but it is. To test this out you can use the following line to see the SQL statement:

echo $shipments->getSelect();

So now that we have that I knew next I wanted to filter out based on the date for my testing I just used static dates to make it easier on me. This is where the AddAttributeToFilter came into play. Now figuring this out was a pain because Magento has it’s own ways to do this, I recommend using this wiki page as a starting point.

And now I can loop over it and use the method getData() to start finding out where I’m short and what I need to do next.

Now depending on what you need to actually accomplish you may need to do the next part or not.

First issue for me was it gave me every shipment between the time period I stated in the filter. I needed only USPS based orders though so I had to go and find out how to join a table because the carrier was not listed in the shipment table. This was probably the most annoying part because I could not find some resource that explained the join method very well.

After some talks with the great folks in #magento-reddit IRC room I found out that you can’t add a join to the bit of code from above that tells it what to select. You have to add that after the fact and you have to use it after you use the getSelect() method. So this is how I was able to add that join:

$shipments->getSelect()->join("sales_flat_shipment_track", "main_table.entity_id = sales_flat_shipment_track.parent_id", "sales_flat_shipment_track.carrier_code");

Ok, so this just says we are using the same collection from before and we are going to get the SQL then add the join method to it. In my case it was to join with the sales_flat_shipment_tack table so I could also filter out anything that was not USPS shipment.

Now if you are wondering how I got this join to work here is the code I used to do that:

	$shipments = Mage::getResourceModel('sales/order_shipment_collection')
		->addAttributeToSelect('*')
		->addAttributeToFilter('main_table.created_at', array(
    		'from' => '2013-09-11 00:00:00',
    		'to' => '2013-09-11 23:59:59'))
		->addAttributeToFilter("sales_flat_shipment_track.carrier_code", array( "eq"=>"usps" ));
	$shipments->getSelect()->join("sales_flat_shipment_track", "main_table.entity_id = sales_flat_shipment_track.parent_id", "sales_flat_shipment_track.carrier_code");

And as you can expect it was a long process for me to get there. But after that it became very easy to finish this project which was creating a CSV file that had some various bits of information in a nice CSV file so it could be triggered to run every day at some point after shipping is all done.

A simple view of what I had done was this:

$shipments = Mage::getResourceModel('sales/order_shipment_collection')
	->addAttributeToSelect('*')
	->addAttributeToFilter('main_table.created_at', array(
		'from' => '2013-09-11 00:00:00',
    		'to' => '2013-09-11 23:59:59'))
	->addAttributeToFilter("sales_flat_shipment_track.carrier_code", array( "eq"=>"usps" ));
$shipments->getSelect()->join("sales_flat_shipment_track", "main_table.entity_id = sales_flat_shipment_track.parent_id", "sales_flat_shipment_track.carrier_code");
foreach($shipments as $shipment){
	$data = $shipment->getData();
	foreach ($tracking_nums as $tracking) {
		$track_num = $tracking->getNumber();
		//write out the CSV after you have escaped it properly.
	}
}