Skip to content

Instantly share code, notes, and snippets.

@hansek

hansek/gist:8594656

Created Jan 24, 2014
Embed
What would you like to do?
Presta - cross selling - products
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment