How to get products with specific features

[Dardani]

Hi to all

 

I want to create a query to get all products with condition of feature

 

Here is my query

SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) AS quantity'.(Combination::isFeatureActive() ? ', IFNULL(product_attribute_shop.id_product_attribute, 0) AS id_product_attribute,
                    product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity' : '').', pl.`description`, pl.`description_short`, pl.`available_now`,
                    pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, image_shop.`id_image` id_image,
                    il.`legend` as legend, m.`name` AS manufacturer_name, cl.`name` AS category_default,
                    DATEDIFF(product_shop.`date_add`, DATE_SUB("'.date('Y-m-d').' 00:00:00",
                    INTERVAL '.(int)$nb_days_new_product.' DAY)) > 0 AS new, product_shop.price AS orderprice
                        FROM `'._DB_PREFIX_.'category_product` cp
                        LEFT JOIN `'._DB_PREFIX_.'product` p
                            ON p.`id_product` = cp.`id_product`
                        '.Shop::addSqlAssociation('product', 'p').
                        (Combination::isFeatureActive() ? ' LEFT JOIN `'._DB_PREFIX_.'product_attribute_shop` product_attribute_shop
                        ON (p.`id_product` = product_attribute_shop.`id_product` AND product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop='.(int)$context->shop->id.')':'').'
                        '.Product::sqlStock('p', 0).'
                        LEFT OUTER JOIN `'._DB_PREFIX_.'feature_product` ff 
                            ON (ff.`id_product` = p.`id_product`)
                        LEFT JOIN `'._DB_PREFIX_.'category_lang` cl
                            ON (product_shop.`id_category_default` = cl.`id_category`
                            AND cl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('cl').')
                        LEFT JOIN `'._DB_PREFIX_.'product_lang` pl
                            ON (p.`id_product` = pl.`id_product`
                            AND pl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('pl').')
                        LEFT JOIN `'._DB_PREFIX_.'image_shop` image_shop
                            ON (image_shop.`id_product` = p.`id_product` AND image_shop.cover=1 AND image_shop.id_shop='.(int)$context->shop->id.')
                        LEFT JOIN `'._DB_PREFIX_.'image_lang` il
                            ON (image_shop.`id_image` = il.`id_image`
                            AND il.`id_lang` = '.(int)$id_lang.')
                        LEFT JOIN `'._DB_PREFIX_.'manufacturer` m
                            ON m.`id_manufacturer` = p.`id_manufacturer`
                        WHERE product_shop.`id_shop` = '.(int)$context->shop->id
                            .($supplier_id  ? ' AND p.`id_supplier` = '. (int)$supplier_id : ' ')
                            .($largeur_id? ' AND ff.`id_feature_value` = '.(int)$largeur_id : ' ')
                            .($hauteur_id ? ' AND ff.`id_feature_value` = '.(int)$hauteur_id : ' ')
                            .($diametre_id ? ' AND ff.`id_feature_value` = '.(int)$diametre_id : ' ')
                            .($charge_id ? ' AND ff.`id_feature_value` = '.(int)$charge_id : ' ')
                            .($vitesse_id ? ' AND ff.`id_feature_value` = '.(int)$vitesse_id : ' ')
                            .($vitesse_id ? ' AND ff.`id_feature_value` = '.(int)$saison_id : ' ').'
                            AND cp.`id_category` = '.(int)$this->id
                            .($active ? ' AND product_shop.`active` = 1' : '')
                            .($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '')
                            .($id_supplier ? ' AND p.id_supplier = '.(int)$id_supplier : '').'
                        GROUP BY ff.`id_product`

Any idea how to solve this problem?