Magento 2 : How to Write Conditional Query in Collection Method

Question

How to write this query in magento2 collection method ?

SELECT o.increment_id, o.base_grand_total, h.status,o.created_at , h.created_at
FROM sales_order AS o
RIGHT JOIN sales_order_status_history AS h ON h.parent_id = o.entity_id

WHERE h.status = 'processing_closed'
AND CONVERT(o.created_at, DATE) <= CONVERT('2018-04-10', DATE)
AND CONVERT(h.created_at, DATE) = CONVERT('2018-04-10', DATE)

Answer 1

You can get your goal by this code

<?php
use Magento\Framework\App\Bootstrap;

include 'app/bootstrap.php';

$bootstrap = Bootstrap::create(BP, $_SERVER);
$objectManager = $bootstrap->getObjectManager();

$state = $objectManager->get('Magento\Framework\App\State');
$state->setAreaCode('frontend');


$factoryCollection = $objectManager->get('\Magento\Sales\Model\ResourceModel\Order\CollectionFactory');
$collection = $factoryCollection->create();
$date = date('Y-m-d H:i:s', strtotime("2018-04-10"));


$collection->addAttributeToSelect("increment_id");
$collection->addAttributeToSelect("base_grand_total");
$collection->addAttributeToSelect("created_at");
$collection->getSelect()->joinRight( array('order_history'=> sales_order_status_history), 'order_history.parent_id = main_table.entity_id', array('order_history.status','order_history.created_at'));

$collection->addFieldToFilter("order_history.status","processing_closed");
$collection->addFieldToFilter('main_table.created_at',array('lteq' => $date));
$collection->addFieldToFilter('order_history.created_at',array('ew' => $date));
echo $collection->getSelect();

If you want to use your custom table alias you need to use this

<?php
use Magento\Framework\App\Bootstrap;

include 'app/bootstrap.php';

$bootstrap = Bootstrap::create(BP, $_SERVER);
$objectManager = $bootstrap->getObjectManager();

$state = $objectManager->get('Magento\Framework\App\State');
$state->setAreaCode('frontend');


$factoryCollection = $objectManager->get('\Magento\Sales\Model\ResourceModel\Order\CollectionFactory');
$collection = $factoryCollection->create();
$date = date('Y-m-d H:i:s', strtotime("2018-04-10"));

$collection->getSelect()
    ->reset(\Zend_Db_Select::FROM)
    ->reset(\Zend_Db_Select::COLUMNS)
    ->from(array('o' => $collection->getMainTable()), array('increment_id','base_grand_total','created_at'));
$collection->getSelect()->joinRight( array('h'=> sales_order_status_history), 'h.parent_id = o.entity_id', array('h.status','h.created_at'));
$collection->addFieldToFilter("h.status","processing_closed");
$collection->addFieldToFilter('o.created_at',array('lteq' => $date));
$collection->addFieldToFilter('h.created_at',array('ew' => $date));


echo $collection->getSelect();

Please note that this code can be run in the root of Magento directory.