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

enter image description here

Do you have an idea to fix it?

Thanks a lot Thomas

• Jaimin Sutariya: Have you tried removing blank space after main_table.order_id_lengow? In MySQL, it allows the space not sure about MariaDB
• thomas poidevin: I'm using mysql and not mariadb. With my other test server with magento 1.7 when i have an error i have also mariadb so I don't think it's the problem isn t it? And i tried by removing the space but i have the same issue :(

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 :)