Duplicate feature's customized value in FO FacetedSearch bar

[hibatallahAouadni]

Describe the bug

If I created two products with the same feature's customized value, in FO>FacetedSearch bar I get the feature's Customized value duplicated. And if I check one, the other will be check too.

Expected behavior

FO>FacetedSearch bar, there's one feature's customized value.

Steps to Reproduce

Steps to reproduce the behavior:

1. BO>SELL>Catalog>Products
2. Create a product with a feature "Composition" (for exemple) and put Customized value, then Save
3. Create another product with same feature (Composition) and same Customized value, then Save
4. BO>IMPROVE>Modules>Module Manager
5. Click on Configure button of FacetedSearch module
6. Click on Edit button of Template filter
7. Check the Feature:Composition option
8. Save
9. FO> see error

Screenshots

Additional information

• PrestaShop version: 1.7.7.0
• PHP version: 7.2
• FacetedSearch version: 3.7.0

---

Originally posted in this comment

[marionf]

@hibatallahAouadni Is it a regression of the last version of ps_facetedsearch or a regression of the 1.7.7.0 ?

[hibatallahAouadni]

@marionf it's a regression of ps_facetedsearch (3.7.0) cause this option (display the Customized value of feature) wasn't available in the old version (check this #20036)

[wzzly]

Is this still not fixed? Have the same problem.

[ghost]

Hello,

For me this is not a regression, but an improvement.
Before we couldn't find these products with custom feature via the facet search.
The merchant has created these two values in any case.
PrestaShop does the work, the error comes from the merchant.

I propose :

An alert in the BO.
A button to delete and replace the custom value.

[kratekk]

I have the same problem with version module 3.8.0 and Prestashop 1.7.8.5. Did someone find the solution to resolve this problem?

[Hlavtox]

Find all duplicate values of one feature:

$results = Db::getInstance()->ExecuteS("SELECT * FROM (
		SELECT a.id_lang, a.id_feature, a.value, COUNT(*) as row_count FROM (
		SELECT fvl.id_lang, fvl.value, fv.id_feature, CONCAT(fvl.id_lang, '###', fvl.value, '###', fv.id_feature) as unique_key 
		FROM " . _DB_PREFIX_ . "feature_value_lang fvl
		LEFT JOIN " . _DB_PREFIX_ . "feature_value fv ON fvl.id_feature_value = fv.id_feature_value
		LEFT JOIN " . _DB_PREFIX_ . "feature_lang fl ON fv.id_feature = fl.id_feature AND fl.id_lang = fvl.id_lang
		) a
		GROUP BY unique_key) b
		WHERE b.row_count > 1;");
		if (!empty($results)) {
			foreach($results as $row) {
				$this->repairDuplicateFeatures($row['id_feature'], $row['value'], $row['id_lang']);
			}
		}

Repair it:

public function repairDuplicateFeatures($id_feature, $value, $id_lang) {

		$result = Db::getInstance()->ExecuteS('SELECT fvl.id_feature_value, fvl.value, fv.custom
		FROM ' . _DB_PREFIX_ . 'feature_value_lang fvl
		LEFT JOIN ' . _DB_PREFIX_ . 'feature_value fv ON fvl.id_feature_value = fv.id_feature_value
		WHERE fvl.value = "' . $value . '" AND fvl.id_lang = ' . $id_lang . ' AND fv.id_feature = ' . $id_feature . ' ORDER BY fvl.id_feature_value ASC;');

		// Find first noncustom feature, which we will use
		$new_feature_id_to_use = (int) $this->resolveFinalFeatureToUse($result);
		
		// Make a list of duplicate features we will delete
		$old_features_to_delete = [];
		foreach ($result as $k => $row) {
			if ($row['id_feature_value'] != $new_feature_id_to_use) {
				$old_features_to_delete[] = (int) $row['id_feature_value'];
			}
		}

		// Make sure the new one is non-custom
		Db::getInstance()->Execute('UPDATE ' . _DB_PREFIX_ . 'feature_value SET custom = 0 WHERE id_feature_value = ' . (int) $new_feature_id_to_use . ';');

		// Find products that used old feature and insert new one
		$products_using_old_features = Db::getInstance()->ExecuteS('SELECT fp.id_product FROM ' . _DB_PREFIX_ . 'feature_product fp WHERE fp.id_feature_value IN (' . implode(",", $old_features_to_delete) . ');');
		foreach ($products_using_old_features as $row) {
			Db::getInstance()->Execute('INSERT IGNORE INTO `' . _DB_PREFIX_ . 'feature_product` (`id_feature`, `id_product`, `id_feature_value`, `position`) VALUES (' . $id_feature . ',	' . $row['id_product'] . ',	' . $new_feature_id_to_use . ',	0);');
		}

		// Delete all feature value from all tables
		foreach ($old_features_to_delete as $id) {
			Db::getInstance()->Execute('DELETE FROM ' . _DB_PREFIX_ . 'feature_value WHERE id_feature_value = ' . $id . ';');
			Db::getInstance()->Execute('DELETE FROM ' . _DB_PREFIX_ . 'feature_value_lang WHERE id_feature_value = ' . $id . ';');
			Db::getInstance()->Execute('DELETE FROM ' . _DB_PREFIX_ . 'feature_product WHERE id_feature_value = ' . $id . ';');
		}

	}

	public function resolveFinalFeatureToUse($features) {
		foreach ($features as $k => $row) {
			if ($row['custom'] == 0) {
				return $row['id_feature_value'];
			}
		}
		return $features[0]['id_feature_value'];
	}

[Hlavtox]

What do we do with this problem? We can automatically repair it on product page, we can repair it in faceted search? We can notify the user?

Can the automatic merge bring some trouble? I could not think of any.

I think that the custom=0/1 system of feature values is just making problems, why it doesn't save it as a normal feature visible in the list? It could then offer the autocompletion next time, and we would not have these duplicates.

Ping @okom3pom @jolelievre @kpodemski

[kratekk]

Hi @Hlavtox in my case this queries does not work. I did some verification and all code looks logical. The same in database but after execution all above, when I get-in into product card (in the backend). Custom values are empty but nothing is selected on the list of values (but should be now, right?) I also cleared cache but still the same. Any idea, why? Also found some bug. In my case 'position' column didnt exist for 'feature_product' column but fixed it before.