Skip to content

Instantly share code, notes, and snippets.

@hansek
Created January 24, 2014 09:45
Show Gist options
  • Save hansek/8594656 to your computer and use it in GitHub Desktop.
Save hansek/8594656 to your computer and use it in GitHub Desktop.
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