1

so I have a renderer for orders and here is the code: the sku renderer is working fine I have problem with search and filtering the skus

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

I read in the article which I should use callback but I don't know how to write the function to select the sku because $this here doesn't have the sku also I used this but it is not correct and gives me error of unknown column sku:

protected function _addressFilter($collection, $column)
    {
        if (!$value = $column->getFilter()->getValue()) {
            return $this;
        }

        $this->getCollection()->getSelect()->where(
            "sales_flat_order_item.sku like ?"
        , "%$value%");


        return $this;
    }

Error:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sales_flat_order_item.sku' in 'where clause'

updated prepare collection:

 protected function _prepareCollection()
    {
        $storeCode='bceu';
        $stores = array_keys(Mage::app()->getStores());
        foreach($stores as $id){
          $store = Mage::app()->getStore($id);
          if($store->getCode()==$storeCode) {
            $storeId=$id;
          }
         }
        //$value=3200000001;        
        $from="2014-05-21"; 
        $collection = Mage::getResourceModel('sales/order_collection')

        ->addFieldToFilter('store_id',$storeId)
        //->addAttributeToFilter('increment_id', array('gt' => $value))

        ->addAttributeToFilter('created_at', array("from" => $from, "datetime" => true))
        ->join(array('a' => 'sales/order_address'), 'main_table.entity_id = a.parent_id AND a.address_type != \'billing\'', array(
                'city'       => 'city',
                'country_id' => 'country_id'
            ))
            ->join(array('c' => 'customer/customer_group'), 'main_table.customer_group_id = c.customer_group_id', array(
                'customer_group_code' => 'customer_group_code'
            ))
            ->addExpressionFieldToSelect(
                'fullname',
                'CONCAT({{customer_firstname}}, \' \', {{customer_lastname}})',
                array('customer_firstname' => 'main_table.customer_firstname', 'customer_lastname' => 'main_table.customer_lastname'))
            ->addExpressionFieldToSelect(
                'products',
                '(SELECT GROUP_CONCAT(\' \', x.name)
                    FROM sales_flat_order_item x
                    WHERE {{entity_id}} = x.order_id
                        AND x.product_type != \'configurable\')',
                array('entity_id' => 'main_table.entity_id')
            )

        ;

         // CUSTOM

        $collection->getSelect()->joinLeft(array('sfop'=>'sales_flat_order_payment'), 
    'main_table.entity_id = sfop.parent_id',array('po_number' => 'sfop.po_number'
    ));
  • sure,I updated the question – Nickool Sep 10 '14 at 17:58
  • I don't know how to make input for $column and $collection in _addressFilter – Nickool Sep 10 '14 at 18:00
  • Sku is not Stored in the order collection, you need to join 'sales_flat_order_item.order_id' – Renon Stewart Sep 10 '14 at 18:04
  • I cannot write it should I write join and then where? I did it but it seems i did it wrong – Nickool Sep 10 '14 at 19:36
  • Let me see the code for Lenmar_Adminhtml_Block_Sales_Order_RendererSkuFk1()? – Renon Stewart Sep 10 '14 at 19:44
1

Since sales_flat_order_item is not included in the original sql you will get Unknown column 'sales_flat_order_item.sku' error because that table is unknown

Try

$collection = Mage::getResourceModel('sales/order_collection')
....
->join(array('sfoi' => 'sales_flat_order_item'), 'main_table.entity_id = sfoi.order_id, array('sku'))
...
->group('main_table.entity_id')

If the example above work then undo and try

protected function _addressFilter($collection, $column)
{
    if (!$value = $column->getFilter()->getValue()) {
        return $this;
    }

    $this->getCollection()->getSelect()
    ->join(array('sfoi' => 'sales_flat_order_item'), 'main_table.entity_id = sfoi.order_id, array('sku'))
    ->where(
        "sales_flat_order_item.sku like ?"
    , "%$value%");


    return $this;
}

Final answer:

protected function _addressFilter($collection, $column)
{
    if (!$value = $column->getFilter()->getValue()) {
        return $this;
    }
    $this->getCollection()->getSelect()
    ->join(array('sfoi' => 'sales_flat_order_item'), 'main_table.entity_id = sfoi.order_id', array('sku'))
    ->where(
        "sfoi.sku like ?"
    , "%$value%")->group('main_table.entity_id');
 return $this;
}
  • Error: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'store_id' in where clause is ambiguous – Nickool Sep 10 '14 at 20:07
  • Change this line ->addFieldToFilter('store_id',$storeId) to ->addFieldToFilter('main_table.store_id',$storeId) – Renon Stewart Sep 10 '14 at 20:10
  • I had the same error for created at and I changed it and after that now I have this Time: 2014-09-10 20:15:18 GMT Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sales_flat_order_item.sku' in 'where clause' – Nickool Sep 10 '14 at 20:15
  • although I can see in my table that I have this field it is so weird – Nickool Sep 10 '14 at 20:16
  • 1
    I learn 90% of what I currently know about magento by answering question on stackoverflow and on here. So just start answering questions. – Renon Stewart Sep 10 '14 at 20:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.