Howto get all products with feature X

[Tito]

Hi,

How can I query/get all products with feature X / Y / Z ?

Goal : Making product list sortable/filterable with features...

[Matthieu Biart]

Hi Tito!

You can easly get what you want from the SQL table called "YOUR-PREFIX_feature_product" where you'll find the associations between your feature and products.

[Tito]

Can someone pls provide a simple example query ?
Where do I start to make it possible through a .tpl file ?

[Fabrice]

sorry, I put the post on the wrong topic : [i would put them in accessories (rename it "file formats" for instance)]

[Tito]

Can someone with the knowledge pls post an example sql query, demonstrating how I can get
"YOUR-PREFIX_feature_product" data , in an array ?


So I can list fe all products which have a specific feature...

[Tito]

I managed to construct the sql query, but then I found the Feature & FeatureValue classes...

Now,
How can I use the special Feature and FeatureValue classes ?

I don't understand how to use em, especially with multiple given id_productS , fe in product-list.tpl

Can you PLS explain what I need to add in category.php to assign product features array to product-list.tpl ???
OR is it possible to add the features in 'products' array ?

category.php (line 23 -35)

       if ($category->id != 1)
       {
           ...
           $cat_products = $category->getProducts(intval($cookie->id_lang), intval($p), intval($n), $orderBy, $orderWay);
------------>>>>>>>>>>>>>>> $cat_features = ?!?!? <<<<<<<<<<<<<<---------------
       }
       $smarty->assign(array(
           'products' => (isset($cat_products) AND $cat_products) ? $cat_products : NULL,
           ------------>>>>>>>>>>>>>>> 'features' => ?!?!? <<<<<<<<<<<<<<---------------
...
           'path' => Tools::getPath($category->id, $category->name)

[Tito]

So far it's working, BUT it is creating (I assume) much mysql overhead...
Could someone pls post a better solution than mine?

I edited category.php with this : (starting from line 29 until next $smarty->assing)

       if ($category->id != 1)
       {
           $nbProducts = $category->getProducts(NULL, NULL, NULL, $orderBy, $orderWay, true);
           include(dirname(__FILE__).'/pagination.php');
           $smarty->assign('nb_products', $nbProducts);
           $cat_products = $category->getProducts(intval($cookie->id_lang), intval($p), intval($n), $orderBy, $orderWay);
// START EDIT         START EDIT         START EDIT         START EDIT         START EDIT         START EDIT         START EDIT            
     $cat_features = array();
     foreach ($cat_products as $key=>$value)
       {
           /*
         echo $key;
           echo " - ";
         echo $value['id_product'];
           echo "
";
           */

         $temp_id_product = $value['id_product'];
         $temp_featuresArray = array();

       $search_product_id = $temp_id_product;
       $query  = "SELECT fvl.value,fl.name feature, fl.id_feature,pl.name,pl.id_product FROM ps_feature_value_lang fvl,ps_feature_lang fl,ps_feature_product fp,ps_product_lang pl ".
         "WHERE pl.id_product = ".$search_product_id." ".
         "AND pl.id_lang = 1 ".  
         "AND fvl.id_feature_value = fp.id_feature_value ".
         "AND fp.id_product = pl.id_product ".
         "AND pl.id_lang = fvl.id_lang ".
         "AND fl.id_feature = fp.id_feature ".
         "AND fl.id_lang = fvl.id_lang";
       $result = mysql_query($query);

       while($row = mysql_fetch_array($result, MYSQL_ASSOC))
         {
         /*
         echo "";
           print_r($row);
         echo "";
         */
         $temp_featuresArray[$row['feature']] = $row['value'];
         $temp_featuresArray[$row['id_feature']] = $row['value'];
         } 

       $cat_features[$temp_id_product] = $temp_featuresArray;
       }


     echo "";
       print_r($cat_features[10]);
     echo "";
       }
       $smarty->assign(array(
           'products' => (isset($cat_products) AND $cat_products) ? $cat_products : NULL,
           'features' => (isset($cat_features) AND $cat_features) ? $cat_features : NULL,
           'id_category' => intval($category->id),
           'id_category_parent' => intval($category->id_parent),
           'return_category_name' => Tools::safeOutput(Category::hideCategoryPosition($category->name)),
           'path' => Tools::getPath($category->id, $category->name)
       ));

In product-list.tpl is use :

{foreach from=$products item=product name=products}
 {assign var="feature_product_id" value=$product.id_product}
 ...
 {$product.name}
 {$features.$feature_product_id.Website}
...
{/foreach}

But I feel I am not doing it as it is supposed to...

[ejectcore]

Please display a link to your site so I can see what you are trying to do.
There is a lot of unnecessary code here.

In fact it may be better to create a new page called featured.php
When I can understand what you are trying to do.
I will try to provide the correct solution for you

[Tito]

Maybe I should move my question to a new topic, OR I shall try clarify... :
(original topic subject isn't correct, but solution might be the same)

I want to show more detailed information about products in the 'product-list.tpl'
Especially, the product 'Features'.

Now, we can use in 'product-list.tpl' variables like '$product.id_product', '$product.name', '$product.price', etcetera...
BUT, I would need something extra like '$product.feature.featureA.KEY' and '$product.feature.featureA.VALUE'

This way, we could do something like (in product-list.tpl):

if (product.feature.location = "european") then ... for example display extra europe icon...

With the code (draft/test) I posted in previous post, the product array is looped and an extra feature array is created for every product_id...
This works, I now do have the features loaded for every product, BUT it causes overhead.
Since there are Feature and FeatureValue classes included in prestashop, I assume someone with the knowledge could make a solution out of my goal... selecting all features at once, not with multiple selects/querys in loop...

I hope I clarified what I need without a link as I'm working offline until next week...

ps Considering your 'featured.php' sugestion :
If you mean a seperate page like : "show me all products where feature-X eaquals value-Y" or "show me all products that have as feature.location the value 'European' ", then that would be great too! But something else...

[ejectcore]

Ok Tito if your still no further with this by tomorrow night. I will see if I can help
if we come up with the solution then this can be shared with the rest of the community.

It just seems quicker intially to chat on SKYPE
Speak Soon

PS you may be able to use this function
$feature = Category::getFeatures(intval($cookie->id_lang));

   /**
   * Select all features for the object
   *
   * @return array Array with feature product's data
   */
   public function getFeatures()
   {
       return self::getFeaturesStatic(intval($this->id));
   }

   static public function getFeaturesStatic($id_product)
   {
       return Db::getInstance()->ExecuteS('
       SELECT id_feature, id_product, id_feature_value
       FROM `'._DB_PREFIX_.'feature_product`
       WHERE `id_product` = '.$id_product);
   }

[Tito]

I'm struggeling with the Feature classes...

Using my own mysql select; I can get all features AND their value.

"SELECT fvl.value,fl.name feature, fl.id_feature,pl.name,pl.id_product FROM ps_feature_value_lang fvl,ps_feature_lang fl,ps_feature_product fp,ps_product_lang pl ".
         "WHERE pl.id_product = ".$search_product_id." ".
         "AND pl.id_lang = 1 ".  
         "AND fvl.id_feature_value = fp.id_feature_value ".
         "AND fp.id_product = pl.id_product ".
         "AND pl.id_lang = fvl.id_lang ".
         "AND fl.id_feature = fp.id_feature ".
         "AND fl.id_lang = fvl.id_lang";

But I don't suceed with the Feature and FeatureValue classes...

Right now; in category.php; the $products array is filled with : (line 28)

$cat_products = $category->getProducts(intval($cookie->id_lang), intval($p), intval($n), $orderBy, $orderWay);

How can I easily get all the features, for every product; in fe $cat_features ?
Now I get lost in the different steps to get the correct feature+value; in the correct language.
First I need to get the feature-id, then the language, then the feature name, then value, etc... all different selects/getFeatureVariants....

So, Can you help me to figure out the correct procedure to get the features from multiple products; to use in the product-list

I think a seperate Select/Query is best solution to overcome extra overhead.
My solution still needs a select for every product
Though the best solution would be if there was a special selector to retrieve all features for Multiple products => 1 select query, no different getFeatures id's ; then language; then etc...

something like : $cat_features = $category->getFeaturesFromTheseProducts(array(id1,id2,id3,...),intval($cookie->id_lang))

ps : Where did you find this ??

PS you may be able to use this function
$feature = Category::getFeatures(intval($cookie->id_lang));

[ejectcore]

Located in the category class.
just defined the feature myself

The best method would be to use one of the existing functions, or creating a new function
not got a lot of time until later in the week, but can chat on SKYPE tonight to help you solve this issue

[Tito]

Thx for your support so far 'amwdesign' !
Though I don't have a mic with me now

But; I can't find the Category::getFeatures() class you mentioned; is it in root/classes/Category.php ? Which linenrs ?

[ejectcore]

I do apologise it's in the product class - Product::getFeatures
You don't need a mic download SKYPE & just chat

[Tito]

AAArrrrgghhhhh

After hours and hours going away further and further from a solution... going through all classes... making my own class, a simple print_r($cat_products) showed that features ALREADY are retrieved ...

Thus in template :

   {if $product.features}
       {foreach from=$product.features item=feature}
{$feature.name|escape:'htmlall':'UTF-8'}{l s=':'} {$feature.value|escape:'htmlall':'UTF-8'}
       {/foreach}
   {/if}

voila

Though; the original topic subject; filterable list through given features, isn't done.
Something I still need and will try to achieve in the comming days...

[dragosh_F]

AAArrrrgghhhhh

Though; the original topic subject; filterable list through given features, isn't done.
Something I still need and will try to achieve in the comming days...

Hi. Did you or anyone else manage to do this?
I will do it myself if necessary, but don't want do it if it's allready done.

[eGzyl.pl]

                $Products = Product::getProducts(Language::getIdByIso('pl'), 0, NULL, 'name', 'ASC', false, true);
               $id_product = 2;
               $feature = Category::getFeatures(intval($cookie->id_lang));
               $c = self::generateCategoryPath();
          echo'';
          //print_r ($Products[0]);
          echo'';

what i do bad ??

error:

Fatal error: Call to undefined method Category::getFeatures() in /usr/local/apache/www/htdocs/galanteria-lowiecka.pl/modules/integrator/integrator.php on line 65

[eGzyl.pl]

resolve:

                $Products = Product::getProducts(Language::getIdByIso('pl'), 0, NULL, 'name', 'ASC', false, true);
               $feature = Product::getFrontFeaturesStatic(3, $id_produc);
               $c = self::generateCategoryPath();
               echo '############## 
';
               foreach($Products as $p) {
               echo $p['id_product'].'-->'.$p['name'].'
';
               $feature = Product::getFrontFeaturesStatic(3, $p['id_product']);
       foreach($feature as $f) {
       echo $f['name'];
       echo $f['value'];
       }
               }