In this tutorial “How to use fetchOne() method with Sql query in Magento 2“, I will guide you on how to utilize the fetchOne() method with SQL Queries in Magento 2. Often, there is a need to perform operations on data using SQL Queries, and the fetchOne() method is particularly useful for fetching the first column of the first row in the output of an SQL query.
You may also like this : How to Write SQL Query in Magento 2 With Standard Way
Main Definition:
fetchOne function fetches the first columns of the first row of the SQL result
Magento\Framework\DB\Adapter\AdapterInterface::fetchOne($sql, $bind = []);public function fetchOne($sql, $bind = []);Let me illustrate with an example:
<?php
/**
* @Module : Jrb Demomodule
* @Package : Jrb_Demomodule
* @Description : Testing custom script output
* @Developer : Jyotiranjan Biswal<biswal@jyotiranjan.in>
* @Copyright : https://www.jyotiranjan.in/
*/
namespace Jrb\DemoModule\Model;
use Magento\Framework\Model\AbstractModel;
use Magento\Framework\App\ResourceConnection;
class CustomScript extends AbstractModel
{
protected $logger;
/**
* Resource
*
* @var ResourceConnection
*/
protected $resourceConnection;
/**
* Constructor
*
* @param \Psr\Log\LoggerInterface $logger
*/
public function __construct(
\Psr\Log\LoggerInterface $logger,
ResourceConnection $resourceConnection,
)
{
$this->logger = $logger;
$this->resourceConnection = $resourceConnection;
}
/**
* Execute the custom script
*
* @return void
*/
public function execute()
{
// get the list of record having store id 1
$storeId = 1;
$customerData = $this->getAllRecordsByFetchOne($storeId);
print_r($customerData);
$this->logger->info('customer_data'.$customerData);
}
/**
* @param int $id
*
* @return array
*/
public function getAllRecordsByFetchOne($id){
$connection = $this->resourceConnection->getConnection();
$tableName = $this->resourceConnection->getTableName('customer_entity');
$bind = ['entity_id' => $id];
$query = $connection
->select()
->from(
['main_table' => $tableName],
['email','firstname']
)
->where('entity_id = :entity_id');
return $connection->fetchOne($query, $bind);
}
}It’s important to note that the $entity_id value is included in the bind array rather than being directly passed to the specified where conditions. The fetchOne() method fetches the initial value of the first column in the record, enabling you to retrieve the email associated with that record.
You can also refer the some example from magento2 core files like Magento\Customer\Model\ResourceModel\Customer::checkCustomerId($customerId)
I hope this article on “How to use fetchOne() method with Sql query in Magento 2” clearly explains how to utilize the fetchOne() method in Magento 2. Feel free to comment if you have further insights or if I missed anything. I’ll respond with the appropriate solution.
Keep liking and sharing!
