Query: Product information by manufacturer ? PS 1.7.x

[wxanl]

Hi,

is it possible to build a query that generates a list of product by manufacturer? I am using PS 1.7.x

I want a list that shows basically information like:

Manufacturer 1  - Product ID - Product name - Product Category - Price excl. vat - Price incl. vat 
Manufacturer 1  - Product ID - Product name - Product Category - Price excl. vat - Price incl. vat 
Manufacturer 2  - Product ID - Product name - Product Category - Price excl. vat - Price incl. vat 
Manufacturer 2  - Product ID - Product name - Product Category - Price excl. vat - Price incl. vat 

So i tried this:

SELECT 
    p.id_product, 
    pa.reference,
    pl.name, 
    GROUP_CONCAT(DISTINCT(pal.name) SEPARATOR ", ") as combination, 
    p.price, 
    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)
WHERE pl.id_lang = 1 
AND pal.id_lang = 1 
GROUP BY pa.reference
ORDER BY p.id_product, pac.id_attribute

Which shows me basically most of the information i need but i am missing

- Category
- Price incl. Vat
- Manufacturer

Can someone point me in the right direction please ?

Edited October 8, 2022 by wxanl (see edit history)

[wxanl]

Anyone got a clue ?  I did try to add a left join to ps_manufacturer but that did not work. 
So any help would be welcome.

[ps8modules]

SELECT ..... pm.manufacturer_lang as manufacturer_name, pa.id_manufacturer as id_manufacturer

...

LEFT JOIN ps_manufacturer_lang AS pm ON (p.id_manufacturer = pm.id_manufacturer)

[wxanl]

2 hours ago, 4you.software said:

SELECT ..... pm.manufacturer_lang as manufacturer_name, pa.id_manufacturer as id_manufacturer

...

LEFT JOIN ps_manufacturer_lang AS pm ON (p.id_manufacturer = pm.id_manufacturer)

Thank a lot.
I added those to the query but the output was empty. So i am not sure about the position. 

 

SELECT 
    p.id_product, 
    pa.reference,
    pl.name, 
    GROUP_CONCAT(DISTINCT(pal.name) SEPARATOR ", ") as combination, 
    p.price, 
    pq.quantity 
   pm.manufacturer_lang as manufacturer_name, pa.id_manufacturer as id_manufacturer
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)
LEFT JOIN ps_manufacturer_lang AS pm ON (p.id_manufacturer = pm.id_manufacturer)

WHERE pl.id_lang = 1 
AND pal.id_lang = 1 
GROUP BY pa.reference
ORDER BY p.id_product, pac.id_attribute

[ps8modules]

I wrote it off the top of my head and there is a mistake.
 

 SELECT 
    pm.id_manufacturer as id_manufacturer,
    pm.name as manufacturer_name,
    p.id_product, 
    pa.reference,
    pl.name, 
    GROUP_CONCAT(DISTINCT(pal.name) SEPARATOR ", ") as combination, 
    p.price, 
    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)
LEFT JOIN ps_manufacturer AS pm ON (p.id_manufacturer = pm.id_manufacturer)


WHERE pl.id_lang = 1 
AND pal.id_lang = 1 
GROUP BY pa.reference
ORDER BY p.id_product, pac.id_attribute 

 

result:

Edited October 10, 2022 by 4you.software (see edit history)

[wxanl]

1 hour ago, 4you.software said:

I wrote it off the top of my head and there is a mistake.
 

 SELECT 
    pm.id_manufacturer as id_manufacturer,
    pm.name as manufacturer_name,
    p.id_product, 
    pa.reference,
    pl.name, 
    GROUP_CONCAT(DISTINCT(pal.name) SEPARATOR ", ") as combination, 
    p.price, 
    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)
LEFT JOIN ps_manufacturer AS pm ON (p.id_manufacturer = pm.id_manufacturer)


WHERE pl.id_lang = 1 
AND pal.id_lang = 1 
GROUP BY pa.reference
ORDER BY p.id_product, pac.id_attribute 

 

result:

Ahhh now i see where i went wrong. Thank you so much for explaining it ! 🙂

[ps8modules]

I gladly helped.
You can Like me by clicking on the gray heart below the posts 😉