How to Use of fetchOne(), fetchCol() and fetchPairs() methods in Magento 2

Sunday, September 1, 2024

In this article we will check How to write fetchOne(), fetchCol() and fetchPairs() method in Magento 2.

We will also Create a MySQL fetchOne(), fetchCol() and fetchPairs() query in the Magento standard way to retrieve our desired SQL result rows.

Base Definition of the function:

You can find the defination of all the above functions in the Magento\Framework\DB\Adapter\AdapterInterface class.

public function fetchCol ($sql, $bind = []);

You can use the fetchCol() function to write a direct SQL query without the necessity of handling Model operations.

The fetchCol() function fetches the first column of all SQL result rows as an array.

The first column in each row is used as the array key.

public function getTagIds(PostModel $post)
{
    $adapter = $this->getConnection();
    $select  = $adapter->select()->from(
        $this->postTagTable,
        'tag_id'
    )
        ->where(
            'item_id = ?',
            (int) $post->getId()
        );

    return $adapter->fetchCol($select);
}

public function fetchPairs ($sql, $bind = []);

You can use the fetchPairs() function to write a direct SQL query without the necessity of handling Model operations, as illustrated in the following code snippet.

The fetchPairs() function always returns an array with key-value pairs as the output.

It fetches all SQL result rows as an array of key-value pairs. The first column is the key, the second column is the value.

public function getPostsPosition(CategoryModel $category)
{
    $select = $this->getConnection()->select()->from(
        $this->categoryPostTable,
        ['item_id', 'position']
    )
        ->where(
            'category_id = :category_id'
        );
    $bind   = ['category_id' => (int) $category->getId()];

    return $this->getConnection()->fetchPairs($select, $bind);
}

public function fetchOne ($sql, $bind = []);

You can use the fetchOne() function to write a direct SQL query without the necessity of handling Model operations.

it fetches the first column of the first row of the SQL result.

public function getTagNameById($id)
{
    $adapter = $this->getConnection();
    $select = $adapter->select()
        ->from($this->getMainTable(), 'name')
        ->where('tag_id = :tag_id');
    $binds = ['tag_id' => (int)$id];

    return $adapter->fetchOne($select, $binds);
}

Tag : Magento2, Magento2 Tips

Magento 𝟮