Skip to content

Instantly share code, notes, and snippets.

@stratboy
Last active February 29, 2024 20:14
Show Gist options
  • Save stratboy/1e53851afa7cf43333c072b639ac68e4 to your computer and use it in GitHub Desktop.
Save stratboy/1e53851afa7cf43333c072b639ac68e4 to your computer and use it in GitHub Desktop.
A query to extract products and their features from prestashop, with features in columns
select
subquery.id_product,
subquery.reference,
subquery.product_name,
Max(CASE WHEN subquery.feature_name = 'Feature 1' THEN subquery.feature_value END) AS `Feature 1`,
Max(CASE WHEN subquery.feature_name = 'Feature 2' THEN subquery.feature_value END) AS `Feature 2`,
Max(CASE WHEN subquery.feature_name = 'Feature 3' THEN subquery.feature_value END) AS `Feature 3`
from (
select
fp.id_product as id_product,
p.reference as reference,
pl.name as product_name,
fl.name as feature_name,
fvl.value as feature_value
from
ps_feature_product fp
left join ps_feature_lang fl on (fl.id_feature = fp.id_feature)
left join ps_feature_value fv on (fv.id_feature_value = fp.id_feature_value)
left join ps_feature_value_lang fvl on (fvl.id_feature_value = fv.id_feature_value and fvl.id_lang = 1)
left join ps_product p on (fp.id_product = p.id_product)
left join ps_product_lang pl on (pl.id_product = p.id_product and pl.id_lang = 1 and pl.id_shop = 1)
where fl.id_lang = 1
order by fp.id_product asc
) as subquery
group by subquery.reference
order by subquery.id_product asc
@stratboy
Copy link
Author

stratboy commented Feb 29, 2024

In the subquery it extracts products and features the usual way, with 1 row per feature. Then on the main select, the CASE statements in fact move features in columns. Add as much CASE statements as you want. Works for known columns.

In this gist the query extracts only one language.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment