0

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

  • Have you tried removing blank space after main_table.order_id_lengow? In MySQL, it allows the space not sure about MariaDB – Jaimin Sutariya Oct 9 '18 at 11:40
  • 1
    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 :( – thomas poidevin Oct 9 '18 at 13:02
0

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

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.