Presta - cross selling - products

gistfile1.sql

SELECT od.product_id,
       pl.name,
       pl.link_rewrite,
       p.reference,
       i.id_image,
       product_shop.show_price,
       cl.link_rewrite category,
       p.ean13,
       COUNT(*) AS product_count
FROM ps_order_detail od
LEFT JOIN ps_product p ON (p.id_product = od.product_id)
INNER JOIN ps_product_shop product_shop ON (product_shop.id_product = p.id_product
                                            AND product_shop.id_shop = 1)
LEFT JOIN ps_product_lang pl ON (pl.id_product = od.product_id
                                 AND pl.id_shop = 1)
LEFT JOIN ps_category_lang cl ON (cl.id_category = product_shop.id_category_default
                                  AND cl.id_shop = 1)
LEFT JOIN ps_image i ON (i.id_product = od.product_id)
WHERE od.id_order IN (52,
                      133)
    AND pl.id_lang = 7
    AND cl.id_lang = 7
    AND od.product_id != 991
    AND i.cover = 1
    AND product_shop.active = 1
GROUP BY product_id
ORDER BY product_count DESC, RAND() LIMIT 10