Write a Mysql fetchCol() query using Magento standard way without using the Model file.
To Fetches the first column of all SQL result rows as an array.
Return Type: fetchCol() always returns as an array.
Base Definition:
/**
* @param string|\Magento\Framework\DB\Select $sql An SQL SELECT statement.
* @param mixed $bind Data to bind into SELECT placeholders.
* @return array
*/
public function fetchCol($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 fetchCol($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 fetchCol($sql, $bind = []);
Let’s write a query from the sales_order table to accomplish the fetchCol()
query operation.
<?php
namespace Path\To\Class;
use Magento\Framework\App\ResourceConnection;
class FetchCol {
const ORDER_TABLE = 'sales_order';
private ResourceConnection $resourceConnection;
public function __construct(
ResourceConnection $resourceConnection
) {
$this->resourceConnection = $resourceConnection;
}
/**
* insert Sql Query
*
* @return string[]
*/
public function fetchColQuery(): array
{
$connection = $this->resourceConnection->getConnection();
$tableName = $connection->getTableName(self::ORDER_TABLE);
$query = $connection->select()
->from($tableName,['entity_id','status'])
->where('status = ?', 'pending');
return $connection->fetchCol($query);
}
}
<?php
namespace Path\To\Class;
use Magento\Framework\App\ResourceConnection;
class FetchCol {
const ORDER_TABLE = 'sales_order';
private ResourceConnection $resourceConnection;
public function __construct(
ResourceConnection $resourceConnection
) {
$this->resourceConnection = $resourceConnection;
}
/**
* insert Sql Query
*
* @return string[]
*/
public function fetchColQuery(): array
{
$connection = $this->resourceConnection->getConnection();
$tableName = $connection->getTableName(self::ORDER_TABLE);
$query = $connection->select()
->from($tableName,['entity_id','status'])
->where('status = ?', 'pending');
return $connection->fetchCol($query);
}
}
<?php namespace Path\To\Class; use Magento\Framework\App\ResourceConnection; class FetchCol { const ORDER_TABLE = 'sales_order'; private ResourceConnection $resourceConnection; public function __construct( ResourceConnection $resourceConnection ) { $this->resourceConnection = $resourceConnection; } /** * insert Sql Query * * @return string[] */ public function fetchColQuery(): array { $connection = $this->resourceConnection->getConnection(); $tableName = $connection->getTableName(self::ORDER_TABLE); $query = $connection->select() ->from($tableName,['entity_id','status']) ->where('status = ?', 'pending'); return $connection->fetchCol($query); } }
Output:
An Array result with value is entity_id of sales_order table.
Array
(
[1] => 15
[2] => 17
[3] => 19
....
)
Array
(
[1] => 15
[2] => 17
[3] => 19
....
)
Array ( [1] => 15 [2] => 17 [3] => 19 .... )
15, 17, and 19 are the entity_id with pending status.