Counting products in a subtree in Prestashop

The problem

Customer wants to display the total number of products that belongs to a category and all subcategories - ie the subtree of category.

The solution

Prestashop uses Nested Sets to implement hierarchy in categories.

The nested set model is to number the nodes according to a tree traversal, which visits each node twice, assigning numbers in the order of visiting, and at both visits. This leaves two numbers for each node, which are stored as two attributes. (link)

Let's call those numbers nleft and nright. Assume category T' with nleft' and nright'. Any subcategory T'' which has nleft'' and nright'' between nleft' and nright' is contained in the subtree formed by T'.

So the code snippet is:

$count = (int)Db::getInstance()->getValue('
    SELECT SUM(count) FROM (
        SELECT COUNT(*) AS count FROM ' . _DB_PREFIX_ . 'category_product AS cp
        INNER JOIN ' . _DB_PREFIX_ . 'product_shop AS ps ON cp.id_product = ps.id_product
        INNER JOIN ' . _DB_PREFIX_ . 'category c ON cp.id_category = c.id_category
        WHERE c.nleft >= ' . (int)$nleft . ' AND c.nright <= ' . (int)$nright . '
        AND ps.active = 1
        AND ps.id_shop = ' . $id_shop . '
    ) AS c
')