I am using following query to load data

SELECT `main_table`.*, `sales_order_item`.`product_options` FROM `magento_giftcardaccount` AS `main_table` LEFT JOIN `sales_order_item` ON sales_order_item.product_options REGEXP code WHERE (sales_order_item.product_type like 'giftcard' AND main_table.code NOT like '')

In magento2

protected function _initSelect()
    ['sales_order_item' => $this->getTable('sales_order_item')],
    'sales_order_item.product_options REGEXP code',
    )->where("sales_order_item.product_type like 'giftcard' AND main_table.code NOT like ''");

    echo $this->getSelect();die();

    return $this;

It is taking too long to load, how can I do it faster.


You can try with the function addFieldToFilter()

protected function _initSelect()

        ['soi' => $this->getTable('sales_order_item')],
        'YOUR CONDITION', //main_table.code = soi.product_type
        ['soi_product_options' => 'soi.product_options']
    $this->addFieldToFilter('soi_product_options', ['like' => '%giftcard%'])
         ->addFieldToFilter('main_table.code', ['nlike' => '']);

  • Not working 'soi_product_options' unknown column – Arjun Aug 9 '19 at 11:55
  • Okey, what is the main table? and what you want to do exactly !!! – aissyass Aug 9 '19 at 12:06
  • I solved the error. I tried by using addFieldToFilter() but no difference, still it takes too much time – Arjun Aug 9 '19 at 12:07
  • Iou have a big data in the tables? – aissyass Aug 9 '19 at 12:10
  • the data inside product_options are serlize – Arjun Aug 9 '19 at 12:11

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.