Показ всех товаров категории

[sillexo]

Пишу модуль.

Как показать все товары определенной категории включая и товары подкатегорий?

 

Делаю так, но не правильно отображает, показывает и товары других категорий...

public function getMyProducts()
    {
        $sql  = "SELECT p2c.category_id, c.parent_id, p.product_id, pd.name, pd.description, p.image, p.price, p.tax_class_id, ps.product_special_id AS special, (SELECT COUNT(*) AS total FROM " . DB_PREFIX . "review r WHERE r.product_id = p.product_id AND r.status = '1' GROUP BY r.product_id) AS reviews";
        $sql .= " FROM product p";
        $sql .= " LEFT JOIN product_to_category p2c ON (p.product_id = p2c.product_id)";
        $sql .= " LEFT JOIN category c ON (p2c.category_id = c.category_id)";
        $sql .= " LEFT JOIN product_description pd ON (p.product_id = pd.product_id)";
        $sql .= " LEFT JOIN product_special ps ON (ps.product_id = p.product_id)";
        $sql .= " WHERE (c.parent_id = 303)";
        $sql .= " ORDER BY p.date_added DESC";
        $sql .= " LIMIT 6";
        
        $query = $this->db->query($sql);
        return $query->rows;
    }