SQL query to list all features names, values and IDs

[lkomarci]

Hi everyone,

Does anyone know what an SQL query would look like to list all Product ID + product name + Feature ID + Feature name + Feature value ? I'm looking to achieve that within Advanced settings/Database

 

I have some duplicates amongst a few hundred features and would like to find out what products they're assigned to so I can remove them. I'm actually looking for the same thing for attributes as well if it's possible.

 

Thanks,

Luka

[Andrei H]

Hello,

By default you are not allowed to have duplicate values for Features (eg. If you have a Composition Feature with the Cotton value assigned to a product, next time you try to add another Composition Value with the Cotton value for the same product, you get the following error: 'You cannot associate the same feature value more than once.')

When you say duplicates, are you referring to the fact that you have a Feature like Composition but with multiple values (Cotton, Ceramic, Wool etc.) assigned to a product?

If the answer to the above is yes, this query will list all of them (if you have multiple languages and want to see the values for these as well, remove the id_lang check or change it accordingly):

SELECT DISTINCT PP.`id_product`, PPL.`name` AS 'product_name', PF.`id_feature`, PFL.`name` AS 'feature_name', PFVL.`value` AS 'feature_value'
FROM `ps_product` PP
INNER JOIN `ps_product_lang` PPL ON PP.`id_product` = PPL.`id_product`
INNER JOIN `ps_feature_product` PFP ON PP.`id_product` = PFP.`id_product`
INNER JOIN `ps_feature` PF on PFP.`id_feature` = PF.`id_feature`
INNER JOIN `ps_feature_lang` PFL ON PF.`id_feature` = PFL.`id_feature`
INNER JOIN `ps_feature_value` PFV ON PFP.`id_feature_value` = PFV.`id_feature_value`
INNER JOIN `ps_feature_value_lang` PFVL ON PFV.`id_feature_value` = PFVL.`id_feature_value`
WHERE PP.`product_type` = 'combinations' AND `PFVL`.`id_lang` = '1'
ORDER BY PP.`id_product`;

 

Edited March 4, 2024 by Andrei H (see edit history)

[lkomarci]

Hi Andrei,

 

Thanks for the reply, in any case I'll give your query a try and see what it'll do.

In my specific case I have a feature "Color temperature" and during and import a feature named "Color temerature (Kelvin)" snuck in.

Each of them contain feature values 2700 Kelvin, 3000 Kelvin etc. So essentially when I check the affected product's features on the web it has the same value written two times. The problem is I don't know how many products have been affected.

[Andrei H]

Hello,

The query should list both the 'Color temperature' and 'Color temperature (Kelvin)' features if a product has them assigned.

Please let me know if it lists the expected data or if I should modify it in any way.

[lkomarci]

Yes this is good Andrei, it'll work for what I need. Thank you very much for your time, I really appreciate that!

[lkomarci]

Out of curiosity, is it possible to do the same with attributes but also including the attribute value id?

[Andrei H]

Hello,

In theory, it should be possible.

I'll have a look and get back to you once I have a solution.

[Andrei H]

Hello,

Can you try the following query and let me know if it is the one you were looking for or if it needs any changes, please?

SELECT DISTINCT PP.`id_product`, PPL.`name` AS 'product_name', PAL.`id_attribute`, PAL.`name` AS 'attribute_name', PAGL.`name` as 'attrbute_group_name'
FROM `ps_product` PP
INNER JOIN `ps_product_lang` PPL ON PP.`id_product` = PPL.`id_product`
INNER JOIN `ps_product_attribute` PPA ON PPL.`id_product` = PPA.`id_product`
INNER JOIN `ps_product_attribute_combination` PPAC ON PPA.`id_product_attribute` = PPAC.`id_product_attribute`
INNER JOIN `ps_attribute` PA ON PPAC.`id_attribute` = PA.`id_attribute`
INNER JOIN `ps_attribute_lang` PAL ON PA.`id_attribute` = PAL.`id_attribute` 
INNER JOIN `ps_attribute_group` PAG ON PA.`id_attribute_group` = PAG.`id_attribute_group`
INNER JOIN `ps_attribute_group_lang` PAGL ON PAG.`id_attribute_group` = PAGL.`id_attribute_group`
WHERE PP.`product_type` = 'combinations' AND PPL.`id_lang` = '1' AND PAL.`id_lang` = '1' AND PAGL.`id_lang` = '1'
ORDER BY PP.`id_product`;

This will list the Product ID, Product Name, Attribute ID, Attribute Name and the Attribute Group Name

[lkomarci]

1 minute ago, Andrei H said:

Hello,

Can you try the following query and let me know if it is the one you were looking for or if it needs any changes, please?

SELECT DISTINCT PP.`id_product`, PPL.`name` AS 'product_name', PAL.`id_attribute`, PAL.`name` AS 'attribute_name', PAGL.`name` as 'attrbute_group_name'
FROM `ps_product` PP
INNER JOIN `ps_product_lang` PPL ON PP.`id_product` = PPL.`id_product`
INNER JOIN `ps_product_attribute` PPA ON PPL.`id_product` = PPA.`id_product`
INNER JOIN `ps_product_attribute_combination` PPAC ON PPA.`id_product_attribute` = PPAC.`id_product_attribute`
INNER JOIN `ps_attribute` PA ON PPAC.`id_attribute` = PA.`id_attribute`
INNER JOIN `ps_attribute_lang` PAL ON PA.`id_attribute` = PAL.`id_attribute` 
INNER JOIN `ps_attribute_group` PAG ON PA.`id_attribute_group` = PAG.`id_attribute_group`
INNER JOIN `ps_attribute_group_lang` PAGL ON PAG.`id_attribute_group` = PAGL.`id_attribute_group`
WHERE PP.`product_type` = 'combinations' AND PPL.`id_lang` = '1' AND PAL.`id_lang` = '1' AND PAGL.`id_lang` = '1'
ORDER BY PP.`id_product`;

This will list the Product ID, Product Name, Attribute ID, Attribute Name and the Attribute Group Name

Thanks Andrei! You're golden I'll give it a try shortly and let you know!

[lkomarci]

Just gave it a try, yes this is it! It gave me exactly what I needed.

Thank You again Andrei. You're the best 👌