In this article we will check How to write a fetchPairs method in Magento 2. Create a MySQL fetchPairs() query in the Magento standard way to retrieve all SQL result rows as an array of key-value pairs.
You can use the fetchPairs()
function to write a direct SQL query without the necessity of handling Model operations, as illustrated in the following code snippet. The fetchPairs() function always returns an array with key-value pairs as the output.
Base Definition of the function:
The fetchPairs
function retrieves SQL results as an array of key-value pairs, where the first column serves as the key and the second column as the value. For more details, please refer to the class below.
Magento\Framework\DB\Adapter\AdapterInterface::fetchPairs($sql, $bind = []);
public function fetchPairs($sql, $bind = []);
Now, let’s consider a query from the customer_entity table to perform the fetchPairs() operation.
<?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->getAllRecordsByFetchPairs($storeId);
print_r($customerData);
$this->logger->info('customer_data',$customerData);
}
/**
* @param int $id
*
* @return array
*/
public function getAllRecordsByFetchPairs($id){
$connection = $this->resourceConnection->getConnection();
$tableName = $this->resourceConnection->getTableName('customer_entity');
$bind = ['store_id' => $id];
$query = $connection
->select()
->from(
['main_table' => $tableName],
['entity_id','email']
)
->where('store_id = :store_id');
return $connection->fetchPairs($query, $bind);
}
}
Output:
The method yields an output with the first column as the key (entity_id) and the second column as the value (email).
Array
(
[1] => roni_cost@example.com
[2] => biswal@jyotiranjan.in
............................
............................
)
You can also refer the some example from magento2 core files like Magento\Catalog\Cron\FrontendActionsFlush::getUniqueNamespaces()
You may also like this : How to Write SQL Query in Magento 2 With Standard Way
I hope this article on “How to write a fetchPairs method in Magento 2” clearly explains you. 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!