Write a fetchAssoc mysql query in Magento 2.

Write a Mysql fetchAssoc() query using Magento standard way for Fetches all the SQL result rows as an associative array as an output.

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

Return Type: fetchAssoc() always returns as an array with the first column as the key and the entire row array as the value.

Base Definition of function:

    /**
     * Fetches all SQL result rows as an associative array.
     *
     * @param string|\Magento\Framework\DB\Select $sql An SQL SELECT statement.
     * @param mixed $bind Data to bind into SELECT placeholders.
     * @return array
     */
    public function fetchAssoc($sql, $bind = []);

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

<?php

namespace Your\Model\ClassPath;

use Magento\Framework\App\ResourceConnection;

class fetchAssoc
{
    public const ORDER_TABLE = 'sales_order';

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

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

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

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

You need to write a custom SQL select query and add that query into the fetchAssoc() function same as the above code.

Output:
You can see key value is always the first-row value of the table. Like 1, 10, and 14 are the entity_id of the result, and it’s the first key of the sales_order table.

fetchAll() shows the result data with a key starting from 0 in the array result.

fetchAssoc() shows the record’s primary key value as the key of the result data.

Array
(
    [1] => Array
        (
            [entity_id] => 1
            [status] => pending
            [grand_total] => 334.9900
        )

    [10] => Array
        (
            [entity_id] => 10
            [status] => pending
            [grand_total] => 69.0000
        )

    [14] => Array
        (
            [entity_id] => 14
            [status] => pending
            [grand_total] => 280.0000
        )

    ...
)

Check for other Direct SQL Query in Magento 2