Possible for Order Item Categories in sales_order_grid

[WinstonN]

Your extension is great! I am trying to add the categories column (similar to catalog_product_grid) to the sales_order_grid I have gotten it right to a degree but, I am missing something. Could you please help me?

I have created my own module, and added the customgrid.xml

<?xml version="1.0"?>
<customgrid>
    <grid_types>

        <sales_order type="customgrid/grid_type_order" module="customgrid">
            <name>Orders</name>
            <sort_order>30000</sort_order>
            <custom_columns>
                <categories model="mycompany_admingridmods/sales_order_items_categories" module="mycompany_admingridmods">
                    <name>Categories</name>
                    <description>Displays for each product all the categories it's associated to</description>
                    <warning>Sorting is based on a csv string of category IDs</warning>
                    <allow_customization>1</allow_customization>
                    <allow_store>1</allow_store>
                </categories>
            </custom_columns>
        </sales_order>

    </grid_types>

</customgrid>

In the model mycompany_admingridmods/sales_order_items_categories I wrote the _getCategoryIdsSelect function like this

protected function _getCategoryIdsSelect($collection, $forFilter=false, $ids=null)
    {
        $helper    = $this->_getCollectionHelper();
        $mainAlias = $this->_getCollectionMainTableAlias($collection);
        list($adapter, $qi) = $this->_getCollectionAdapter($collection, true);
        $cpAlias   = $this->_getUniqueTableAlias($forFilter ? '_filter' : '_select');
        $mainField = ($forFilter ? 'COUNT(*)' : 'GROUP_CONCAT('.$qi($cpAlias.'.category_id').')');

        $collection->getSelect()
            ->joinInner(array('sfoi' => 'sales_flat_order_item'),'main_table.entity_id = sfoi.order_id',array('sfoi.product_id'));
        $collection->getSelect()
            ->joinInner(array('ccp' => 'catalog_category_product'),'ccp.product_id = sfoi.product_id',array('ccp.category_id'));

        $select = $adapter->select()
            ->from(
                array($cpAlias => $collection->getTable('catalog/category_product')),
                array('value' => new Zend_Db_Expr($mainField))
            )
            ->where($qi($cpAlias.'.product_id').' = '.$qi('sfoi'.'.product_id'));

        if (is_array($ids)) {
            $select->where($qi($cpAlias.'.category_id').' IN (?)', $ids);
        }

        return $select;
    }

This does produce some good results, but not quite. Example, I get multiple rows per order (one for each order item) which then forces me to add $collection->getSelect()->group('entity_id'); in the addFieldToGridCollection function

I have hit a wall though, and I thought that you guys are such experts maybe you could guide me? I could make a pull request of my work so that this feature would be available to other magento users using your module. I am willing to donate some USD if you want me to pay for it, welcome to let me know.

Once, again, thank you for your great extension

Hi from Cape Town, South Africa

[mage-eag]

Thanks ! :)

Regarding your code, you should not join any table directly in the collection, but rather use a sub-query, and group when necessary in that sub-query only (as in the original code from the product categories column). This is at least required because grouping would break the grid pager (it would not detect more than one page anymore), but it also ensures that not any order will go missing in the grid (because of the inner join on the category products table, in the case of unassigned products).

[WinstonN]

Hi, just getting back onto this ticket. Where would the best place be to write my subquery? I am trying to find the best place to join but I always end up with a query like this

SELECT `main_table`.*, (SELECT GROUP_CONCAT(`_rsagmmsoic_select`.`category_id`) AS `value`, `sfoi`.`product_id` FROM `catalog_category_product` AS `_rsagmmsoic_select`
 LEFT JOIN `sales_flat_order_item` AS `sfoi` ON `main_table`.`entity_id` = sfoi.order_id WHERE (`_rsagmmsoic_select`.`product_id` = `main_table`.`entity_id`) GROUP BY `_rsagmmsoic_select`.`product_id`) AS `blcg_custom_field_6`, `_blcgmccoas_ca24ce3d82e861c4d328a42e07a4273e`.`email` AS `blcg_custom_field_2`, `_blcgmccoas_ca24ce3d82e861c4d328a42e07a4273e_1`.`firstname` AS `blcg_custom_field_3`, `_blcgmccop_db52ca50388ff4fc8118ce49eabce053`.`method` AS `blcg_custom_field_4`, `_blcgmccoas_ca24ce3d82e861c4d328a42e07a4273e_2`.`telephone` AS `blcg_custom_field_5` FROM `sales_flat_order_grid` AS `main_table`
 LEFT JOIN `sales_flat_order_address` AS `_blcgmccoas_ca24ce3d82e861c4d328a42e07a4273e` ON `_blcgmccoas_ca24ce3d82e861c4d328a42e07a4273e`.`parent_id` = `main_table`.`entity_id` AND `_blcgmccoas_ca24ce3d82e861c4d328a42e07a4273e`.`address_type` = 'shipping'
 LEFT JOIN `sales_flat_order_address` AS `_blcgmccoas_ca24ce3d82e861c4d328a42e07a4273e_1` ON `_blcgmccoas_ca24ce3d82e861c4d328a42e07a4273e_1`.`parent_id` = `main_table`.`entity_id` AND `_blcgmccoas_ca24ce3d82e861c4d328a42e07a4273e_1`.`address_type` = 'shipping'
 LEFT JOIN `sales_flat_order_payment` AS `_blcgmccop_db52ca50388ff4fc8118ce49eabce053` ON `_blcgmccop_db52ca50388ff4fc8118ce49eabce053`.`parent_id` = `main_table`.`entity_id`
 LEFT JOIN `sales_flat_order_address` AS `_blcgmccoas_ca24ce3d82e861c4d328a42e07a4273e_2` ON `_blcgmccoas_ca24ce3d82e861c4d328a42e07a4273e_2`.`parent_id` = `main_table`.`entity_id` AND `_blcgmccoas_ca24ce3d82e861c4d328a42e07a4273e_2`.`address_type` = 'shipping' WHERE (main_table.increment_id LIKE '%15360079%') ORDER BY main_table.created_at DESC LIMIT 20

Notice how my join comes in right at the beginning? Then I get this error.

exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'main_table.entity_id' in 'on clause''

Once again, thank you so much for the help man