0

I have below sql query, how do I turn this to Magento get data method to retrieve data? Please help

SELECT sales_order.increment_id, sales_order_payment.additional_information FROM sales_order LEFT JOIN 
            sales_order_payment ON sales_order.entity_id = sales_order_payment.parent_id 
            WHERE sales_order_payment.method = 'opayment' AND sales_order.updated_at >= NOW() - INTERVAL 30 MINUTE
1

You can use the below query:

    /**
     * @var $orderCollection Collection
     */
    $orderCollection = $this->collectionFactory->create();
    $orderCollection->getSelect()->joinLeft(
        ['sales_order_payment' => $orderCollection->getTable('sales_order_payment')],
        "(main_table.entity_id = {$orderCollection->getTable('sales_order_payment')}.parent_id" .
        " )",
        [
            $orderCollection->getTable('sales_order_payment') . '.additional_information'
        ]
    )->where($orderCollection->getTable('sales_order_payment').'.method = ?','payflowpro');
    $orderCollection->getSelect()->where('main_table.updated_at >= ?',$orderCollection->getConnection()->getDateSubSql(
        new \Zend_Db_Expr('NOW()'),
        '30',
        \Magento\Framework\DB\Adapter\AdapterInterface::INTERVAL_MINUTE
    ));
2
  • thanks! can you help how to implement OrderCollection to the the code, I am confuse how to set the element up at the page
    – Paul Fan
    Dec 3 '20 at 18:57
  • THANKS! YOU ARE MY LIFE SAVER
    – Paul Fan
    Dec 4 '20 at 22:50
0

You can use Magento\Framework\App\ResourceConnection to run custom SQL queries

$objectManager = \Magento\Framework\App\ObjectManager::getInstance();
$resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
$query = "SELECT sales_order.increment_id, sales_order_payment.additional_information FROM sales_order LEFT JOIN 
            sales_order_payment ON sales_order.entity_id = sales_order_payment.parent_id 
            WHERE sales_order_payment.method = 'opayment' AND sales_order.updated_at >= NOW() - INTERVAL 30 MINUTE";
$Data =  $resource->getConnection()->fetchAll($query);

Via Dependency Injection

public function __construct(
        \Magento\Framework\App\ResourceConnection $resource
    ) {
        $this->_resource = $resource;
    }

public function RunQuery(){
    $query = "SELECT sales_order.increment_id, sales_order_payment.additional_information FROM sales_order LEFT JOIN 
                sales_order_payment ON sales_order.entity_id = sales_order_payment.parent_id 
                WHERE sales_order_payment.method = 'opayment' AND sales_order.updated_at >= NOW() - INTERVAL 30 MINUTE";
    $Data =  $this->_resource->getConnection()->fetchAll($query);
}
4
  • If I don't use ObjectManager directly, how do I using Dependency injection?
    – Paul Fan
    Dec 2 '20 at 18:03
  • I have updated it in my answer
    – Ghulam.M
    Dec 3 '20 at 6:11
  • Thanks! Can you tell me why this way is better than using ObjectManager Directly?
    – Paul Fan
    Dec 3 '20 at 6:21
  • 1
    read this thread
    – Ghulam.M
    Dec 3 '20 at 8:01

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.