How to create Magento 2 custom collection query

December 7, 2015 - Fire development, Magento 2

To create Magento 2 custom collection query, use the same principle as in case of 1.x (Magento 2 SQL query syntax s the same). For instance:

$connection = $this->getConnection();
$select = $connection->select()
   ->from(
       ['o' => $this->getTable('sales_order_item')],
       ['o.product_type', new \Zend_Db_Expr('COUNT(*)')]
   )
   ->where('o.order_id=?', $orderId)
   ->where('o.product_id IS NOT NULL')
   ->group('o.product_type');