convert sql query to magento addFieldToFilter combine OR and AND

Question

You can convert me this sql I did not arrive at combined OR and AND at the same time

SELECT * FROM sales_order AS main_table, sales_order_payment AS sop WHERE ((status = 'processing') Or ((status = 'pending') and (sop.method = 'chekmo') )) AND (updated_at >= '2020-06-19 18:24:59')

Please, can someone help or suggest anything?

Answer 1

Unfortunately you cannot nest AND condition in OR with the addFieldToFilter. So you could use Zend_Db_Select::where method to write custom nested queries.

    $collection = $this->_orderCollectionFactory->create()
        ->addFieldToSelect('*');

    /* join with payment table */
    $collection->getSelect()
        ->join(
            ["sop" => "sales_order_payment"],
            'main_table.entity_id = sop.parent_id',
            array('method')
        );


    $collection->getSelect()->where(
        "status = 'processing' OR (status = 'pending' AND sop.method = 'checkmo')"
    );

    $collection->addFieldToFilter('updated_at' , ['gteq' => '2019-06-19 18:24:59']);
    return $collection;

For more information about addFieldToFilter refer https://magento.stackexchange.com/a/139760/77673

Thanks