In this article I will try to cover Write a fetchCol() Sql query in Magento 2. Develop a Magento fetchCol() MySQL query without relying on the Model file, adhering to the standard Magento methodology. The fetchCol() function is designed to fetch the first column of all SQL result rows as an array, consistently returning an array.
You may also like this : How to Write SQL Query in Magento 2 With Standard Way
Base Definition:
fetchCol function fetches the first column of all SQL result as an array like the fisrt column in each row is used as the array key.
Magento\Framework\DB\Adapter\AdapterInterface::fetchCol($sql, $bind = []);public function fetchCol($sql, $bind = []);Now, let’s implement a query from the customer_entity table to execute the fetchCol() 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->getAllRecordsByFetchCol($storeId);
print_r($customerData);
$this->logger->info('customer_data',$customerData);
}
/**
* @param int $id
*
* @return array
*/
public function getAllRecordsByFetchCol($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->fetchCol($query, $bind);
}
}Output:
The result is an array containing the entity_id values from the customer_entity table with a store_id number is 1.
Array
(
[0] => 1
[1] => 2
........
........
)
You can also refer the some example from magento2 core files like Magento\Framework\Setup\Declaration\Schema\Db\MySQL\DbSchemaReader::readTables($resource)
I believe that this article, titled “Write a fetchCol() Sql query in Magento 2” simplifies the understanding of crafting fetchCol() SQL queries in Magento 2. If I missed details or if you have additional information to contribute. I’ll promptly respond with the appropriate solution.
Keep appreciating and sharing!
