Jump to content

SQL query to get product stock for all combinations


Guest

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by eleazar  (see edit history)
  • Thanks 2
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

  • 4 weeks later...

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

  • Thanks 1
Link to comment
Share on other sites

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
 

  • Like 1
  • Thanks 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

  • Like 2
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 2 weeks later...

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 by KarZan (see edit history)
  • Like 1
Link to comment
Share on other sites

  • 4 weeks later...
  • 2 months later...
  • 1 month later...

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

Link to comment
Share on other sites

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

  • Like 1
Link to comment
Share on other sites

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

  • Like 1
Link to comment
Share on other sites

  • 1 month later...

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!

Link to comment
Share on other sites

  • 3 months later...

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!

  • Like 2
Link to comment
Share on other sites

  • 4 months later...

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 :D 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 by hakeryk2 (see edit history)
  • Like 1
Link to comment
Share on other sites

  • 4 weeks later...

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 by rvcat (see edit history)
Link to comment
Share on other sites

  • 3 months later...

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 by hakeryk2 (see edit history)
Link to comment
Share on other sites

  • 2 weeks later...

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

  • Thanks 1
Link to comment
Share on other sites

  • 2 weeks later...

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 6 months later...
  • 4 months later...
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 :)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!!

Link to comment
Share on other sites

  • 3 months later...

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,

Link to comment
Share on other sites

 

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

 

Link to comment
Share on other sites

  • 4 weeks later...
  • 3 weeks later...

@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

 

Link to comment
Share on other sites

  • 2 weeks later...

@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);

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 8 months later...

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

 

Link to comment
Share on other sites

  • 3 months later...
  • 2 months later...

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

Link to comment
Share on other sites

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

Link to comment
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
×
×
  • Create New...