Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

[SOLVED] SKU Attribute
 
symmetrics_bl
Member
 
Avatar
Total Posts:  58
Joined:  2008-01-07
Hannover
 

Hi folks,

I have to add a new column to the order list. It’s called sku and should display the sku-value contained in the order itself. It has to be searchable, too.

This is the col:

$this->addColumn('sku', array(
        
'header'=> 'sku',
        
'index' => 'sku',
));

And here the code for the collection of the grid:

$collection Mage::getResourceModel('sales/order_collection')
  ->
addAttributeToSelect('*')
  ->
joinAttribute('billing_firstname''order_address/firstname''billing_address_id'null'left')
  ->
joinAttribute('billing_lastname''order_address/lastname''billing_address_id'null'left')
  ->
joinAttribute('shipping_firstname''order_address/firstname''shipping_address_id'null'left')
  ->
joinAttribute('shipping_lastname''order_address/lastname''shipping_address_id'null'left')

  
// my code
  
->joinAttribute('sku''order_item/sku''???'null'left')
  
// my code

  
->addExpressionAttributeToSelect('billing_name',
      
'CONCAT({{billing_firstname}}, " ", {{billing_lastname}})',
      array(
'billing_firstname''billing_lastname'))
  ->
addExpressionAttributeToSelect('shipping_name',
      
'CONCAT({{shipping_firstname}}, " ", {{shipping_lastname}})',
      array(
'shipping_firstname''shipping_lastname'));

As you may have noticed, I don’t know which id-field I have to pull.

What has to be done to get the sku properly displayed while also being searchable?

Thanks!

 
Magento Community Magento Community
Magento Community
Magento Community
 
symmetrics_bl
Member
 
Avatar
Total Posts:  58
Joined:  2008-01-07
Hannover
 

does anyone have an idea?

 
Magento Community Magento Community
Magento Community
Magento Community
 
LeeSaferite
Guru
 
Avatar
Total Posts:  322
Joined:  2007-08-31
Lake City, FL
 

Um, isn’t SKU attached to the order item?  If so, which SKU should you show in the grid view?  That grid is showing you a list of orders, not a list of order items.

 
Magento Community Magento Community
Magento Community
Magento Community
 
symmetrics_bl
Member
 
Avatar
Total Posts:  58
Joined:  2008-01-07
Hannover
 

Sorry, but the problem is not that there are probably many skus, but rather to search thru all of them. I want to get all orders whose items contain a specific sku. For displaying the skus I’ve already written a working renderer. So I only have to get the search functionality working.

Image Attachments
Bild 1.png
 
Magento Community Magento Community
Magento Community
Magento Community
 
LeeSaferite
Guru
 
Avatar
Total Posts:  322
Joined:  2007-08-31
Lake City, FL
 

Ok, while I think it is a it odd, try this

->joinTable(
    
'sales/order_item',
    
'order_id=entity_id',
    array(
'sku' => 'sku')
)

Not positive it will work, but looks like it should.

Order Items present a unique problem.  Varien decided to turn them into flat tables instead of EAV tables.  So you need to handle them a bit differently.

 
Magento Community Magento Community
Magento Community
Magento Community
 
symmetrics_bl
Member
 
Avatar
Total Posts:  58
Joined:  2008-01-07
Hannover
 

I am very sorry that I did’t answer immediately - honestly i forgot to do
that. Your solution to the problem helped me a lot. Thank you very much!

 
Magento Community Magento Community
Magento Community
Magento Community
 
symmetrics_bl
Member
 
Avatar
Total Posts:  58
Joined:  2008-01-07
Hannover
 

the solution:

 
Magento Community Magento Community
Magento Community
Magento Community
 
symmetrics_bl
Member
 
Avatar
Total Posts:  58
Joined:  2008-01-07
Hannover
 

the grid: app/code/local/Symmetrics/Adminhtml/Block/Sales/Order/Grid.php

<?php
class Symmetrics_Adminhtml_Block_Sales_Order_Grid extends Mage_Adminhtml_Block_Sales_Order_Grid
{
    
protected function _prepareCollection()
    
{
        
// rewrite Mage_Adminhtml_Block_Sales_Order_Grid::_prepareCollection()
        
$collection Mage::getResourceModel('sales/order_collection')
            ->
addAttributeToSelect('*')
            ->
joinAttribute('billing_firstname''order_address/firstname''billing_address_id'null'left')
            ->
joinAttribute('billing_lastname''order_address/lastname''billing_address_id'null'left')
            ->
joinAttribute('shipping_firstname''order_address/firstname''shipping_address_id'null'left')
            ->
joinAttribute('shipping_lastname''order_address/lastname''shipping_address_id'null'left')

            
// begin: my code
            
->joinField('sku''sales/order_item''sku''order_id=entity_id')
            
// end: my code

            
->addExpressionAttributeToSelect('billing_name',
                
'CONCAT({{billing_firstname}}, " ", {{billing_lastname}})',
                array(
'billing_firstname''billing_lastname'))
            ->
addExpressionAttributeToSelect('shipping_name',
                
'CONCAT({{shipping_firstname}}, " ", {{shipping_lastname}})',
                array(
'shipping_firstname''shipping_lastname'))

            
// begin: my code
            
->groupByAttribute('entity_id');
            
// end: my code

        
$this->setCollection($collection);

        
// copy of Mage_Adminhtml_Block_Widget_Grid::_prepareCollection()
        
if ($this->getCollection()) {

            $this
->_preparePage();

            
$columnId $this->getParam($this->getVarNameSort(), $this->_defaultSort);
            
$dir      $this->getParam($this->getVarNameDir(), $this->_defaultDir);
            
$filter   $this->getParam($this->getVarNameFilter(), null);

            if (
is_null($filter)) {
                $filter 
$this->_defaultFilter;
            
}

            
if (is_string($filter)) {
                $data 
= array();
                
$filter base64_decode($filter);
                
parse_str(urldecode($filter), $data);
                
$this->_setFilterValues($data);
            
else if ($filter && is_array($filter)) {
                $this
->_setFilterValues($filter);
            
else if(!== sizeof($this->_defaultFilter)) {
                $this
->_setFilterValues($this->_defaultFilter);
            
}

            
if (isset($this->_columns[$columnId]) && $this->_columns[$columnId]->getIndex()) {
                $dir 
= (strtolower($dir)=='desc') ? 'desc' 'asc';
                
$this->_columns[$columnId]->setDir($dir);
                
$column $this->_columns[$columnId]->getFilterIndex() ?
                    
$this->_columns[$columnId]->getFilterIndex() : $this->_columns[$columnId]->getIndex();
                
$this->getCollection()->setOrder($column $dir);
            
}

            $this
->getCollection()->load();
            
$this->_afterLoadCollection();
        
}

        
return $this;

        
// return parent::_prepareCollection();
    
}

    
protected function _prepareColumns()
    
{
        $this
->addColumn('real_order_id', array(
                
'header'=> Mage::helper('sales')->__('Order #'),
                
'width' => '80px',
                
'type'  => 'text',
                
'index' => 'increment_id',
            ));

        
// begin: sku column
        
$this->addColumn('sku', array(
                
'header'=> Mage::helper('sales')->__('SKU'),
                
'index' => 'sku',
                
// begin: render the sku content
                
'renderer' => new Symmetrics_Adminhtml_Block_Sales_Order_RendererSku(),
                
// end: render the sku content
                
'type' => 'text',
            ));
        
// end: sku column

        // colums...
     
}
}
 
Magento Community Magento Community
Magento Community
Magento Community
 
symmetrics_bl
Member
 
Avatar
Total Posts:  58
Joined:  2008-01-07
Hannover
 

the renderer: app/code/local/Symmetrics/Adminhtml/Block/Sales/Order/RendererSku.php

<?php
class Symmetrics_Adminhtml_Block_Sales_Order_RendererSku extends Mage_Adminhtml_Block_Widget_Grid_Column_Renderer_Abstract
{
    
public function render(Varien_Object $row)
    
{
        $items_string 
null ;
        foreach ( 
$row -> getAllItems () as $items {
            $item 
$items -> toArray () ;
            
$items_string .= $item [ 'sku' '<br />' ;
        
}

        
return $items_string ;
    
}
}
 
Magento Community Magento Community
Magento Community
Magento Community
 
LeeSaferite
Guru
 
Avatar
Total Posts:  322
Joined:  2007-08-31
Lake City, FL
 

Ok, I thought what you wanted to do seemed a little difficult (impossible?) with Magento.  I use Doctrine a lot and they support what you want to do, but I have yet to find a way to do that in Magento. 

The code I gave you to link in the sku is not doing what you think it is.

It is simply returning multiple lines per order (1 line per item of the order) and the you are grouping on the entity_id.

It works, but is extra overhead for the DB.  Ultimatly, you get the exact same rowset back from the DB with the eception that a new column named sku was added.  But it is just the FIRST sku, not all of them.

Your magic is happening in the renderer.  Every time an order row is drawn in the grid, you make a call to the DB to get the items for the order.

So, your above code would work even if youe did this

<?php
class Symmetrics_Adminhtml_Block_Sales_Order_Grid extends Mage_Adminhtml_Block_Sales_Order_Grid
{
    
protected function _prepareCollection()
    
{
        
// rewrite Mage_Adminhtml_Block_Sales_Order_Grid::_prepareCollection()
        
$collection Mage::getResourceModel('sales/order_collection')
            ->
addAttributeToSelect('*')
            ->
joinAttribute('billing_firstname''order_address/firstname''billing_address_id'null'left')
            ->
joinAttribute('billing_lastname''order_address/lastname''billing_address_id'null'left')
            ->
joinAttribute('shipping_firstname''order_address/firstname''shipping_address_id'null'left')
            ->
joinAttribute('shipping_lastname''order_address/lastname''shipping_address_id'null'left')

            
// begin: my code
            
->addExpressionAttributeToSelect('sku''"fake_sku"', array())
            
// end: my code

            
->addExpressionAttributeToSelect('billing_name',
                
'CONCAT({{billing_firstname}}, " ", {{billing_lastname}})',
                array(
'billing_firstname''billing_lastname'))
            ->
addExpressionAttributeToSelect('shipping_name',
                
'CONCAT({{shipping_firstname}}, " ", {{shipping_lastname}})',
                array(
'shipping_firstname''shipping_lastname'))

        
$this->setCollection($collection);

        
// copy of Mage_Adminhtml_Block_Widget_Grid::_prepareCollection()
        
if ($this->getCollection()) {

            $this
->_preparePage();

            
$columnId $this->getParam($this->getVarNameSort(), $this->_defaultSort);
            
$dir      $this->getParam($this->getVarNameDir(), $this->_defaultDir);
            
$filter   $this->getParam($this->getVarNameFilter(), null);

            if (
is_null($filter)) {
                $filter 
$this->_defaultFilter;
            
}

            
if (is_string($filter)) {
                $data 
= array();
                
$filter base64_decode($filter);
                
parse_str(urldecode($filter), $data);
                
$this->_setFilterValues($data);
            
else if ($filter && is_array($filter)) {
                $this
->_setFilterValues($filter);
            
else if(!== sizeof($this->_defaultFilter)) {
                $this
->_setFilterValues($this->_defaultFilter);
            
}

            
if (isset($this->_columns[$columnId]) && $this->_columns[$columnId]->getIndex()) {
                $dir 
= (strtolower($dir)=='desc') ? 'desc' 'asc';
                
$this->_columns[$columnId]->setDir($dir);
                
$column $this->_columns[$columnId]->getFilterIndex() ?
                    
$this->_columns[$columnId]->getFilterIndex() : $this->_columns[$columnId]->getIndex();
                
$this->getCollection()->setOrder($column $dir);
            
}

            $this
->getCollection()->load();
            
$this->_afterLoadCollection();
        
}

        
return $this;

        
// return parent::_prepareCollection();
    
}

    
protected function _prepareColumns()
    
{
        $this
->addColumn('real_order_id', array(
                
'header'=> Mage::helper('sales')->__('Order #'),
                
'width' => '80px',
                
'type'  => 'text',
                
'index' => 'increment_id',
            ));

        
// begin: sku column
        
$this->addColumn('sku', array(
                
'header'=> Mage::helper('sales')->__('SKU'),
                
'index' => 'sku',
                
// begin: render the sku content
                
'renderer' => new Symmetrics_Adminhtml_Block_Sales_Order_RendererSku(),
                
// end: render the sku content
                
'type' => 'text',
            ));
        
// end: sku column

        // colums...
     
}
}

EDIT: Removed useless group by statement

 
Magento Community Magento Community
Magento Community
Magento Community
 
LeeSaferite
Guru
 
Avatar
Total Posts:  322
Joined:  2007-08-31
Lake City, FL
 

@wyclefbu

I would suggest a more efficient approach (in terms of DB accesses).

You could create your order collection object, then create a new item collection object.  Join the item collection object back to the order model and just use the order info when applying the filter.  Now you have 2 collections.  The first one is a filtered list of orders and the second is a filtered list of order items.  You could then walk the order items and add/collect them under a new property on the order.  Then your renderer could access the new property on the order as an array of items.  No need to hit the DB separately for every order.  Instead, you make 2 simple calls and post process/merge the data.

If a Magento developer is listening, look into how Doctrine does this.  They allow you to pull back multiple levels of objects at once.  I could retrieve all of my orders and their items in a single SQL call.  It’s more raw data over the wire, but it’s a SINGLE call.

 
Magento Community Magento Community
Magento Community
Magento Community
 
lingerie1
Jr. Member
 
Total Posts:  5
Joined:  2008-10-04
 

I am trying to do this also but with no luck
I can not find
app/code/local/Symmetrics/Adminhtml/Block/Sales/Order/Grid.php

but i can find

app/code/core/mage/Adminhtml/Block/Sales/Order/Grid.php
I tried to apply this to the above location in my directory but i get

parse error: syntax error, unexpected ‘{’, expecting ‘)’ in /var/www/vhosts/mystore/httpdocs/app/code/core/Mage/Adminhtml/Block/Sales/Order/Grid.php on line 86

What am i doing wrong.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Fibo
Sr. Member
 
Avatar
Total Posts:  107
Joined:  2008-06-25
Marseille, France
 

’Symmetrics’ is the name of the ‘namespace’, usually the company name to avoid interferences.

 
Magento Community Magento Community
Magento Community
Magento Community
 
rhaps
Jr. Member
 
Total Posts:  29
Joined:  2007-11-24
 

Hi Lee,
I tried your code, but I could not get it to overwrite sales_order grid.

What did you write in your config.xml ?

I tried

<?xml version="1.0" encoding="UTF-8"?>
<config>
    <
modules>
        <
Symmetrics_Adminhtml>
            <
active>true</active>
            <
codePool>local</codePool>
        </
Symmetrics_Adminhtml>
    </
modules>
   <global>
        <
blocks>
        <
adminhtml>  
                  <
rewrite>  
                <
sales_order_create_search_grid>  
                      
Symmetrics_Adminhtml_Block_Sales_Order_Grid  
                
</sales_order_create_search_grid>  
                  </
rewrite>  
        </
adminhtml>
        </
blocks>
    </global>
</
config>

Empty caches, but Admin->Sales->Orders didn’t changed

 
Magento Community Magento Community
Magento Community
Magento Community
 
mcm999
Member
 
Total Posts:  42
Joined:  2009-05-06
 

Hi, I have got this semi-working, but the page number dont seem to be working, so I can go to page 2 etc. Also the order count just shows 1, even if there are hundreds.

Any ideas?

Thanks

 
Magento Community Magento Community
Magento Community
Magento Community
 
sherrie
Moderator
 
Avatar
Total Posts:  1655
Joined:  2007-12-14
Waterloo, ON
 

Any idea why this is throwing out the following error in 1.4.2?

Fatal errorCall to undefined method Mage_Sales_Model_Mysql4_Order_Collection::addExpressionAttributeToSelect() in /*******/app/code/core/Mage/Adminhtml/Block/Sales/Order/Grid.php on line 68
 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top