database - Prestashop module SQL Query to get id_products from a specific feature value

Question

I'm currently working with a module called "Leo Parts Filter" on prestashop, which sort id_products by make, model, year and device in a database "ps_leopartsfilter_product".

When i call a make, model, year a device that have an id_product in this table, prestashop show this id_product.

 public static $definition = array(
        'table' => 'leopartsfilter_product',
        'primary' => 'id_product',
        'multilang' => false,
    $sql = 'SELECT * FROM `' . _DB_PREFIX_ . 'leopartsfilter_product` WHERE 1=1 ';  
            
             if ($id_product != null && (int) $id_product) {
                $sql .= ' AND id_product =' . (int) id_product;
            }

I would like to change the way id_product is called, instead of calling only this id_product according this way :

I would like to query all products where the feature_id = 212 and where the feature_value_lang = id_product.

I tried to adapt this query but i can't make it work correctly (no data showing)

SELECT id_product, fl.name, value, pf.id_feature
                FROM '._DB_PREFIX_.'feature_product pf
                LEFT JOIN '._DB_PREFIX_.'feature_lang fl ON (fl.id_feature =212 AND fl.id_lang = '.(int)$context->language->id.')
                LEFT JOIN '._DB_PREFIX_.'feature_value_lang fvl ON (fvl.id_feature_value = pf.id_feature_value AND fvl.id_lang = '.(int)$context->language->id.')
                LEFT JOIN '._DB_PREFIX_.'feature f ON (f.id_feature = pf.id_feature)
                '.Shop::addSqlAssociation('feature', 'f').'
    
    WHERE fvl.`value` =  '['id_product']'
    GROUP BY id_product
    ORDER BY f.position ASC
    ';

I'm not good at SQL so don't blame me, i learned everything by my way and i try to build my own company website, this module is all i need to finish.

If someone want some money for help, i'm open to give rewards to make it work.

Thanks

question from:https://stackoverflow.com/questions/65926799/prestashop-module-sql-query-to-get-id-products-from-a-specific-feature-value

Answer 1

Your second query has nothing to do with the Leo Parts Filter module, since it only queries native Prestashop feature tables and not those of the Leo module itself, so your request is not very clear.

If you need to rely on basic features, it must be clear to you that the relationship between id features / id feature values and id products is all in the ps_feature_product table, so if you want products that have id_feature_value = 212 you just need a simple

SELECT id_product FROM ps_feature_product WHERE id_feature_value = 212;