Write a fetchPair mysql query in Magento 2.

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

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

Return Type: fetchPair() 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 = []);

/**

* @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 sales_order table to accomplish fetchPair() query operation.

<?php
namespace Path\To\Class;

use Magento\Framework\App\ResourceConnection;

class fetchPair {

    const ORDER_TABLE = 'sales_order';

    /**
     * @var ResourceConnection
     */
    private $resourceConnection;

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

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

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

      $fetchData = $connection->fetchPair($query);
      return $fetchData;
    }
}

<?php

namespace Path\To\Class;

use Magento\Framework\App\ResourceConnection;

class fetchPair {

const ORDER_TABLE = 'sales_order';

/**

* @var ResourceConnection

private $resourceConnection;

public function __construct(

ResourceConnection $resourceConnection

) {

$this->resourceConnection = $resourceConnection;

}

/**

* fetchpair Sql Query

* @return string[]

public function fetchPairQuery()

{

$connection = $this->resourceConnection->getConnection();

$tableName = $connection->getTableName(self::ORDER_TABLE);

$query = $connection->select()

->from($tableName,['entity_id','status'])

->where('status = ?', 'pending');

$fetchData = $connection->fetchPair($query);

return $fetchData;

}

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

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

Array

(

[1] => pending

[12] => pending

[20] => pending

....

)

Check for other Direct SQL Query in Magento 2

Share this: