Home > Magento, Programming > View and Filter Orders Grid with Product SKU

View and Filter Orders Grid with Product SKU

March 3rd, 2012

One of the most bewildering missing features I have come across in Magento is the inability to look up orders by ordered products. There are occasions where this feature is sorely needed. For example, you may discover a product needs to be recalled and need a list of every order in the past month containing that product. I discovered I wasn’t the only soul begging for this feature. There is one solution posted which worked but had a few problems. To be precise, it breaks the pager, it replaces a core file and filtering doesn’t work. So, I’ve fixed the major problems and packaged and released it as a free module on github!

This module adds a column to the orders grid which displays either the product name and ordered quantities or the product SKU and ordered quantities which is configurable under Sales > Orders Grid Products Column. The one not selected to be displayed will be added to the title attribute so it can be seen with a mouse hover. The column is filterable by either the product name or SKU depending on the configuration.

Enjoy!

Magento, Programming

  • Tom

    Works like a champ!  I had created a similar module using the Inchoo method but you can’t search by sku or product name using their method.  Can search with your method. One thing missing is item quantities per item.  This is something that we depend on so I will dig in a bit more to see if it can be added simply.  Any suggestions?  Thanks for contributing this to the community.

  • http://colin.mollenhour.com Colin Mollenhour

    Hi Tom. Group_concat has it’s limitations so probably the best way would be to write a custom column renderer that renders the qtys. I’ll see if I can cook something up quickly.

  • Tom

    Got it.  I prefer the product names so commented out SKUs and change the SQL in the ‘names’ var to:

    ‘names’ => new Zend_Db_Expr(‘group_concat(concat( ‘(‘, TRUNCATE(`sales/order_item`.qty_ordered, 0), ‘) ‘, `sales/order_item`.name) SEPARATOR “”)’),

    Will create something like this in the grid:

    (1) Product 1
    (14) Product 2
    (3) Product 3

    Thanks again for the post.

    Tom

  • http://colin.mollenhour.com Colin Mollenhour

    That works nicely, group_concat is a bit more flexible than I thought! I just pushed an update that uses a custom column renderer and makes whether it displays and filters by SKU or name configurable. It uses the title attribute so that whichever is not displayed can be read with a mouse hover. Cheers!

  • Andrew

    Hi Colin, I am wondering if this will work with version 1.6.2?

  • http://colin.mollenhour.com Colin Mollenhour

    Hi Andrew, I have not yet tested in 1.6, only 1.4.1.1. I will probably be testing it though within the next couple weeks.

  • srikanth thandra

    Hi,

        I am looking to call sales person which is a attribute in customer account which is not visible on front end.  I want to call that value and show in the admin order grid with Company and Country. Can you help me with it please.

    Thanks & Regards,
    Srikanth Thandra

  • http://colin.mollenhour.com Colin Mollenhour

    I do not offer consulting services, but adapting the code from my module or one of the other tutorials out there (Inchoo, Ecomdev, Webkul) to suit your needs should be pretty easy.

  • http://twitter.com/CaCycleworks CaCycleworks

    It works on version 1.7.0. We’re testing on the lamest Amazon EC2 instance with great results.

  • http://colin.mollenhour.com Colin Mollenhour

    FYI, the latest version actually uses two separate queries rather than one. The single-query version generates an SQL query that MySQL is unable to properly optimize, and changing the query into one that is optimized is way too complicated given the collection abstraction so now it is two queries. On a site with >100k orders this makes a difference of a few seconds.

  • Nick

    I’m still getting a problem with records found when filtering the products.

  • http://colin.mollenhour.com Colin Mollenhour

    Please use github’s bug tracker if bugs are found.

  • Andrew

     Colin, Is there any way to install this module without github and modman?  I an not familiar with how to use them but desperately need this functionality.  I have tried to figure out where to upload the files but i am missing something as my changes have made no affect on my sales grid.

  • http://colin.mollenhour.com Colin Mollenhour

    Yes, modman simply creates symlinks as described in the file named “modman”. So you can install without modman by copying or symlinking the files to the appropriate location which you can discern by referencing the modman file.

  • ErikHansen

    Thanks for sharing! We are going to reference this extension for modifying the Shipment grid to do the same thing.

  • Ralphy

    Not working for me on Magento 1.7.2.0. It doesn’t even show the column on the backend.

  • http://colin.mollenhour.com/ Colin M

    It may be conflicting with another module, or you didn’t clear the config cache, or you simply didn’t install it right. :P