how do I change this sql query to magento get data method?

Question

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

Answer 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
 ));

• Paul Fan: 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: THANKS! YOU ARE MY LIFE SAVER

Answer 2

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);
}

• Paul Fan: If I don't use ObjectManager directly, how do I using Dependency injection?
• Ghulam.M: I have updated it in my answer
• Paul Fan: Thanks! Can you tell me why this way is better than using ObjectManager Directly?
• Ghulam.M: read this thread