How to use fetchPairs method in Magento 2

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!