Jump to content
Wilfredcy

Duplicated list of product list, and how to fix it.

Recommended Posts

As you can see the file below, the product info is duplicated a few times.

 Here is my SQL query.

SELECT 

a.`id_product`,
b.`name`,
a.`reference`AS `main_sku`,
pa.`reference` AS `sku`,
b.`link_rewrite` AS `url`,
a.`price` AS `price`,
sav.`quantity`,
m.`name` AS `Manufacturer`

FROM `ps_product` a 
LEFT JOIN `ps_product_lang` b ON (b.`id_product` = a.`id_product`) 
LEFT JOIN `ps_stock_available` sav ON (sav.`id_product` = a.`id_product`) 
LEFT JOIN `ps_manufacturer` m ON (a.id_manufacturer = m.id_manufacturer)
LEFT JOIN  `ps_product_attribute` pa ON (pa.`id_product` = a.`id_product`)

WHERE 1 ORDER BY a.`id_product` ASC        

 

duplicate.JPG

Share this post


Link to post
Share on other sites
FROM `ps_product` a 
LEFT JOIN `ps_product_lang` b ON (b.`id_product` = a.`id_product`) 
LEFT JOIN `ps_manufacturer` m ON (a.id_manufacturer = m.id_manufacturer)
LEFT JOIN  `ps_product_attribute` pa ON (pa.`id_product` = a.`id_product`)
LEFT JOIN `ps_stock_available` sav ON ((sav.`id_product` = a.`id_product`) AND (sav.id_product_attribute is null OR pa.id_product_attribute = sav.id_product_attribute)

Try this way (not tested) :)

Edited by Rodrigo B Laurindo (see edit history)

Share this post


Link to post
Share on other sites

Hi, after test with your code, the duplicated products are reduced, but it still has a double product list there.

w1.JPG

Share this post


Link to post
Share on other sites

Update:

The problem is solved, thank for the help Rodrigo B Laurindo.

Here is the final code

SELECT 

a.`id_product`,
b.`name`,

a.`reference`AS `main_sku`,
pa.`reference` AS `sku`,
b.`link_rewrite` AS `url`,
a.`price` AS `price`,
sav.`quantity`,
m.`name` AS `Manufacturer`

FROM `ps_product` a 
LEFT JOIN `ps_product_lang` b ON ((b.`id_product` = a.`id_product`) AND (b.id_lang = "1"))
LEFT JOIN `ps_manufacturer` m ON (a.id_manufacturer = m.id_manufacturer)
LEFT JOIN  `ps_product_attribute` pa ON (pa.`id_product` = a.`id_product`)
LEFT JOIN `ps_stock_available` sav ON ((sav.`id_product` = a.`id_product`) AND (sav.id_product_attribute is null OR pa.id_product_attribute = sav.id_product_attribute))


WHERE 1 ORDER BY a.`id_product` ASC 

 

Share this post


Link to post
Share on other sites

Hi, sorry to bother you again.

 

I just realize that there got another problem, which is the product with no combination cannot show its quantity, how can I fix this?

 

stock.JPG

Share this post


Link to post
Share on other sites

Try this:

SELECT

a.`id_product`,
b.`name`,

a.`reference`AS `main_sku`,
pa.`reference` AS `sku`,
b.`link_rewrite` AS `url`,
a.`price` AS `price`,
sav.`quantity`,
m.`name` AS `Manufacturer`

FROM `ps_product` a 
LEFT JOIN `ps_product_lang` b ON ((b.`id_product` = a.`id_product`) AND (b.id_lang = "1"))
LEFT JOIN `ps_manufacturer` m ON (a.id_manufacturer = m.id_manufacturer)
LEFT JOIN  `ps_product_attribute` pa ON (pa.`id_product` = a.`id_product`)
LEFT JOIN `ps_stock_available` sav ON ((sav.`id_product` = a.`id_product`) AND (coalesce(sav.id_product_attribute,0) = 0 OR pa.id_product_attribute = sav.id_product_attribute))


WHERE 1 ORDER BY a.`id_product` ASC 

 

Share this post


Link to post
Share on other sites

Hi, it show the quantity for the product with no combination, but now the product with the combination is duplicated again.

vc.JPG

Share this post


Link to post
Share on other sites

Of course it is duplicating 🤔

SELECT

a.`id_product`,
b.`name`,

a.`reference`AS `main_sku`,
pa.`reference` AS `sku`,
b.`link_rewrite` AS `url`,
a.`price` AS `price`,
sav.`quantity`,
m.`name` AS `Manufacturer`

FROM `ps_product` a 
LEFT JOIN `ps_product_lang` b ON ((b.`id_product` = a.`id_product`) AND (b.id_lang = "1"))
LEFT JOIN `ps_manufacturer` m ON (a.id_manufacturer = m.id_manufacturer)
LEFT JOIN `ps_product_attribute` pa ON (pa.`id_product` = a.`id_product`)
LEFT JOIN `ps_stock_available` sav ON ((sav.`id_product` = a.`id_product`) AND ((coalesce(sav.id_product_attribute,0) = 0 and (pa.id_product_attribute is null))
OR (pa.id_product_attribute = sav.id_product_attribute)))


WHERE 1 ORDER BY a.`id_product` ASC

 

Share this post


Link to post
Share on other sites

Hi, it work, THANK YOU

The code:

SELECT 

a.`id_product`,
b.`name`,

a.`reference`AS `main_sku`,
pa.`reference` AS `sku`,
b.`link_rewrite` AS `url`,
a.`price` AS `price`,
sav.`quantity`,
m.`name` AS `Manufacturer`

FROM `ps_product` a 
LEFT JOIN `ps_product_lang` b ON ((b.`id_product` = a.`id_product`) AND (b.id_lang = "1"))
LEFT JOIN `ps_manufacturer` m ON (a.id_manufacturer = m.id_manufacturer)
LEFT JOIN  `ps_product_attribute` pa ON (pa.`id_product` = a.`id_product`)
LEFT JOIN `ps_stock_available` sav ON ((sav.`id_product` = a.`id_product`) AND (coalesce(sav.id_product_attribute,0) = 0 OR (pa.id_product_attribute is null)) OR (pa.id_product_attribute = sav.id_product_attribute))

WHERE 1 ORDER BY a.`id_product` ASC   

 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

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