SQL query to get product stock for all combinations

[Guest]

Hi 
 
I am struggling with creating a sql query to use in the backoffice. I want to get the complete inventory so I can output it to .csv.
 
This question is pretty close but it still does not work 100%.
 
SELECT m.name AS manufacturer, p.id_product, pl.name, al.name AS ATTRIBUTE, pa.reference, p.wholesale_price, s.quantity
FROM ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
LEFT JOIN ps_product_attribute_combination pac ON (pac.id_product_attribute = pa.id_product_attribute)
LEFT JOIN ps_attribute_lang al ON (al.id_attribute = pac.id_attribute)
LEFT JOIN ps_stock_available s ON (p.id_product = s.id_product)
LEFT JOIN ps_manufacturer m ON (p.id_manufacturer = m.id_manufacturer)
WHERE pl.id_lang = 1
GROUP BY pac.id_product_attribute
ORDER BY p.id_manufacturer, p.id_product

 

The are two problems with this query:

1. It only outputs products that has attributes. All other products are not listed.

2. The quantity is not the individual quantity but the total quantity for all combinations of the product. If there are 2 variants of a product with 5 items each in stock, both lines outputs 10 as quantity and not 5+5.

 

If someone with more sql knowledge could help out it would be fantastic.

 

Thanks in advance

[Guest]

bump

 

Anyone? It does not seem like a strange question. For me it would help dramatically when I need to plan an order from a distributor to be able to dump the current stock into a csv file like this.

[rvcat]

Hello,

Is it really important to have this field al.name ATTRIBUTE ?

It is normal that your query returns multiple results

 

 

SELECT m.name AS manufacturer, p.id_product, pa.id_product_attribute,
       if(pa.id_product is null, p.reference, pa.reference) reference, p.wholesale_price, s.quantity
FROM ps_product p
JOIN ps_product_lang pl ON (p.id_product = pl.id_product
                                           and pl.id_lang = 1)
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)

LEFT JOIN ps_stock_available s ON (p.id_product = s.id_product
                                                         and ((pa.id_product_attribute is null and s.id_product_attribute = 0)
                                                                 or (s.id_product_attribute = pa.id_product_attribute)))

LEFT JOIN ps_manufacturer m ON (p.id_manufacturer = m.id_manufacturer)
ORDER BY p.id_manufacturer, p.id_product, pa.id_product_attribute
I did not set the table attributes for as a fair result must be nested tables 5 and then make a group_concat on results and they should be sorted in the order that you have configured.

[Guest]

Thanks for trying to help

 

I have tested this query but I just get an Error message.

 

I started to remove things from it is the problem is the AND and OR operators inside the LEFT JOIN.

 

If I remove and pl.id_lang = 1 and and ((pa.id_product_attribute is null and s.id_product_attribute = 0)

                                                                 or (s.id_product_attribute = pa.id_product_attribute))

 

the query executes but with that info included, it does not. I am using Prestashop 1.5.4.1. 

 

Does it work for you?

 

Thanks for the effort

[eleazar]

Hi guys,

 

the following query includes products with combinations:

SELECT m.name AS manufacturer, p.id_product, pl.name, GROUP_CONCAT(DISTINCT(al.name) SEPARATOR ", ") AS combinations, 
GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ",") AS categories, p.price, pa.price, p.id_tax_rules_group, p.wholesale_price, 
p.reference, p.supplier_reference, p.id_supplier, p.id_manufacturer, p.upc, p.ecotax, p.weight, s.quantity, 
pl.description_short, pl.description, pl.meta_title, pl.meta_keywords, pl.meta_description, pl.link_rewrite, 
pl.available_now, pl.available_later, p.available_for_order, p.date_add, p.show_price, p.online_only, p.condition, 
p.id_shop_default
FROM ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
LEFT JOIN ps_manufacturer m ON (p.id_manufacturer = m.id_manufacturer)
LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category)
LEFT JOIN ps_category c ON (cp.id_category = c.id_category)
LEFT JOIN ps_stock_available s ON (p.id_product = s.id_product)
LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
LEFT JOIN ps_product_attribute_combination pac ON (pac.id_product_attribute = pa.id_product_attribute)
LEFT JOIN ps_attribute_lang al ON (al.id_attribute = pac.id_attribute)
WHERE pl.id_lang = 1
AND cl.id_lang = 1
AND p.id_shop_default = 1
AND c.id_shop_default = 1
GROUP BY pac.id_product_attribute

Maybe you have to change the language id if it's not 1 or the default prefix if it's not ps_.

Edited September 19, 2015 by eleazar  (see edit history)

[Guest]

Thanks for the help. It seems like a really promising step. However I tried this and first I ran into the 1000 char limit so I simplified it a little removing things I do not need. So the same query but slightly shorter is:

 

SELECT m.name AS manufacturer, pl.name, p.reference, al.name AS attribute, p.wholesale_price, s.quantity as stock_quantity

FROM ps_product p

LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)

LEFT JOIN ps_manufacturer m ON (p.id_manufacturer = m.id_manufacturer)

LEFT JOIN ps_stock_available s ON (p.id_product = s.id_product)

LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)

LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)

LEFT JOIN ps_product_attribute_combination pac ON (pac.id_product_attribute = pa.id_product_attribute)

LEFT JOIN ps_attribute_lang al ON (al.id_attribute = pac.id_attribute)

WHERE pl.id_lang = 1

AND p.id_shop_default = 1

GROUP BY m.name, pac.id_product_attribute

 

However with this query I see two issues:

1. The s.quantity is the accumulated quantity of all combinations of a product. If I have a 10 x of a product in white and 10 x in black. The quantity is 20. Not 10 for each.

2. I do not know why, but I have at least one product that is not included when running this query. It is a product with does not have any combinations but it is of the same brand as several others that does show. I will try to look further but it is really strange since the product is available for order on the store.

[Adria Laxson]

It  seems to me if you combined what you've just made with some of the code from this below you'd get quantities for each combination. Please let me know if you figure it out because I'm working on it now for myself.

 

SELECT
    p.id_product,
    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_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
GROUP BY pa.reference
ORDER BY p.id_product, pac.id_attribute

[Adria Laxson]

Actually this seems to have done it for me below. Hope it does for you as well.

 

SELECT
    p.id_product,
    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_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
GROUP BY pac.id_product_attribute
 

[Guest]

Hi Adria

 

Thanks for trying to help.

 

I get an error when executing this query, it is this line that is failing:

LEFT JOIN ps_stock_available pq ON (p.id_product = pq.id_product AND pa.id_product_attribute = pq.id_product_attribute)

 

If I insert them one by one such as:

LEFT JOIN ps_stock_available pq ON (p.id_product = pq.id_product)

LEFT JOIN ps_stock_available pq ON (pa.id_product_attribute = pq.id_product_attribute)

 

I can save the query ok. So it is not a typo but something about how the AND is used. Does it work for you, I am on Prestashop 1.5.4.1 but I guess that should not be much different to 1.6.x either?

 

I think it looks close but I can not figure out what is wrong with this line.

[Adria Laxson]

I have presta 1.6 & it works like a charm. Maybe this?

 

SELECT p.id_product, 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_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
GROUP BY pac.id_product_attribute

[Adria Laxson]

Actually my version is 1.5.6.1

[Guest]

Amazing, we have almost the same version but this keeps failing here.

 

It is the same issue as before, if I remove the AND I can save the Query ok but with the AND, it just gives the very non informative Error with no explanation.

 

Thank you so much for trying to help, right now this is just weird. If it works for you it must be something in Prestashop.

[eleazar]

@hubbobubbo2

 

And you sure you have the same shop id and language id?

[Adria Laxson]

Here's another version that works well it seems. I'd be curious to see if you get the same error at the AND line

 

SELECT
CONCAT(pl.name,' / ',pal.name,' / ', pq.quantity) AS required
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_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category)
LEFT JOIN ps_category c ON (cp.id_category = c.id_category)
WHERE pl.id_lang = 1
AND pal.id_lang = 1
GROUP BY p.id_product,pal.name
ORDER BY p.id_product, pac.id_attribute

[Guest]

Thanks again or helping. Yes, it behaves exactly the same. 

 

Using only

LEFT JOIN ps_stock_available pq ON (p.id_product = pq.id_product)

 

or 

 

LEFT JOIN ps_stock_available pq ON (pa.id_product_attribute = pq.id_product_attribute)  

 

is ok but as soon as I add the AND I can not save the query. It's frustrating, maybe I have my self to blame for being on an older version but it's not that old and this seems like basic functionality.

 

@eleazar, thanks for the comment but the lang_id=1 is fine and does not seem related to this problem.

[KarZan]

Hi

 

I have been looking answer for this for some time now. I looked at the first solution by eleazar but there were some issues with that. Like the colors came with all languages and products with no combinations had all categories. Then I tried the last one from Adria but that did not work for me because if a product had more than one attribute in a combination (say red, size 5) they were on two lines. So I tried changing it a bit and came up with this which seems to work for me

SELECT 
	m.name AS 'Manufacturer', 
	p.id_product 'Product ID', 
	pl.name 'Product Name', 
	GROUP_CONCAT(DISTINCT(al.name) SEPARATOR ", ") AS 'Combination', 
	GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ",") AS 'Categories', 
	p.price 'Price', 
	pa.price 'Combination Price', 
	p.id_tax_rules_group 'VAT Group', 
	p.wholesale_price 'Wholesale Price', 
	p.reference 'Reference', 
	p.supplier_reference 'Supplier Reference', 
	p.weight 'Weight', 
	s.quantity 'Quantity', 
	pl.description_short 'Short Description', 
	pl.description 'Description', 
	pl.meta_title 'Meta Title', 
	pl.meta_keywords 'Meta Keywords', 
	pl.meta_description 'Meta Description', 
	pl.link_rewrite 'Link Rewrite', 
	pl.available_now 'Available Now', 
	pl.available_later 'Available Later', 
	p.available_for_order 'Available For Order', 
	p.date_add 'Date Added', 
	p.show_price 'Show Price', 
	p.online_only 'Online Only'
FROM ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product and pl.id_lang=2)
LEFT JOIN ps_manufacturer m ON (p.id_manufacturer = m.id_manufacturer)
LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN ps_category c ON (cp.id_category = c.id_category)
LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category and cl.id_lang=2)
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
LEFT JOIN ps_stock_available s ON (p.id_product = s.id_product and pa.id_product_attribute=s.id_product_attribute)
LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)
LEFT JOIN ps_product_attribute_combination pac ON (pac.id_product_attribute = pa.id_product_attribute)
LEFT JOIN ps_attribute_lang al ON (al.id_attribute = pac.id_attribute and al.id_lang=2)
GROUP BY p.id_product,pac.id_product_attribute
order by p.id_product

The languages must of cource be set apropriate and if there are more than one shop it has to be taken into consideration.

 

EDIT: Forgot to mention that I have Prestashop 1.6.1.1

Edited November 3, 2015 by KarZan (see edit history)

[matiss.jekabsons]

Could someone help with similar type of help...
With MySQL query for selled products (so i could see all product in packs too) in period of time?
 

[robepo]

Hi Kar Zan,

 

your query would be great, but products without combinations quantity shows null. Any ideas how to fix it?

[robepo]

Hi Adria, your query don't show products without combinations. May you know how to export all products (not just with combinations)

[Adria Laxson]

Sorry I just saw this. There are several ways. This one has a lot of detail. Not sure what version you have...

 

SELECT p.id_product, p.active, pl.name, GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ",") as categories, p.price, p.id_tax_rules_group, p.wholesale_price, p.reference, p.supplier_reference, p.id_supplier, p.id_manufacturer, p.upc, p.ecotax, p.weight, p.quantity, p.out_of_stock, pl.description_short, pl.description, pl.meta_title, pl.meta_keywords, pl.meta_description, pl.link_rewrite, pl.available_now, pl.available_later, p.available_for_order, p.date_add, p.show_price, p.online_only, p.condition, p.id_shop_default
FROM ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category)
LEFT JOIN ps_category c ON (cp.id_category = c.id_category)
LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)
WHERE pl.id_lang = 1
AND cl.id_lang = 1
AND p.id_shop_default = 1 AND c.id_shop_default = 1
GROUP BY p.id_product

[robepo]

Hi Adria, thanks for your response. In this version there is no product combinations name.

After some tests I've wrote query what I needed. It exports products with its combinations and quantities. But it works just for exporting data.

Importings as csv file through prestashop backoffice is not suitible.

Anyway, thanks for your help!

There is my querry:

 

SELECT
 m.name AS 'Manufacturer',
if (p.reference ='' or p.reference = null ,  pa.reference, p.reference) as Reference,

 p.id_product 'Product ID',
 p.price 'Price',  
 pl.name 'Product Name',
 GROUP_CONCAT(DISTINCT(al.name) SEPARATOR ", ") AS 'Combination',
 s.quantity 'Quantity'
FROM ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product and pl.id_lang=3)
LEFT JOIN ps_manufacturer m ON (p.id_manufacturer = m.id_manufacturer)
LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN ps_category c ON (cp.id_category = c.id_category)
LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category and cl.id_lang=3)
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
LEFT JOIN ps_stock_available s ON (p.id_product = s.id_product and ((pa.id_product_attribute is null and s.id_product_attribute = 0)
                                                                 or (s.id_product_attribute = pa.id_product_attribute)))
LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)
LEFT JOIN ps_product_attribute_combination pac ON (pac.id_product_attribute = pa.id_product_attribute)
LEFT JOIN ps_attribute_lang al ON (al.id_attribute = pac.id_attribute and al.id_lang=3)
GROUP BY p.id_product,pac.id_product_attribute
order by p.id_product

[Adria Laxson]

I see. I've had lots of luck importing orders, products, customers, etc from the backoffice. I downloaded apache openoffice for free & it's a great csv editing tool for importing back into presta.

[KarZan]

Sorry robepo I had been using different email and thus did not notice you had asked about my query. But yes I noticed also that products without combinations were dropped from the results. So I did do some modifications.

 

Also I have a lot of fields in it so that I can use it as a base query to build up queries for different purposes

 

And I have also combination specific things like reference for combination (which your query shows only if product reference is empty but which I noticed I needed separately to follow for stock rotation) and also price for combination (in case the combination has an impact on price). I am still lacking the specials price in the query if such exists but have not had a real need for it yet .

 

So basically my query is same as yours This is how my query looks like now.

 

SELECT
p.active 'Active',
m.name 'Manufacturer',
p.id_product 'Product number',
p.reference 'Reference',
pl.name 'Product name',
GROUP_CONCAT(DISTINCT(al.name) SEPARATOR ", ") AS 'Combination',
s.quantity 'Quantity',
p.price 'Price w/o VAT',
pa.price 'Combination price',
p.wholesale_price 'Wholesale price',
GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ",") AS 'Product groups',
p.weight 'Weight',
p.id_tax_rules_group 'TAX group',
pa.reference 'Combination reference',
pl.description_short 'Short description',
pl.description 'Long description',
pl.meta_title 'Meta Title',
pl.meta_keywords 'Meta Keywords',
pl.meta_description 'Meta Description',
pl.link_rewrite 'Link',
pl.available_now 'In stock text',
pl.available_later 'Coming text',
p.available_for_order 'Orderable text',
p.date_add 'Added',
p.show_price 'Show price',
p.online_only 'Only online'
FROM
ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product and pl.id_lang=2)
LEFT JOIN ps_manufacturer m ON (p.id_manufacturer = m.id_manufacturer)
LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN ps_category c ON (cp.id_category = c.id_category)
LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category and cl.id_lang=2)
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
LEFT JOIN ps_stock_available s ON (p.id_product = s.id_product and (pa.id_product_attribute=s.id_product_attribute or pa.id_product_attribute is null))
LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)
LEFT JOIN ps_product_attribute_combination pac ON (pac.id_product_attribute = pa.id_product_attribute)
LEFT JOIN ps_attribute_lang al ON (al.id_attribute = pac.id_attribute and al.id_lang=2)
GROUP BY p.id_product,pac.id_product_attribute order by p.id_product

[san_merah]

Hi Adria, thanks for your response. In this version there is no product combinations name.

After some tests I've wrote query what I needed. It exports products with its combinations and quantities. But it works just for exporting data.

Importings as csv file through prestashop backoffice is not suitible.

Anyway, thanks for your help!

There is my querry:

 

SELECT

 m.name AS 'Manufacturer',

if (p.reference ='' or p.reference = null ,  pa.reference, p.reference) as Reference,

 

 p.id_product 'Product ID',

 p.price 'Price',  

 pl.name 'Product Name',

 GROUP_CONCAT(DISTINCT(al.name) SEPARATOR ", ") AS 'Combination',

 s.quantity 'Quantity'

FROM ps_product p

LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product and pl.id_lang=3)

LEFT JOIN ps_manufacturer m ON (p.id_manufacturer = m.id_manufacturer)

LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)

LEFT JOIN ps_category c ON (cp.id_category = c.id_category)

LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category and cl.id_lang=3)

LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)

LEFT JOIN ps_stock_available s ON (p.id_product = s.id_product and ((pa.id_product_attribute is null and s.id_product_attribute = 0)

                                                                 or (s.id_product_attribute = pa.id_product_attribute)))

LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)

LEFT JOIN ps_product_attribute_combination pac ON (pac.id_product_attribute = pa.id_product_attribute)

LEFT JOIN ps_attribute_lang al ON (al.id_attribute = pac.id_attribute and al.id_lang=3)

GROUP BY p.id_product,pac.id_product_attribute

order by p.id_product

 

 

This one does exactly what I wanted. Thanks!

[PrestaShark]

Here's another version that works well it seems. I'd be curious to see if you get the same error at the AND line

 

SELECT

CONCAT(pl.name,' / ',pal.name,' / ', pq.quantity) AS required

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_category_product cp ON (p.id_product = cp.id_product)

LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category)

LEFT JOIN ps_category c ON (cp.id_category = c.id_category)

WHERE pl.id_lang = 1

AND pal.id_lang = 1

GROUP BY p.id_product,pal.name

ORDER BY p.id_product, pac.id_attribute

 

Very nice Adria. Useful piece of query!

Any ideas how to show only available product attributes? (im weak with sql queries ;( )

 

Thanks!

[hakeryk2]

Here's another version that works well it seems. I'd be curious to see if you get the same error at the AND line

 

SELECT

CONCAT(pl.name,' / ',pal.name,' / ', pq.quantity) AS required

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_category_product cp ON (p.id_product = cp.id_product)

LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category)

LEFT JOIN ps_category c ON (cp.id_category = c.id_category)

WHERE pl.id_lang = 1

AND pal.id_lang = 1

GROUP BY p.id_product,pal.name

ORDER BY p.id_product, pac.id_attribute

 

 

Adria You're a legend! I was making searching product with combinations by ajax and I used this code based on Your work.

SELECT
    p.id_product,
    pa.id_product_attribute,
    CONCAT(pl.name, ' ',pal.name) as name
FROM ps_product p
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
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 pac.id_product_attribute

P.S If someone will have error on mysql try to edit my.ini and whenever you like this code and restart mysql server.

[mysqld]
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

I was a little bit confused because I was working with those SQL stamenents od my PC in house and everything was working but when I move to another PC with different WAMP version the code was not working. Adding this to mysql config file helped.

 

 

EDIT:

 

I found out that none of your code is generating products with more then 1 attribute, it is just not showing the second or third combination. Any help?

 

EDIT v2:

I get it done If someone wants to search for products just use this code below and change phrase in 12 line Like statement Your Product Name with attr

SELECT
    p.id_product,
    pa.id_product_attribute,
    CONCAT(pl.name, ' ',GROUP_CONCAT(DISTINCT(pal.name) SEPARATOR ", ")) as productname
FROM ps_product p
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
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 AND CONCAT (pl.name, ' ', pal.name)
    LIKE "%Your Product Name with attr%"
GROUP BY pac.id_product_attribute

Edited December 19, 2016 by hakeryk2 (see edit history)

[rvcat]

another version with all combinaitions of attributes with all lang.

For me in my database, I made a view to have combinations of attributes, it's simpler after to have it in SQL queries

* Warning to prefixes. 

In this version the attributes exit is in the expected order

 

SELECT 

   m.name AS manufacturer, 

p.id_product,

cm.id_product_attribute, 

pl.name, 

   coalesce(pa.reference, p.reference) as reference,

   p.wholesale_price, s.quantity,

   cm.combinaison AS ATTRIBUTE

FROM product p

LEFT JOIN product_lang pl ON (p.id_product = pl.id_product)

LEFT JOIN product_attribute pa ON (p.id_product = pa.id_product)

LEFT JOIN stock_available s ON (p.id_product = s.id_product

                               and ((pa.id_product_attribute is null and s.id_product_attribute = 0)

                                     or (s.id_product_attribute = pa.id_product_attribute)))

 

LEFT JOIN manufacturer m ON (p.id_manufacturer = m.id_manufacturer)

 

LEFT JOIN (SELECT 

               `v`.`id_product_attribute` AS `id_product_attribute`,

               `v`.`id_lang` AS `id_lang`, 

               GROUP_CONCAT(CONCAT(`v`.`group_name`,' ',`v`.`attribute_name`) ORDER BY `v`.`group_position` ASC,`v`.`attribute_position` ASC SEPARATOR ',') AS `combinaison`

            FROM (

                SELECT `pac`.`id_product_attribute` AS `id_product_attribute`,

                       `pa`.`id_product` AS `id_product`,

                       `al`.`id_lang` AS `id_lang`,

                       `a`.`id_attribute` AS `id_attribute`,

                       `a`.`id_attribute_group` AS `id_attribute_group`,

                       `ag`.`position` AS `group_position`,

                       `a`.`position` AS `attribute_position`,

                       `agl`.`public_name` AS `group_name`,

                       `al`.`name` AS `attribute_name`

                FROM `product_attribute_combination` `pac`

                JOIN `attribute` `a` ON`a`.`id_attribute` = `pac`.`id_attribute`

                JOIN `attribute_lang` `al` ON`al`.`id_attribute` = `pac`.`id_attribute`

                JOIN `product_attribute` `pa` ON`pa`.`id_product_attribute` = `pac`.`id_product_attribute`

                JOIN `attribute_group` `ag` ON`ag`.`id_attribute_group` = `a`.`id_attribute_group`

                JOIN `attribute_group_lang` `agl` ON`agl`.`id_attribute_group` = `a`.`id_attribute_group` AND `agl`.`id_lang` = `al`.`id_lang`

                ) `v`

            GROUP BY `v`.`id_product_attribute`,`v`.`id_lang`) cm ON cm.`id_product_attribute` = pa.`id_product_attribute` and cm.`id_lang` = pl.id_lang              

WHERE pl.id_lang = 1

 

ORDER BY p.id_manufacturer, p.id_product

Edited January 14, 2017 by rvcat (see edit history)

[hakeryk2]

I found out that any of these codes will show products with combinations and products without combinations. In my case there is some products that doesn't have any combination or attribute and I tried to create sql query but I failed. Any help?

 Current code
 

SELECT
            p.id_product,
            s.quantity,
            pa.id_product_attribute,
            CONCAT(pl.name, \' \',GROUP_CONCAT(DISTINCT(pal.name) SEPARATOR ", ")) as name
        FROM ps_product p
        LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
        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_stock_available s ON (p.id_product = s.id_product AND ((s.id_product_attribute = 0 OR s.id_product_attribute is null)
                                                                 or (s.id_product_attribute = pa.id_product_attribute)))
        WHERE pl.id_lang = 1
        AND pal.id_lang = 1
        AND CONCAT(pl.name, \' \',pal.name) LIKE "%PRODUCT_NAME%"
        GROUP BY pa.id_product_attribute
        ORDER BY pl.name

I will be very happy if someone would help me. I am using this code to search for product in ajax query but I just cant get it done when product does not have attribute id or combination.

-------------------------------------------------------------------------------------------------------------

EDIT with solution

 

The main issue in my statement were concat functions Now with this code everything works and I can find any product with or withour attribute

 

 SELECT p.id_product,
                    pl.name,
                    pa.id_product_attribute,
                    p.reference,
                    al.name,
                    s.quantity,
                    CONCAT(pl.name, \' \', CASE WHEN al.name is not null THEN GROUP_CONCAT(DISTINCT(al.name) SEPARATOR ", ") ELSE "" END) as name
            FROM ps_product p
            LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
            LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
            LEFT JOIN ps_product_attribute_combination pac ON (pac.id_product_attribute = pa.id_product_attribute)
            LEFT JOIN ps_attribute_lang al ON (al.id_attribute = pac.id_attribute)
            LEFT JOIN ps_stock_available s ON (p.id_product = s.id_product)
            WHERE pl.id_lang = 1
            AND p.id_shop_default = 1
            AND pl.name LIKE "%PRODUCT NAME%"
            GROUP BY pac.id_product_attribute
            ORDER BY pl.name
            LIMIT 60

Edited April 24, 2017 by hakeryk2 (see edit history)

[hamid-esf]

Hi

 

I have been looking answer for this for some time now. I looked at the first solution by eleazar but there were some issues with that. Like the colors came with all languages and products with no combinations had all categories. Then I tried the last one from Adria but that did not work for me because if a product had more than one attribute in a combination (say red, size 5) they were on two lines. So I tried changing it a bit and came up with this which seems to work for me

SELECT 
	m.name AS 'Manufacturer', 
	p.id_product 'Product ID', 
	pl.name 'Product Name', 
	GROUP_CONCAT(DISTINCT(al.name) SEPARATOR ", ") AS 'Combination', 
	GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ",") AS 'Categories', 
	p.price 'Price', 
	pa.price 'Combination Price', 
	p.id_tax_rules_group 'VAT Group', 
	p.wholesale_price 'Wholesale Price', 
	p.reference 'Reference', 
	p.supplier_reference 'Supplier Reference', 
	p.weight 'Weight', 
	s.quantity 'Quantity', 
	pl.description_short 'Short Description', 
	pl.description 'Description', 
	pl.meta_title 'Meta Title', 
	pl.meta_keywords 'Meta Keywords', 
	pl.meta_description 'Meta Description', 
	pl.link_rewrite 'Link Rewrite', 
	pl.available_now 'Available Now', 
	pl.available_later 'Available Later', 
	p.available_for_order 'Available For Order', 
	p.date_add 'Date Added', 
	p.show_price 'Show Price', 
	p.online_only 'Online Only'
FROM ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product and pl.id_lang=2)
LEFT JOIN ps_manufacturer m ON (p.id_manufacturer = m.id_manufacturer)
LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN ps_category c ON (cp.id_category = c.id_category)
LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category and cl.id_lang=2)
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
LEFT JOIN ps_stock_available s ON (p.id_product = s.id_product and pa.id_product_attribute=s.id_product_attribute)
LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)
LEFT JOIN ps_product_attribute_combination pac ON (pac.id_product_attribute = pa.id_product_attribute)
LEFT JOIN ps_attribute_lang al ON (al.id_attribute = pac.id_attribute and al.id_lang=2)
GROUP BY p.id_product,pac.id_product_attribute
order by p.id_product

The languages must of cource be set apropriate and if there are more than one shop it has to be taken into consideration.

 

EDIT: Forgot to mention that I have Prestashop 1.6.1.1

Hello

 

This is the best query but image url was not included and records numbers are more than products records, I fixed it by removing group by attributes.

please put image urls

 

thanks

[KarZan]

Hi hamid-esf

 

It is understandable my query returns more rows than the number of products since every product attribute results as its own row with the stock of the product with that particular attribute. So if you would have one product with three colors each with is own stock amount my query returns three rows. I left out the image url because I my self had no use of it. And adding the image complicates the query quite a lot especially if attribute images should be noted.

[traktap]

On 9/19/2015 at 8:42 PM, eleazar  said:

Hi guys,

 

the following query includes products with combinations:

SELECT m.name AS manufacturer, p.id_product, pl.name, GROUP_CONCAT(DISTINCT(al.name) SEPARATOR ", ") AS combinations, 
GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ",") AS categories, p.price, pa.price, p.id_tax_rules_group, p.wholesale_price, 
p.reference, p.supplier_reference, p.id_supplier, p.id_manufacturer, p.upc, p.ecotax, p.weight, s.quantity, 
pl.description_short, pl.description, pl.meta_title, pl.meta_keywords, pl.meta_description, pl.link_rewrite, 
pl.available_now, pl.available_later, p.available_for_order, p.date_add, p.show_price, p.online_only, p.condition, 
p.id_shop_default
FROM ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
LEFT JOIN ps_manufacturer m ON (p.id_manufacturer = m.id_manufacturer)
LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category)
LEFT JOIN ps_category c ON (cp.id_category = c.id_category)
LEFT JOIN ps_stock_available s ON (p.id_product = s.id_product)
LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
LEFT JOIN ps_product_attribute_combination pac ON (pac.id_product_attribute = pa.id_product_attribute)
LEFT JOIN ps_attribute_lang al ON (al.id_attribute = pac.id_attribute)
WHERE pl.id_lang = 1
AND cl.id_lang = 1
AND p.id_shop_default = 1
AND c.id_shop_default = 1
GROUP BY pac.id_product_attribute

Maybe you have to change the language id if it's not 1 or the default prefix if it's not ps_.

 

 

Great Help!!! Thanks a Million 

[Azi]

Hi there

 

Im looking for an SQL query that shows

-All active products from supplier x.

 

Would be great if somebody could assist with that. Thanks

[Marcos Pastoriza]

On 19/10/2015 at 2:34 AM, Adria said:

Actually this seems to have done it for me below. Hope it does for you as well.

 

SELECT
    p.id_product,
    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_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
GROUP BY pac.id_product_attribute
 

 

This works fine!

Many thanks

[KarZan]

Hi

 

The problem with this is you do not get products which have no combinations. At least I have products with no combinations as I have products with one or two combinations. My earlier post on the other hand lists those also. But in my post there are somewhat more fields AND the id_lang needs to be edited to proper value.

[onlydeshpande]

On 10/19/2015 at 8:07 PM, Adria said:

I have presta 1.6 & it works like a charm. Maybe this?

 

SELECT p.id_product, 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_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
GROUP BY pac.id_product_attribute

This worked for me for Prestashop 1.6.1.6. Thank you so much!!

[PrestashopUser03]

Hello,

I'm on prestashop 1.6.1.6 and the previous request (

  

SELECT p.id_product, 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_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
GROUP BY pac.id_product_attribute

) words fine on my side but doesn't show the products with no combination at all. Which request am I supposed to use for it to show every product ?

Thanks,

[radek.juthner]

Hi.

Goog job.

Can you make php script of this SQL Query?

R.

[rvcat]

 

21 hours ago, PrestashopUser03 said:

Hello,

I'm on prestashop 1.6.1.6 and the previous request (

  

SELECT p.id_product, 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_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
GROUP BY pac.id_product_attribute

) words fine on my side but doesn't show the products with no combination at all. Which request am I supposed to use for it to show every product ?

Thanks,

normal, it comes from this instruction in the clause where .... AND pal.id_lang =1

 

[PrestashopUser03]

Thank you,

I just tried with  "OR pal.id_lang=1" and by deleting this combination but it doesn't work. I have even less products. So what am I supposed to write in place of and pal.id_lang=1?

 

[karlitomadrid]

Hi everyone! Thanks for the contributions.

If someone can help me please, I need a SQL to export the following values in a csv: Reference, Product name, EAN13, Attribute, Price, Quantity sold for each attribute. Thanks!

[PrestaShark]

Hi guys,

Im not a SQL expert and i need Your help. Need query to update quantities of all combinations where ean13 = xxx. Any hints on that? Help much appreciated (i can donate solution via PayPal too)

[radek.juthner]

@Adria

Hi Adria,

I have to ask you a favor. Could you please extend your SQL query for products without combinations? Thank you very much.

 

SELECT
	p.active,
	p.id_product,
	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)
	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_product_attribute_image pai on(pa.id_product_attribute = pai.id_product_attribute)
    WHERE pl.id_lang = 3
 	AND pal.id_lang = 3
 	GROUP BY pa.reference
 	ORDER BY p.id_product, pac.id_attribute

 

[legacy12]

Would be great if this query is extended with product without combinations.

[PrestashopUser03]

@legacy12 For product without combination, here is a php script :

        $sql='SELECT
            p.id_product,
            pa.id_product_attribute as id_product_attribute,
            pa.reference,
            CONCAT(p.supplier_reference, " - ", pa.supplier_reference) as supplier_reference,
            pa.upc,
            pa.ean13,
            p.price,
            pl.name,
            GROUP_CONCAT(DISTINCT(pal.name) SEPARATOR ", ") as combination,
            pq.quantity, pa.weight, p.width, p.depth, p.height
        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_product_attribute_image pai on(pa.id_product_attribute = pai.id_product_attribute)
        WHERE pl.id_lang = 1
        AND pal.id_lang = 1
        GROUP BY pa.reference

        union

        SELECT
            p.id_product,
            "" as id_product_attribute,
            p.reference,
            p.supplier_reference,
            p.upc,
            p.ean13,
            p.price,
            pl.name,
            "" as combination,
            p.quantity, p.weight, p.width, p.depth, p.height
        FROM ps_product p
        LEFT JOIN ps_stock_available pq ON (p.id_product = pq.id_product)
        LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
        WHERE pl.id_lang = 1
        GROUP BY p.reference

        ORDER BY id_product, id_product_attribute';


            $products=Db::getInstance()->ExecuteS($sql);

 

[radek.juthner]

@PrestashopUser03

 

Thanx. It works 🙂

Edited April 3, 2019 by radek.juthner (see edit history)

[beyonceuk1]

On 4/2/2019 at 2:27 PM, PrestashopUser03 said:

@legacy12 For product without combination, here is a php script :

        $sql='SELECT
            p.id_product,
            pa.id_product_attribute as id_product_attribute,
            pa.reference,
            CONCAT(p.supplier_reference, " - ", pa.supplier_reference) as supplier_reference,
            pa.upc,
            pa.ean13,
            p.price,
            pl.name,
            GROUP_CONCAT(DISTINCT(pal.name) SEPARATOR ", ") as combination,
            pq.quantity, pa.weight, p.width, p.depth, p.height
        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_product_attribute_image pai on(pa.id_product_attribute = pai.id_product_attribute)
        WHERE pl.id_lang = 1
        AND pal.id_lang = 1
        GROUP BY pa.reference

        union

        SELECT
            p.id_product,
            "" as id_product_attribute,
            p.reference,
            p.supplier_reference,
            p.upc,
            p.ean13,
            p.price,
            pl.name,
            "" as combination,
            p.quantity, p.weight, p.width, p.depth, p.height
        FROM ps_product p
        LEFT JOIN ps_stock_available pq ON (p.id_product = pq.id_product)
        LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
        WHERE pl.id_lang = 1
        GROUP BY p.reference

        ORDER BY id_product, id_product_attribute';


            $products=Db::getInstance()->ExecuteS($sql);

 

I needed to change p.quantity in the second section to pq.quantity or it would show zero stock.

[Guest]

Answering my own question many years later. 

This is what I got working today for PS 1.7.6. My objective was to export quantities for all products and all combinations. Used one of the suggestions above as starting point.

SELECT 
	m.name AS 'Manufacturer', 
	p.id_product 'Product ID', 
	pl.name 'Product Name', 
        IFNULL(pa.reference, p.reference)  'Reference',
        IFNULL(pa.upc, p.upc)  'Position',
        IFNULL(pa.ean13, p.ean13)  'EAN13',
        IFNULL(s.quantity, p.quantity)  'Quantity'
FROM ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product and pl.id_lang=1)
LEFT JOIN ps_manufacturer m ON (p.id_manufacturer = m.id_manufacturer)
LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN ps_category c ON (cp.id_category = c.id_category)
LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category and cl.id_lang=1)
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
LEFT JOIN ps_stock_available s ON (p.id_product = s.id_product and pa.id_product_attribute=s.id_product_attribute)
LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)
LEFT JOIN ps_product_attribute_combination pac ON (pac.id_product_attribute = pa.id_product_attribute)
LEFT JOIN ps_attribute_lang al ON (al.id_attribute = pac.id_attribute and al.id_lang=1)
GROUP BY p.id_product,pac.id_product_attribute
order by p.id_product

 

[GoPure Kratom]

Thanks for this!!

I have been trying to do this as well.

The problem obviously is that some products have attributes but some not, so it is hard to make any JOIN's etc work....

[ecentury]

Thanks for the above, I have extended it to show price / quantity / reference / combinations for all products

Good if you want to export your products (with combinations) so you can edit price / quantity and then reimport within prestashop

You might have to change ps_ to your database name if you have changed it on installation

 

SELECT
    m.name AS 'Manufacturer',
    p.id_product 'Product ID',
    pl.name 'Product Name',
    GROUP_CONCAT(DISTINCT(al.name) SEPARATOR ", ") AS Combinations,
        IFNULL(pa.reference, p.reference)  'Reference',
        IFNULL(s.quantity, p.quantity)  'Quantity',
                IFNULL(p.price,'0')  'Main Price',
                IFNULL(pa.price,'0')  'Combination Price'    
FROM ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product and pl.id_lang=1)
LEFT JOIN ps_manufacturer m ON (p.id_manufacturer = m.id_manufacturer)
LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN ps_category c ON (cp.id_category = c.id_category)
LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category and cl.id_lang=1)
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
LEFT JOIN ps_stock_available s ON (p.id_product = s.id_product and pa.id_product_attribute=s.id_product_attribute)
LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)
LEFT JOIN ps_product_attribute_combination pac ON (pac.id_product_attribute = pa.id_product_attribute)
LEFT JOIN ps_attribute_lang al ON (al.id_attribute = pac.id_attribute and al.id_lang=1)
GROUP BY p.id_product,pac.id_product_attribute
order by p.id_product

[Ehsanai]

Hi,

Is it possible to add sale count of every product on this ?

[DoGGoD]

On 6/11/2021 at 11:25 AM, ecentury said:

Thanks for the above, I have extended it to show price / quantity / reference / combinations for all products

Good if you want to export your products (with combinations) so you can edit price / quantity and then reimport within prestashop

You might have to change ps_ to your database name if you have changed it on installation

 

SELECT
    m.name AS 'Manufacturer',
    p.id_product 'Product ID',
    pl.name 'Product Name',
    GROUP_CONCAT(DISTINCT(al.name) SEPARATOR ", ") AS Combinations,
        IFNULL(pa.reference, p.reference)  'Reference',
        IFNULL(s.quantity, p.quantity)  'Quantity',
                IFNULL(p.price,'0')  'Main Price',
                IFNULL(pa.price,'0')  'Combination Price'    
FROM ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product and pl.id_lang=1)
LEFT JOIN ps_manufacturer m ON (p.id_manufacturer = m.id_manufacturer)
LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN ps_category c ON (cp.id_category = c.id_category)
LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category and cl.id_lang=1)
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
LEFT JOIN ps_stock_available s ON (p.id_product = s.id_product and pa.id_product_attribute=s.id_product_attribute)
LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)
LEFT JOIN ps_product_attribute_combination pac ON (pac.id_product_attribute = pa.id_product_attribute)
LEFT JOIN ps_attribute_lang al ON (al.id_attribute = pac.id_attribute and al.id_lang=1)
GROUP BY p.id_product,pac.id_product_attribute
order by p.id_product

Hi,

So though the code is awesome, it does not export product quantities, which do not have the combinations (quantities without the combinations is marked 0). I have resolved it at the moment like this:

presta has the option to use sql query from the product page:

SELECT SQL_CALC_FOUND_ROWS p.`id_product`  AS `id_product`,
 p.`reference`  AS `reference`,
 sa.`price`  AS `price`,
 p.`id_shop_default`  AS `id_shop_default`,
 p.`is_virtual`  AS `is_virtual`,
 pl.`name`  AS `name`,
 pl.`link_rewrite`  AS `link_rewrite`,
 sa.`active`  AS `active`,
 shop.`name`  AS `shopname`,
 image_shop.`id_image`  AS `id_image`,
 cl.`name`  AS `name_category`,
 0 AS `price_final`,
 pd.`nb_downloadable`  AS `nb_downloadable`,
 sav.`quantity`  AS `sav_quantity`,
 IF(sav.`quantity`<=0, 1, 0) AS `badge_danger` 
FROM  `ps_product` p 
 LEFT JOIN `ps_product_lang` pl ON (pl.`id_product` = p.`id_product` AND pl.`id_lang` = 1 AND pl.`id_shop` = 1) 
 LEFT JOIN `ps_stock_available` sav ON (sav.`id_product` = p.`id_product` AND sav.`id_product_attribute` = 0 AND sav.id_shop = 1  AND sav.id_shop_group = 0 ) 
 JOIN `ps_product_shop` sa ON (p.`id_product` = sa.`id_product` AND sa.id_shop = 1) 
 LEFT JOIN `ps_category_lang` cl ON (sa.`id_category_default` = cl.`id_category` AND cl.`id_lang` = 1 AND cl.id_shop = 1) 
 LEFT JOIN `ps_category` c ON (c.`id_category` = cl.`id_category`) 
 LEFT JOIN `ps_shop` shop ON (shop.id_shop = 1) 
 LEFT JOIN `ps_image_shop` image_shop ON (image_shop.`id_product` = p.`id_product` AND image_shop.`cover` = 1 AND image_shop.id_shop = 1) 
 LEFT JOIN `ps_image` i ON (i.`id_image` = image_shop.`id_image`) 
 LEFT JOIN `ps_product_download` pd ON (pd.`id_product` = p.`id_product`) 
WHERE (1 AND state = 1)
 
ORDER BY  `id_product` desc
;

this exports all the product quantities, which does not have combinations. My solution at the moment is to merge two tabels (the one by Go Pure and the one generated by prestashop) with excel IFERROR(VLOOKUP(...) formula, which tries to get quantity information from prestashop table to Go Pure table. Then I can merge two different quantity columns into one, which gives out an actual up to date quantities for all products. Though, you need to be sure that all products have unique reference numbers. 

If there would be possible to merge that data as an sql, it would be awsome.