<![CDATA[SELECT ps_shop.`id_shop`, ps_shop.`name`, ps_shop_url.`virtual_uri`, (SELECT COUNT(p.`id_product`)
FROM `ps_shop` s
LEFT JOIN `ps_product` p ON s.`id_shop` = p.`id_shop_default`
WHERE s.`active` = 1
AND p.active = 1
AND s.id_shop = ps_shop.id_shop
ORDER BY s.`name`) as nb_products
FROM ps_shop
LEFT JOIN ps_shop_url ON ps_shop_url.`id_shop` = ps_shop.`id_shop`
INNER JOIN `ps_shop_information_shop` sis ON sis.`id_shop` = ps_shop.`id_shop`
INNER JOIN `ps_shop_information` si ON sis.`id_shop_information` = s.`id_shop_information`
WHERE ps_shop.active = 1 AND ps_shop.id_shop != 1 AND sis.`is_association` != 0
GROUP BY ps_shop.id_shop
ORDER BY ps_shop.name]]>
END