How to execute the COUNT() SQL function

dmitry_fedyuk · March 15, 2016, 3:26am

Example 1

magento/magento2/blob/2.0.0/app/code/Magento/Catalog/Model/ResourceModel/Category.php#L970-L981


/**
 * Get total number of persistent categories in the system, excluding the default category
 *
 * @return int
 */
public function countVisible()
{
    $connection = $this->getConnection();
    $select = $connection->select();
    $select->from($this->getEntityTable(), 'COUNT(*)')->where('parent_id != ?', 0);
    return (int)$connection->fetchOne($select);
}

Example 2

github.com

magento/magento2/blob/2.0.0/app/code/Magento/Catalog/Model/ResourceModel/Category.php#L595-L616


/**
 * Get products count in category
 *
 * @param \Magento\Catalog\Model\Category $category
 * @return int
 */
public function getProductCount($category)
{
    $productTable = $this->_resource->getTableName('catalog_category_product');


    $select = $this->getConnection()->select()->from(
        ['main_table' => $productTable],
        [new \Zend_Db_Expr('COUNT(main_table.product_id)')]
    )->where(
        'main_table.category_id = :category_id'
    );


    $bind = ['category_id' => (int)$category->getId()];
    $counts = $this->getConnection()->fetchOne($select, $bind);


    return intval($counts);
}

Example 3

github.com

magento/magento2/blob/2.0.0/app/code/Magento/AdminNotification/Model/ResourceModel/Inbox.php#L56-L83


/**
 * Get notifications grouped by severity
 *
 * @param \Magento\AdminNotification\Model\Inbox $object
 * @return array
 * @SuppressWarnings(PHPMD.UnusedFormalParameter)
 */
public function getNoticeStatus(\Magento\AdminNotification\Model\Inbox $object)
{
    $connection = $this->getConnection();
    $select = $connection->select()->from(
        $this->getMainTable(),
        [
            'severity' => 'severity',
            'count_notice' => new \Zend_Db_Expr('COUNT(' . $this->getIdFieldName() . ')')
        ]
    )->group(
        'severity'
    )->where(
        'is_remove=?',
        0
    )->where(
        'is_read=?',
        0
    );
    $return = $connection->fetchPairs($select);
    return $return;
}

Example 4

github.com

magento/magento2/blob/2.0.0/app/code/Magento/Catalog/Model/ResourceModel/Product.php#L636-L648


/**
 * Get total number of records in the system
 *
 * @return int
 */
public function countAll()
{
    $connection = $this->getConnection();
    $select = $connection->select();
    $select->from($this->getEntityTable(), 'COUNT(*)');
    $result = (int)$connection->fetchOne($select);
    return $result;
}

Example 5

github.com

magento/magento2/blob/2.0.0/app/code/Magento/Catalog/Model/ResourceModel/Category/Flat.php#L455-L476


/**
 * Get count of active/not active children categories
 *
 * @param \Magento\Catalog\Model\Category $category
 * @param bool $isActiveFlag
 * @return integer
 */
public function getChildrenAmount($category, $isActiveFlag = true)
{
    $_table = $this->getMainStoreTable($category->getStoreId());
    $select = $this->getConnection()->select()->from(
        $_table,
        "COUNT({$_table}.entity_id)"
    )->where(
        "{$_table}.path LIKE ?",
        $category->getPath() . '/%'
    )->where(
        "{$_table}.is_active = ?",
        (int)$isActiveFlag
    );
    return (int)$this->getConnection()->fetchOne($select);
}

David_O_Connor · November 30, 2018, 10:13am

All the links are broken

dmitry_fedyuk · December 1, 2018, 8:28am

Fixed.