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

score 1 · Accepted Answer · answered Dec 03 '20 at 09:30

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

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 03 '20 at 18:57

Ghulam.M · Answer 2 · 2020-12-03T06:10:57.853

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

If I don't use ObjectManager directly, how do I using Dependency injection? — Paul Fan, Dec 02 '20 at 18:03
Thanks! Can you tell me why this way is better than using ObjectManager Directly? — Paul Fan, Dec 03 '20 at 06:21

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

2 Answers2