CODEWITHSUNDEEP
prestashop
abuduba
abuduba

Reputation: 5042

How to get in prestashop filtered products by its attribute

I have no experience in prestashop. I'm trying to get all products which matches to some value of its attribute.

[edit]

Now I get all products by using Product::getProducts. After that I filter result array by array_filter. I'm wondering about method to retrieve that filtered products directly, where filtering is doing in database. Eg. passing a function or string filter like Product::getProductsBy( 'product_id > 10')

Upvotes: 2

Views: 16576

Answers (2)

Paul Campbell
Paul Campbell

Reputation: 1145

The short answer is that no there isn't anything built in that can do this, however, IF I'm understanding correctly, then you want a list of products that have a particular attribute value in a suitable format to output using the product listing theme template.

I'll assume that you start from a drop-down list of attribute values within a group and as such we can ignore attribute grouping completely. For example you may have an attribute group "Size" (id_attribute_group = 1) with selectable values "M" and "L", these latter corresponding to entries in the XX_attribute table.

In the XX_attribute table you would have

id_attribute | id_attribute_group | color
1            | 1                  | #000000
2            | 1                  | #000000

In the XX_attribute_lang table you would have

id_attribute | id_lang            | name
1            | 1                  | M
2            | 1                  | L

Assuming that you don't care about categories etc. - to get all the products available in size "L" you could then just use a simple query such as:

SELECT pa.`id_product` FROM `XX_product_attribute` pa
LEFT JOIN `XX_product_attribute_combination` pac ON pa.`id_product_attribute` = pac.`id_product_attribute`
WHERE pac.`id_attribute` = '2'

You then have to bascially replicate the functionality used internally by Prestashop to retrieve the product details for an array of product_ids obtained above to pass to the product listing template. Getting only the product ids at this stage is probably the best approach since you need to post-process the product data anyway for language, currency, tax etc.

To get the detailed product data from an array of ids built from the above or a similar filtering query (e.g. stored in an array called $product_ids) you would have to do something like:

global $cookie;
$id_lang = $cookie->id_lang;
$product_list = array();

if (count($product_ids))
{
  $sql = '
  SELECT p.*, pa.`id_product_attribute`, 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`, i.`id_image`, il.`legend`, m.`name` AS manufacturer_name, tl.`name` AS tax_name, t.`rate`, cl.`name` AS category_default, DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL '.(Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20).' DAY)) > 0 AS new,
    (p.`price` * IF(t.`rate`,((100 + (t.`rate`))/100),1)) AS orderprice
  FROM `'._DB_PREFIX_.'category_product` cp
  LEFT JOIN `'._DB_PREFIX_.'product` p ON p.`id_product` = cp.`id_product`
  LEFT JOIN `'._DB_PREFIX_.'product_attribute` pa ON (p.`id_product` = pa.`id_product` AND default_on = 1)
  LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND cl.`id_lang` = '.(int)($id_lang).')
  LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = '.(int)($id_lang).')
  LEFT JOIN `'._DB_PREFIX_.'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
  LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = '.(int)($id_lang).')
  LEFT JOIN `'._DB_PREFIX_.'tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group`
                                             AND tr.`id_country` = '.(int)Country::getDefaultCountryId().'
                                               AND tr.`id_state` = 0)
    LEFT JOIN `'._DB_PREFIX_.'tax` t ON (t.`id_tax` = tr.`id_tax`)
  LEFT JOIN `'._DB_PREFIX_.'tax_lang` tl ON (t.`id_tax` = tl.`id_tax` AND tl.`id_lang` = '.(int)($id_lang).')
  LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
  WHERE p.`active` = 1' : '').'
        '.($id_supplier ? 'AND p.id_supplier = '.(int)($id_supplier) : '').'
  AND p.`id_product` IN(' . implode(',', $product_ids) . ')';

  $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS($sql);


  /* Modify SQL result */
  $product_list = Product::getProductsProperties($id_lang, $result);
}

Obviously I've used IN() to filter the results to our product set, which for a large number of products is going to get tricky, but it gives the general idea! Another issue is that the query above is pretty much lifted out of a similar function elsewhere in Prestashop (apologies if I've mis-edited it anywhere!) and as such may break on upgrade....

Upvotes: 7

Mike Kranzler
Mike Kranzler

Reputation: 192

As Paul mentioned above, your question is extremely vague and may be difficult to properly answer without additional information. However, I will try to offer a couple potential solutions:

If you're looking to purchase a specific product, such as an iPod, with options for color and size, you can do this using the Attributes & Groups tab and the Combinations Generator in your Catalog.

If you're looking to narrow down a list of products based on parameters such as price, features, color, etc. (think of how you shop on Amazon.com, for example), you can configure this using the built-in Layered Navigation module.

If neither of these address your question, please post additional information to help us better understand what you are asking.

Source: I am PrestaShop's Community Manager

Upvotes: 0

Related Questions