1

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()
{
    parent::_initSelect();
    $this->getSelect()->joinLeft(
    ['sales_order_item' => $this->getTable('sales_order_item')],
    'sales_order_item.product_options REGEXP code',
    ['sales_order_item.product_options']
    )->where("sales_order_item.product_type like 'giftcard' AND main_table.code NOT like ''");

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

    return $this;
}

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

1 Answer 1

1

You can try with the function addFieldToFilter()

protected function _initSelect()
{
    parent::_initSelect();

    $this->getSelect()->joinLeft(
        ['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' => '']);

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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