Opencart 2.2.0允许按制造商搜索 [英] Opencart 2.2.0 enable search by manufacturer
本文介绍了Opencart 2.2.0允许按制造商搜索的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在使用OC 2.2.0，并且一段时间以来一直在解决以下问题:
I am using OC 2.2.0 and been struggling with the following problem for a while now:
示例:我在标题搜索中输入Siemens，然后单击显示所有结果"，显示所有结果的搜索页面.问题是-结果列表仅包括名称中包含Siemens的产品.我需要在搜索结果列表中显示属于该制造商的所有 产品，在我们的示例中为西门子制造商.在我的search.php控制器文件中，结果在以下行中定义:
Example: I enter Siemens in header search and click SHOW ALL RESULTS, my search page appears with all results. The problem is - results list includes only products that have Siemens in their NAME. What I need is to show all products in search results list, belonging to that manufacturer, which in our example is Siemens manufacturer. In my search.php controller file, results are defined in this line:
$results = $this->model_catalog_product->getProducts($filter_data);
这向我展示了model-catalog-product中product.php文件的getProducts($filter_data)函数是我需要定义结果的地方.我尝试调整此功能中的查询，以使搜索结果中也包含制造商，但没有运气.到目前为止，我的getProducts($filter_data)函数看起来像这样:
Which shows me that getProducts($filter_data) function of product.php file in model-catalog-product is where I need to define results. I tried tweaking the query in this function so that it includes manufacturer in search results too, but with no luck. So far, my getProducts($filter_data) function looks like this:
public function getProducts($data = array()) {
$sql = "SELECT p.product_id, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT price FROM " . DB_PREFIX . "product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM " . DB_PREFIX . "product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special";
if (!empty($data['filter_category_id'])) {
if (!empty($data['filter_sub_category'])) {
if(!empty($data['filter_sub_subcategory'])) {
$sql .= " FROM " . DB_PREFIX . "product_to_category p2c";
} else {
$sql .= " FROM " . DB_PREFIX . "product_to_category p2c";
}
//$sql .= " FROM " . DB_PREFIX . "category_path cp LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (cp.category_id = p2c.category_id)";
} else {
$sql .= " FROM " . DB_PREFIX . "product_to_category p2c LEFT JOIN " . DB_PREFIX . "category cc ON (p2c.category_id = cc.category_id)";
}
if (!empty($data['filter_filter'])) {
$sql .= " LEFT JOIN " . DB_PREFIX . "product_filter pf ON (p2c.product_id = pf.product_id) LEFT JOIN " . DB_PREFIX . "product p ON (pf.product_id = p.product_id)";
} else {
$sql .= " LEFT JOIN " . DB_PREFIX . "product p ON (p2c.product_id = p.product_id)";
}
} else {
$sql .= " FROM " . DB_PREFIX . "product p";
}
$sql .= " LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'";
if (!empty($data['filter_category_id'])) {
if (!empty($data['filter_sub_category'])) {
if(!empty($data['filter_sub_subcategory'])) {
$sql .= " AND p2c.category_id = '" . (int)$data['filter_sub_subcategory'] . "'";
} else {
$sql .= " AND p2c.category_id = '" . (int)$data['filter_sub_category'] . "'";
}
//$sql .= " AND cp.path_id = '" . (int)$data['filter_category_id'] . "'";
} else {
$sql .= " AND cc.parent_id = '" . (int)$data['filter_category_id'] . "'";
}
if (!empty($data['filter_filter'])) {
$implode = array();
$filters = explode(',', $data['filter_filter']);
foreach ($filters as $filter_id) {
$implode[] = (int)$filter_id;
}
$sql .= " AND pf.filter_id IN (" . implode(',', $implode) . ")";
}
}
if (!empty($data['filter_subcategory_id'])) {
if (!empty($data['filter_sub_category'])) {
$sql .= " AND p2c.category_id = '" . (int)$data['filter_sub_category'] . "'";
//$sql .= " AND cp.path_id = '" . (int)$data['filter_category_id'] . "'";
} else {
$sql .= " AND p2c.category_id = '" . (int)$data['filter_category_id'] . "'";
}
if (!empty($data['filter_filter'])) {
$implode = array();
$filters = explode(',', $data['filter_filter']);
foreach ($filters as $filter_id) {
$implode[] = (int)$filter_id;
}
$sql .= " AND pf.filter_id IN (" . implode(',', $implode) . ")";
}
}
if (!empty($data['filter_sub_subcategory'])) {
if (!empty($data['filter_sub_subcategory'])) {
$sql .= " AND p2c.category_id = '" . (int)$data['filter_sub_subcategory'] . "'";
//$sql .= " AND cp.path_id = '" . (int)$data['filter_category_id'] . "'";
} else {
$sql .= " AND p2c.category_id = '" . (int)$data['filter_category_id'] . "'";
}
if (!empty($data['filter_filter'])) {
$implode = array();
$filters = explode(',', $data['filter_filter']);
foreach ($filters as $filter_id) {
$implode[] = (int)$filter_id;
}
$sql .= " AND pf.filter_id IN (" . implode(',', $implode) . ")";
}
}
if (!empty($data['filter_name']) || !empty($data['filter_tag'])) {
$sql .= " AND (";
if (!empty($data['filter_name'])) {
$implode = array();
$words = explode(' ', trim(preg_replace('/\s+/', ' ', $data['filter_name'])));
foreach ($words as $word) {
$implode[] = "pd.name LIKE '%" . $this->db->escape($word) . "%'";
}
if ($implode) {
$sql .= " " . implode(" AND ", $implode) . "";
}
if (!empty($data['filter_description'])) {
$sql .= " OR pd.description LIKE '%" . $this->db->escape($data['filter_name']) . "%'";
}
}
if (!empty($data['filter_name']) && !empty($data['filter_tag'])) {
$sql .= " OR ";
}
if (!empty($data['filter_tag'])) {
$sql .= "pd.tag LIKE '%" . $this->db->escape($data['filter_tag']) . "%'";
}
if (!empty($data['filter_name'])) {
$sql .= " OR LCASE(p.model) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
$sql .= " OR LCASE(p.sku) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
$sql .= " OR LCASE(p.upc) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
$sql .= " OR LCASE(p.ean) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
$sql .= " OR LCASE(p.wholesale) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
$sql .= " OR LCASE(p.isbn) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
$sql .= " OR LCASE(p.mpn) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
}
$sql .= ")";
}
if (!empty($data['filter_manufacturer_id'])) {
$sql .= " AND p.manufacturer_id = '".(int)$data['filter_manufacturer_id']."'";
}
$sql .= " GROUP BY p.product_id";
$sort_data = array(
'pd.name',
'p.model',
'p.quantity',
'p.price',
'rating',
'p.sort_order',
'p.date_added'
);
if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
if ($data['sort'] == 'pd.name' || $data['sort'] == 'p.model') {
$sql .= " ORDER BY LCASE(" . $data['sort'] . ")";
} elseif ($data['sort'] == 'p.price') {
$sql .= " ORDER BY (CASE WHEN special IS NOT NULL THEN special WHEN discount IS NOT NULL THEN discount ELSE p.price END)";
} else {
$sql .= " ORDER BY " . $data['sort'];
}
} else {
$sql .= " ORDER BY p.sort_order";
}
if (isset($data['order']) && ($data['order'] == 'DESC')) {
$sql .= " DESC, LCASE(pd.name) DESC";
} else {
$sql .= " ASC, LCASE(pd.name) ASC";
}
if (isset($data['start']) || isset($data['limit'])) {
if ($data['start'] < 0) {
$data['start'] = 0;
}
if ($data['limit'] < 1) {
$data['limit'] = 20;
}
$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
}
$product_data = array();
$query = $this->db->query($sql);
foreach ($query->rows as $result) {
$product_data[$result['product_id']] = $this->getProduct($result['product_id']);
}
return $product_data;
}
任何人都可以帮助调整查询，以便它可以显示所有属于搜索到的制造商的产品吗?
Can anyone help tweak the query so that it can show all products belonging to a searched manufacturer?
谢谢.
推荐答案
因此，最后我意识到缺少的查询是什么.在行前
So, finally i realized what was the missing query. Before the line
$sql .= " LEFT JOIN " . DB_PREFIX . "product_description pd ON
(p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX .
"product_to_store p2s
我不得不放$sql .= " LEFT JOIN " . DB_PREFIX . "manufacturer m ON (m.manufacturer_id = p.manufacturer_id) ";
然后在该行之前
$sql .= " OR LCASE(p.model) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
我不得不放
$sql .= " OR LCASE(m.name) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
因此，很明显我缺少制造商数据.这样，我从制造商表中提取了数据并对其进行了正确处理.我希望这可以帮助某人，加油！
So obviously I was missing the manufacturer data. This way I pulled the data from manufacturer table and processed it correctly. I hope this helps someone, cheers!
这篇关于Opencart 2.2.0允许按制造商搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
