搜索特定类别的产品 [英] Search products in a specific category
问题描述
我正在尝试扩展Prestashop 1.6.0.6中默认模块 blocksearch 提供的搜索机制的默认行为。
I am trying to extend the default behaviour of the search mechanism provided by the default module blocksearch in Prestashop 1.6.0.6.
我在默认表单中添加了一个 select 输入返回类别。
I added to the default form a select input returning categories.
这两种方法都可以正常工作控制器和视图侧。整个表单会将正确的参数发送到 SearchController 。但是，在Model中，我仍然不知道要对 Search.php 实体应用哪些更改，以便方法查找返回属于指定类别的产品。
我花了很多时间试图了解查找中的搜索机制是如何工作的，但是没有用。有人可以给我指出代码的有关部分吗？在SQL中 category_id 上添加我的条件的地方？
Everything is working in both controller and view sides. The whole form is sending the correct parameter to the SearchController. But, in Model, I still don't know what are the changes to apply on Search.php entity so that the method find returns products that belong to the specified category.
I spent a good time trying to understand how the search mechanism in find works but to no avail. Can someone please point me the concerned part of code? where to add my conditions on category_id in SQL?
编辑：
在 Search :: find 方法中的所有sql查询中，我认为下面的一个有关。实际上，我添加了 AND p.’id_manufacturer’=’。（int）$ manufacturer_id。’，然后可以看到更改。但是对于类别，它似乎更加复杂，我认为我需要在某处添加 JOIN 。恐怕我构建的东西似乎可以正常工作，但是在创建过程中却没有遵循最佳实践。请帮助我，我是sql的新手。
Among all the sql queries in Search::find method, I think the following one is the concerned. In fact, I added AND p.'id_manufacturer' ='.(int)$manufacturer_id.' and I can see changes. But for categories, it seems more complicated, I think I need a JOIN somewhere. I am afraid I build things that can apparently working but are created with no respect to best practices. Please to help me I am a newbie in sql.
$sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity,
pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`name`,
MAX(image_shop.`id_image`) id_image, il.`legend`, m.`name` manufacturer_name '.$score.', MAX(product_attribute_shop.`id_product_attribute`) id_product_attribute,
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 new
FROM '._DB_PREFIX_.'product p
'.Shop::addSqlAssociation('product', 'p').'
INNER 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_.'product_attribute` pa ON (p.`id_product` = pa.`id_product`)
'.Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.`default_on` = 1').'
'.Product::sqlStock('p', 'product_attribute_shop', false, $context->shop).'
LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
LEFT JOIN `'._DB_PREFIX_.'image` i ON (i.`id_product` = p.`id_product`)'.
Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1').'
LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = '.(int)$id_lang.')
WHERE p.`id_product` '.$product_pool.'
AND p.`id_manufacturer` ='.(int)$manufacturer_id.'
GROUP BY product_shop.id_product
'.($order_by ? 'ORDER BY '.$alias.$order_by : '').($order_way ? ' '.$order_way : '').'
LIMIT '.(int)(($page_number - 1) * $page_size).','.(int)$page_size;
编辑numero 2：临时解决方案
if($category_id!=0) $sql=$sql.' AND p.`id_product` IN (SELECT `id_product` FROM `ps_category_product` p WHERE p.`id_category`='.(int)$category_id.' )';
推荐答案
您可以尝试
$sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity,
pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`name`,
MAX(image_shop.`id_image`) id_image, il.`legend`, m.`name` manufacturer_name '.$score.', MAX(product_attribute_shop.`id_product_attribute`) id_product_attribute,
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 new
FROM `'._DB_PREFIX_.'category_product` cp
LEFT JOIN `'._DB_PREFIX_.'product` p
ON p.`id_product` = cp.`id_product`
'.Shop::addSqlAssociation('product', 'p').'
INNER 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_.'product_attribute` pa ON (p.`id_product` = pa.`id_product`)
'.Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.`default_on` = 1').'
'.Product::sqlStock('p', 'product_attribute_shop', false, $context->shop).'
LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
LEFT JOIN `'._DB_PREFIX_.'image` i ON (i.`id_product` = p.`id_product`)'.
Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1').'
LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = '.(int)$id_lang.')
WHERE p.`id_product` '.$product_pool.'
AND p.`id_manufacturer` ='.(int)$manufacturer_id.' AND cp.id_lang ='.(int)$id_category.'
GROUP BY product_shop.id_product
'.($order_by ? 'ORDER BY '.$alias.$order_by : '').($order_way ? ' '.$order_way : '').'
LIMIT '.(int)(($page_number - 1) * $page_size).','.(int)$page_size;
其中（int）$ id_category是id_category（来自select），category_product是存储以下内容的表产品与类别之间的关系
where (int)$id_category is the id_category (from the select) and category_product is the table storing the relation between products and categories
希望这会有所帮助。
这篇关于搜索特定类别的产品的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
