Describe the bug
when you have available multiple languages in your site or have old special prices for a product, the function getProducts of catalog/model/extension/module/facebook_business.php (line 32) returns multiple repeated products slowing down the creation of the csv
To Reproduce
Steps to reproduce the behavior:
create a special price with an old date or enable a second language in Opencart
run the script
/ CODE /
SELECT p., pd., m.name AS manufacturer_name, ptc.category_name FROM oc_product p
LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id)
LEFT JOIN oc_manufacturer m ON (p.manufacturer_id = m.manufacturer_id)
LEFT JOIN oc_product_special ps ON (p.product_id = ps.product_id)
LEFT JOIN
(SELECT ptc.product_id, ptc.category_id, cd.name AS category_name
FROM (SELECT product_id, MAX(category_id) AS category_id
FROM oc_product_to_category
GROUP BY product_id) AS ptc
LEFT JOIN oc_category_description cd
ON (ptc.category_id = cd.category_id)) ptc
ON (p.product_id = ptc.product_id)
WHERE pd.language_id = '1';
/ END CODE /
you will see that the product with special price is duplicated
Additional context
I suggest correct the script with the following, the number 2 has to be replaced with (int)$this->config->get('config_language_id') :
/ CODE /
SELECT p. , pd., m.name AS manufacturer_name, ptc.category_name
FROM oc_product p
LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id and pd.language_id='2')
LEFT JOIN oc_manufacturer m ON (p.manufacturer_id = m.manufacturer_id)
LEFT JOIN oc_product_special ps ON (p.product_id = ps.product_id and CURDATE() between ps.date_start and ps.date_end)
LEFT JOIN
(SELECT ptc.product_id, ptc.category_id, cd.name AS category_name
FROM (SELECT product_id, MAX(category_id) AS category_id
FROM oc_product_to_category
GROUP BY product_id) AS ptc
LEFT JOIN oc_category_description cd
ON (ptc.category_id = cd.category_id and cd.language_id='2')) ptc
ON (p.product_id = ptc.product_id)
WHERE pd.language_id = '2'
Webstore Url
FBE plugin version v 4.2.1
OpenCart version 3.0.8.8
Describe the bug when you have available multiple languages in your site or have old special prices for a product, the function getProducts of catalog/model/extension/module/facebook_business.php (line 32) returns multiple repeated products slowing down the creation of the csv
To Reproduce Steps to reproduce the behavior:
SELECT p., pd., m.name AS manufacturer_name, ptc.category_name FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_manufacturer m ON (p.manufacturer_id = m.manufacturer_id) LEFT JOIN oc_product_special ps ON (p.product_id = ps.product_id) LEFT JOIN (SELECT ptc.product_id, ptc.category_id, cd.name AS category_name FROM (SELECT product_id, MAX(category_id) AS category_id FROM oc_product_to_category GROUP BY product_id) AS ptc LEFT JOIN oc_category_description cd ON (ptc.category_id = cd.category_id)) ptc ON (p.product_id = ptc.product_id) WHERE pd.language_id = '1';
/ END CODE /
Additional context I suggest correct the script with the following, the number 2 has to be replaced with (int)$this->config->get('config_language_id') :
/ CODE / SELECT p. , pd., m.name AS manufacturer_name, ptc.category_name FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id and pd.language_id='2') LEFT JOIN oc_manufacturer m ON (p.manufacturer_id = m.manufacturer_id) LEFT JOIN oc_product_special ps ON (p.product_id = ps.product_id and CURDATE() between ps.date_start and ps.date_end) LEFT JOIN (SELECT ptc.product_id, ptc.category_id, cd.name AS category_name FROM (SELECT product_id, MAX(category_id) AS category_id FROM oc_product_to_category GROUP BY product_id) AS ptc LEFT JOIN oc_category_description cd ON (ptc.category_id = cd.category_id and cd.language_id='2')) ptc ON (p.product_id = ptc.product_id) WHERE pd.language_id = '2'
/ END CODE /