Easy way to access custom feature

[Mirm]

Hi there.

I was wondering if there is an easy way to access self-defined product properties, since it seems to require querying over multiple tables, to get the name aswell as the value?

 

1.) SELECT `id_feature` FROM  `ps_feature_product` WHERE `id_product` = $id_rpoduct

2.) SELECT `name` FROM `ps_feature_lang` WHERE `id_feature` = (1st step) AND `id_lang` = $id_lang

3.) SELECT `value` FROM `ps_feature_value_lang` WHERE `id_feature` = (1st step) AND `id_lang` = $id_lang

 

Seems a bit tideous. Isn't there a simpler way?

 

Regards,

Mirm

Edited November 28, 2013 by Mirm (see edit history)

[vekia]

why not to create one query with JOINS ?

 

something like:

SELECT * FROM ps_feature_product AS fp 
LEFT JOIN ps_feature_lang fl ON fp.id_feature = fl.id_feature

LEFT JOIN ps_feature_value_lang fvl on fp.id_feature = fvl.id_feature

Where fl.id_lang= $id_lang

[Mirm]

I have lousy SQL-skills but at the end of the day, I would have shortened it ofcourse. But I was wondering if there isn't something already setup in PS's Feature or Product classes. Have been playing around with Product::getFeatureStatic($id_product) but for some reason, it won't return all features.

 

I will use your suggested join, so thanks for the help, but will keep the thread as unsolved for a little longer to see if someone else might have to say something with regards to Product or Feature classes.

[Mirm]

Noone?

Well anyways, to solve this but have chosen against a tripple JOIN on the product tables because I am, as stated before, a lousy SQL programmer/developer. But in case someone is in need of a function to get the value of a custom feature of a product:

 

The code is far from perfect so improvments are very welcome!

 /**
     * Returns value of a custom feature of a given product
     *
     * @param type $id_product
     * @param type $featureName
     * @return string $featureValue
     */
    private function getProductFeatureValue($id_product, $featureName) {

        $featureValue = null;

        $queryFP = "SELECT `id_feature` FROM  `" . _DB_PREFIX_ . "feature_product` WHERE `id_product` = " . $id_product;
        $resultFP = Db::getInstance()->ExecuteS($queryFP);

        foreach ($resultFP as $fp) {
            $queryFL = "SELECT `name` FROM `" . _DB_PREFIX_ . "feature_lang` WHERE `id_feature` = " . $fp["id_feature"] . " AND `id_lang` = " . $this->id_lang;
            $resultFL = DB::getInstance()->executeS($queryFL);
            foreach ($resultFL as $fl) {
                if (strtolower($fl["name"]) == strtolower($featureName)) {
                    $queryFV = "SELECT `id_feature_value` FROM `" . _DB_PREFIX_ . "feature_value` WHERE `id_feature` = " . $fp["id_feature"];
                    $resultFV = DB::getInstance()->executeS($queryFV);
                    $resultFV = $resultFV[0];
                    $queryFVL = "SELECT `value` FROM `" . _DB_PREFIX_ . "feature_value_lang` WHERE `id_feature_value` = " . $resultFV["id_feature_value"] . " AND `id_lang` = " . $this->id_lang;
                    $resultFVL = DB::getInstance()->executeS($queryFVL);
                    $resultFVL = $resultFLV[0];
                    $featureValue = $resultFVL["value"];
                }
            }
        }
        return $featureValue;
    }

Regards.

 

PS: Where can I set the topic [sOLVED] ?

Edited November 29, 2013 by Mirm (see edit history)

[vekia]

still wondering why you don't want to use one query with joins, i posted full code above

query with joins is much faster than separate queries.

 

here is the information about marking thread as [solved]

 

 

[sOLVED] Topic
If, after posting a topic, you find a solution to your problem, please indicate it in your post and describe the solution.
Furthermore if you are the author of the topic for which a solution has been found, please edit your topic title to mark it as [sOLVED].

To mark a topic as [solved] :
- Edit the first post of your topic by clicking on the "Edit" button,
- Click on the "Use full editor" button,
- Add the "[solved]" string at the beginning of your topic title and click on the "Submit Modified Post" button.