Mysql operations in Magento 2 collection

Mysql operations in Magento 2 collection like COUNT, GROUP BY and HAVING clause

Today we will see how to use Mysql operations in Magento 2 collection like COUNT, GROUP BY, and HAVING clause.

$collection->getSelect()->columns(['count' => new \Zend_Db_Expr('COUNT(badge)')]);

This will display the COUNT of the attribute badge in the collection.

To apply GROUP BY for the attribute badge –

$collection->getSelect()
           ->columns(['count' => new \Zend_Db_Expr('COUNT(badge)')])
           ->group('badge');

Now if you have some conditions to apply using MySQL HAVING then,

$collection->getSelect()
           ->columns(['count' => new \Zend_Db_Expr('COUNT(badge)')])
           ->group('badge')
           ->having('count like ?', $condition);

where $condition is the condition that you need to apply.

Write custom mysql query in Magento2

However, the use of these might lead to PHPCS warnings.

That’s it.

Happy coding 🙂