Jump to content

SQL SELECT all product include combinations


Scientific
 Share

Recommended Posts

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.

Share this post


Link to post
Share on other sites

  • 2 weeks later...

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 

 

  • Like 2

Share this post


Link to post
Share on other sites

  • 5 months later...
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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More