Possible raw SQL statement detected

Question

Magento 2. Custom module.

I have to run some raw SQLs. It works and doesn't show any error. But when I'm trying to upload module to Magento 2 Market Place (developer.magento.com/extension) it shows in technical report a dozen of such errors:

Possible raw SQL statement 'UPDATE `' detected. Possible raw SQL statement 'SELECT MAX(entity_id) AS MaxOrderId FROM ' detected. Possible raw SQL statement 'SELECT COUNT(entity_id) AS CountNewOrder FROM ' detected.

What is the correct way to run this statement?

$sql = 'SELECT COUNT(entity_id) AS CountNewOrder FROM ' . $this->getDbPrefix() . 'sales_order WHERE entity_id > ' . $order_id;

$result = \Magento\Framework\App\ObjectManager::getInstance()->getConnection()->fetchAll($sql);*

Answer 1

The idea is to never run raw queries in your modules. Magento EcgM2 coding standards does not like that.
You can use the ORM to do that.

If you want the number of orders with Id over a certain value you can do this in your class.

class MyClassNameHere extends WhateverYouNeed
{
    protected $collectionFactory;
    public function __construct(
        //add a dependency to your class, don't use object manager directly  
        \Magento\Sales\Model\ResourceModel\Order\Collectionfactory $collectionFactory, 
         //other dependencies here if needed
    )
    {
       $this->collectionFactory = $collectionFactory;
       //rest of constructor here
    }
    public function getOrdersWithIdEqualOrOver($id) 
    {
        $collection = $this->collectionFactory->create();
        $collection->addFieldToFilter('entity_id', ['gteq' => $id]);
        return $collection->getSize();
    }
}

then just call the method getOrdersWithIdEqualOrOver where you need.

Side note: I would think twice before using a module that has raw queries in it.

Answer 2

Here is solution:

$om = \Magento\Framework\App\ObjectManager::getInstance();
$adapter = $om->get('Magento\Framework\App\ResourceConnection')->getConnection()->select()->getAdapter();
$sql = 'SELECT COUNT(entity_id) AS CountNewOrder FROM sales_order WHERE entity_id > :order_id';
$binding = ['order_id' => 1];

$data = $adapter->query($sql, $binding)->fetch();

But still no success with Magento 2 Code Validator...