# Counting products in a subtree in Prestashop

26 Sep 2016 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
')
```