How to write a fetchPairs MySql query in Magento 2?

Write a Mysql fetchPairs() query using Magento standard way for fetching all SQL result rows as an array of key-value pairs.

You can write direct SQL query fetchPairs() without worrying about Model operation using below code snippet.

Return Type: fetchPairs() always return as an array with key-value pair as output.

Base Definition of function:

  /**
  * @param string|\Magento\Framework\DB\Select $sql An SQL SELECT statement.
  * @param mixed $bind Data to bind into SELECT placeholders.
  * @return array
  */
  public function fetchPairs($sql, $bind = []);

Let’s we are writing a query from the sales_order table to accomplish the fetchPairs() query operation.

<?php
namespace Jesadiya\SelectQuery\Model\ResourceModel;

use Magento\Framework\App\ResourceConnection;

class Data
{
    private const ORDER_TABLE = 'sales_order';
    private ResourceConnection $resourceConnection;

    public function __construct(
        ResourceConnection $resourceConnection
    ) {
        $this->resourceConnection = $resourceConnection;
    }

    /**
     * Fetch pairs Sql Query
     *
     * @return string[]
     */
    public function fetchPairsQuery()
    {
        $connection = $this->resourceConnection->getConnection();
        $tableName = $connection->getTableName(self::ORDER_TABLE);

        $status = 'pending';
        $query = $connection->select()->from($tableName, ['entity_id', 'status'])
                ->where('status = ?', $status);

        return $connection->fetchPairs($query);
    }
}

Output:
Here in the output, you can see the key value is entity_id, and the value is the status of the order.

Array
(
    [1] => pending
    [12] => pending
    [20] => pending
    ....
)

Check for other Direct SQL Query in Magento 2