SQL SELECT all product include combinations

[Scientific]

Hi,

mco please try to make a SQL SELECT query that returns information about all products including combinations. I can't find it here.

It should somehow combine the tables ps_product and ps_product_attribute, other information such as product images, quantity, etc. I can probably add there via JOIN.

Do you have anyone please ask me such a question?

Thank you very much.

[Scientific]

bump, please for tips how i can resolve this

[knacky]

Here's the base, you can add the rest.

SELECT

    p.id_product, p.reference as product_reference, 
    pa.reference as attribute_reference, CONCAT(p.supplier_reference, ' - ', pa.supplier_reference) as supplier_reference, pa.id_product_attribute as id_product_attribute, pa.upc, pa.price,
    pl.name,
    GROUP_CONCAT('https://', su.domain, su.physical_uri , IF(pai.id_image IS NOT NULL, pai.id_image, pi.id_image), '/', REPLACE(REPLACE(REPLACE(pl.name,' ','_'),'\"',''),'#',""), '.jpg') as image_url,
    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_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 AND pal.id_lang = pl.id_lang)
    LEFT JOIN ps_product_attribute_image pai ON (pa.id_product_attribute = pai.id_product_attribute)
    LEFT JOIN ps_image pi ON (p.id_product = pi.id_product)
    LEFT JOIN ps_shop sh ON (p.id_shop_default = sh.id_shop)
    LEFT JOIN ps_shop_url su ON (su.id_shop = sh.id_shop AND su.main = 1)
    LEFT JOIN ps_lang psl ON (pl.id_lang = psl.id_lang)
/*WHERE pl.id_lang = 1*/

GROUP BY pa.reference

ORDER BY p.id_product, pa.id_product_attribute 

 

[Borgas]

On 3/24/2022 at 8:23 AM, knacky said:

Here's the base, you can add the rest.

SELECT

    p.id_product, p.reference as product_reference, 
    pa.reference as attribute_reference, CONCAT(p.supplier_reference, ' - ', pa.supplier_reference) as supplier_reference, pa.id_product_attribute as id_product_attribute, pa.upc, pa.price,
    pl.name,
    GROUP_CONCAT('https://', su.domain, su.physical_uri , IF(pai.id_image IS NOT NULL, pai.id_image, pi.id_image), '/', REPLACE(REPLACE(REPLACE(pl.name,' ','_'),'\"',''),'#',""), '.jpg') as image_url,
    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_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 AND pal.id_lang = pl.id_lang)
    LEFT JOIN ps_product_attribute_image pai ON (pa.id_product_attribute = pai.id_product_attribute)
    LEFT JOIN ps_image pi ON (p.id_product = pi.id_product)
    LEFT JOIN ps_shop sh ON (p.id_shop_default = sh.id_shop)
    LEFT JOIN ps_shop_url su ON (su.id_shop = sh.id_shop AND su.main = 1)
    LEFT JOIN ps_lang psl ON (pl.id_lang = psl.id_lang)
/*WHERE pl.id_lang = 1*/

GROUP BY pa.reference

ORDER BY p.id_product, pa.id_product_attribute 

 

Hi Knacky

 

Is it possible to point some how, how to extract from orders?

I have one product with combinations, how can i get them but from orders.

Thank you

[knacky]

Hi.

You need to give more information and be specific.
What parameters should be searched for?
Which product ID and which attribute ID?
What should be the result?

Is SQL needed or is it better to use a PHP script?
I recommend PHP script.

[delonatelo]

On 3/24/2022 at 11:23 AM, knacky said:

Here's the base, you can add the rest.

SELECT

    p.id_product, p.reference as product_reference, 
    pa.reference as attribute_reference, CONCAT(p.supplier_reference, ' - ', pa.supplier_reference) as supplier_reference, pa.id_product_attribute as id_product_attribute, pa.upc, pa.price,
    pl.name,
    GROUP_CONCAT('https://', su.domain, su.physical_uri , IF(pai.id_image IS NOT NULL, pai.id_image, pi.id_image), '/', REPLACE(REPLACE(REPLACE(pl.name,' ','_'),'\"',''),'#',""), '.jpg') as image_url,
    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_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 AND pal.id_lang = pl.id_lang)
    LEFT JOIN ps_product_attribute_image pai ON (pa.id_product_attribute = pai.id_product_attribute)
    LEFT JOIN ps_image pi ON (p.id_product = pi.id_product)
    LEFT JOIN ps_shop sh ON (p.id_shop_default = sh.id_shop)
    LEFT JOIN ps_shop_url su ON (su.id_shop = sh.id_shop AND su.main = 1)
    LEFT JOIN ps_lang psl ON (pl.id_lang = psl.id_lang)
/*WHERE pl.id_lang = 1*/

GROUP BY pa.reference

ORDER BY p.id_product, pa.id_product_attribute 

 

This is fantastic, and very valuable - but here's a problem with "GROUP_CONCAT(DISTINCT(pal.name) SEPARATOR ', ') as combination".  It produces a result  - sometimes size first, sometimes colour first, depending on alphabetic order.  This makes this column difficult to manage, and impossible to use further.  Is it possible to have it download consistently?  correct order based on combinations imported - eg: Size, Colour.

[delonatelo]

3 minutes ago, delonatelo said:

This is fantastic, and very valuable - but here's a problem with "GROUP_CONCAT(DISTINCT(pal.name) SEPARATOR ', ') as combination".  It produces a result  - sometimes size first, sometimes colour first, depending on alphabetic order.  This makes this column difficult to manage, and impossible to use further.  Is it possible to have it download consistently?  correct order based on combinations imported - eg: Size, Colour.

To explain see screenshot

[delonatelo]

Anybody ?