0

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)
3
  • Use new object of Zend_Db_Expr in collection query Apr 14, 2018 at 10:27
  • can you elaborate more about it via collection expression? Apr 14, 2018 at 13:21
  • 1
    Check @zexperto - WISAM HAKIM answer. Apr 14, 2018 at 16:25

1 Answer 1

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.

3
  • ok that code is working but if i use it inside my extension due to include bootstrap my admin dash layout disturbs , is it anyway to solve this ? can you please reply me at my email ([email protected]) Apr 16, 2018 at 6:06
  • You cannot use bootstrap in your extension, it is only examples how to make the query, you have to initialize Collection factory in your constructor. Hope this will help you Apr 16, 2018 at 9:30
  • yes thank you so much for time . but i am facing itergrity exception now due to created_at column are in both tables , can you please come in email box , i will show you via team viewer if possible. just mail me some text. i will show you screenshots, i would so thankful to you i am stuck in this task from week in office. Apr 16, 2018 at 10:19

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.