Skip to content

Instantly share code, notes, and snippets.

@benytocarlo
Last active October 17, 2016 12:15
Show Gist options
  • Save benytocarlo/6628b0ed2998834e9d831cedafbc25c4 to your computer and use it in GitHub Desktop.
Save benytocarlo/6628b0ed2998834e9d831cedafbc25c4 to your computer and use it in GitHub Desktop.
Productos activados en Prestashop
SELECT
p.id_product AS ID,
p.reference AS REFERENCIA,
pl.name AS NOMBRE ,
pm.name AS FABRICANTE,
p.price AS PRECIO ,
pl.link_rewrite as LINK,
pa.reference,
pa.upc,
pa.price,
pai.id_image,
pl.name,
GROUP_CONCAT(DISTINCT(pal.name) SEPARATOR ", ") as combination,
pq.quantity
FROM ps_product p
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
LEFT JOIN ps_stock_available pq ON (p.id_product = pq.id_product AND pa.id_product_attribute = pq.id_product_attribute)
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
LEFT JOIN ps_manufacturer pm ON p.id_manufacturer=pm.id_manufacturer
LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute)
LEFT JOIN ps_attribute_lang pal ON (pac.id_attribute = pal.id_attribute)
LEFT JOIN ps_product_attribute_image pai on(pa.id_product_attribute = pai.id_product_attribute)
WHERE pl.id_lang = 1
AND pal.id_lang = 1 AND p.active=1
GROUP BY pa.reference
ORDER BY p.id_product, pac.id_attribute
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment