prestashop: MySQL Query For product features

Question

i need to get product features in prestashop 1.5, but the probleme is that i use a modification tat enable me to put multiple value to one feature per product

example:

compatible memory cards: - Carte Mémoire xD - Memory Stick - Memory Stick PRO - MultimediaCard (MMC) - Carte Secure Digital (SD)

I got this query:

Select fl.name, fvl.value, f.id_feature
From ps_feature_lang fl Left Join
  ps_feature_product fp On fl.id_feature = fp.id_feature And fp.id_product = 291
  Left Join
  ps_feature_value_lang fvl On fp.id_feature_value = fvl.id_feature_value And
    fvl.id_lang = 5 Left Join
  ps_feature f On f.id_feature = fl.id_feature
Where fl.id_feature In (Select ps__fc_categories_features.feature_id
  From ps__fc_categories_features)
Group By fl.name, fvl.value, f.id_feature, fl.id_feature
Order By f.position

but if there is a feature with more than one value for a given product, it will give multiple raws for that feature The best is a query that will concat the values for each feature that have pultiple value

thanx in advence.

Answer 1

You should be able to do this with a small adjustment to your query. Try this:

Select fl.name, group_concat(fvl.value) as values, f.id_feature
From ps_feature_lang fl Left Join
     ps_feature_product fp
     On fl.id_feature = fp.id_feature And fp.id_product = 291 Left Join
     ps_feature_value_lang fvl
     On fp.id_feature_value = fvl.id_feature_value And fvl.id_lang = 5 Left Join
     ps_feature f
     On f.id_feature = fl.id_feature
Where fl.id_feature In (Select ps__fc_categories_features.feature_id
                        From ps__fc_categories_features
                       )
Group By fl.name, fl.id_feature
Order By f.position;

This query removes the fvl.value from the group by and adds group_concat() the select. I also removed the f.id_feature. It seems redundant based on the join conditions.