request sql bad with invalid quote

Question

We are trying to migrate our code from magento 1.7 to 1.9 and I have an issue when i build a request with magento 1.9. I wrote this for a column on the order grid :

 $this->addColumn('id_po', array(
              'header'      => $helper->__('Commande / OP'),
              'index'       => 'id_po',
              'filter_index' => "CONCAT(main_table.increment_id , CONCAT('|AP', LPAD(main_table.entity_id , 7, '0')), '0|', coalesce(main_table.order_id_lengow , ''))",
              'renderer' => new Amixys_Pilot_Block_Adminhtml_Renderer_IdPo(),
          ));

But it returns an error

:"SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.order_id_lengow , '')) LIKE '%OFR00093036%')' at line 2, query was: SELECT COUNT(*) FROM sales_flat_order AS main_table INNER JOIN sales_flat_order_address AS a ON main_table.entity_id = a.parent_id AND a.address_type != 'shipping' WHERE (CONCAT(main_table.increment_id , CONCAT('|AP', LPAD(main_table.entity_id , 7, '0')), '0|', coalesce(main_table.order_id_lengow , '')) LIKE '%OFR00093036%')"

You can't see it on this page , but it added some unicode caracters U+0060 in wrong places as you can see on my screenshot

Do you have an idea to fix it?

Thanks a lot Thomas

Answer 1

I found the answer. So in magento 1.9.3.8 you can t use anymore 'filter_index' like this . You have to use

  $this->addColumn('id_po',
            array(
                'header'=> Mage::helper('catalog')->__('Commande / OP'),
                'index' => array('main_table.increment_id','main_table.entity_id', 'main_table.order_id_lengow'),
                'separator'=>'|',
                'renderer' => new Amixys_Pilot_Block_Adminhtml_Renderer_IdPo(),
                'filter_condition_callback' => array($this, "_idpoFilter")
            ));

And use a function _idpoFilter like that :

protected function _idpoFilter($collection, $column) {
        if (!$value = $column->getFilter()->getValue()) {
            return $this;
        }
        $this->getCollection()->getSelect()->where('CONCAT(main_table.increment_id , CONCAT('|AP', LPAD(main_table.entity_id , 7, '0')), '0|', coalesce(main_table.order_id_lengow , '') like ?', , "%$value%");

      return $this;
    }

I hope it will help someone :)