How to run custom query in Magento?

In Magento, running custom queries directly against the database is generally discouraged because it bypasses the Magento ORM (Object-Relational Mapping) and can lead to potential issues such as data inconsistency and security vulnerabilities. However, there are scenarios where executing custom queries might be necessary, albeit with caution.

Here’s a general guideline on how to run custom queries in Magento:

1. Using the Magento Database Adapter: Magento provides a database adapter class \Magento\Framework\DB\Adapter\AdapterInterface which can be used to execute custom SQL queries. Here’s a basic example:
   phpCopy code

   <?php
   namespace Vendor\Module\Model;

   use Magento\Framework\Model\ResourceModel\Db\Context;

   class CustomModel extends \Magento\Framework\Model\AbstractModel
   {
   protected $_resource;

   public function __construct(
   Context $context,
   array $data = []
   ) {
   $this->_resource = $context->getResources();
   parent::__construct($context, $data);
   }

   public function customQuery()
{
$connection = $this->_resource->getConnection();
$query = "SELECT * FROM your_table";
$result = $connection->fetchAll($query);
return $result;
}
}


2. Using Resource Models: Instead of directly executing SQL queries, it’s recommended to use Magento’s resource models whenever possible. Resource models provide a higher level of abstraction and ensure that queries are executed safely within the context of Magento’s database schema. Here’s a simplified example:
   phpCopy code

   <?php
   namespace Vendor\Module\Model\ResourceModel;

   use Magento\Framework\Model\ResourceModel\Db\AbstractDb;

   class YourModelResource extends AbstractDb
   {
   public function _construct()
   {
   $this->_init('your_table', 'entity_id'); // Initialize resource model
   }

   public function yourCustomMethod()
{
$connection = $this->getConnection();
$select = $connection->select()->from($this->getMainTable());
$result = $connection->fetchAll($select);
return $result;
}
}


3. Sanitizing Inputs: If you must run custom queries, always sanitize inputs to prevent SQL injection attacks. Magento provides methods like $connection->quoteInto() to properly escape values before using them in queries.
4. Avoid Direct SQL Execution: Whenever possible, try to utilize Magento’s built-in models, collections, and methods for CRUD operations rather than resorting to direct SQL queries. This ensures compatibility with Magento’s architecture and future upgrades.

Remember, before running any custom queries, carefully consider whether it’s the best approach for your specific use case and whether there are alternative, safer methods available within Magento’s framework.