[Решено] OR и AND в стандартном фильтре 1.5.5.1

[Pavelligaigr]

Здравствуйте, ищу решение по допилу стандартного модуля фильтра Opencart 1.5.5.1. Читал темы но там старые ссылки которые уже, увы, не работают. Интересует, в частности, решение по выводу сразу двух значений фильтров по арлгоритму AND, чтобы показывало товары только те в которых есть только оба значения фильтра... Нашел модуль c такими решениями, но он почему то не работает:

 

Файл 1: catalog/model/catalog/filter.php

<?php
class ModelCatalogFilter extends Model {

    public function getFilterGroups($filter_ids) {
        $implode = array();

        $filters = explode(',', $filter_ids);

        foreach ($filters as $filter_id) {
            $implode[] = (int)$filter_id;
        }

        $sql = "SELECT COUNT(DISTINCT f.filter_group_id) AS total FROM `" . DB_PREFIX . "filter` f WHERE f.filter_id IN (" .
            implode(',', $implode) . ")";

        $query = $this->db->query($sql);

        if ($query->row) {
            return $query->row['total'];
        } else {
            return 0;
        }
    }        
}

и файл 2 vqmod/xml/oc_filter_module.xml

<?xml version="1.0" encoding="UTF-8"?>
<modification>
    <id><![CDATA[Color module Installation]]></id>
    <version><![CDATA[1.1.6]]></version>
    <vqmver><![CDATA[2.4.1]]></vqmver>
    <author><![CDATA[The Wizard of Osch, for www.CrystalCopy.nl]]></author>
    <file name="catalog/model/catalog/product.php" error="abort">
        <operation error="abort" info="Adding AND logic for standart opencart filter module">
            <search position="after" index="1,2"><![CDATA[$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)";]]></search>
            <add><![CDATA[$sql .= " LEFT JOIN " . DB_PREFIX . "filter f ON (pf.filter_id = f.filter_id)";]]></add>
        </operation>
        <operation error="abort" info="Adding AND logic for standart opencart filter module">
            <search position="after" index="1"><![CDATA[$sql .= " GROUP BY p.product_id";]]></search>
            <add><![CDATA[
            if (!empty($data['filter_filter'])) {
                $this->load->model('catalog/filter');
                $filter_group_count = $this->model_catalog_filter->getFilterGroups($data['filter_filter']);            
                $sql .= " HAVING COUNT(DISTINCT f.filter_group_id) = " . $filter_group_count;
            }
        ]]></add>
        </operation>
        <operation error="abort" info="Adding AND logic for standart opencart filter module">
            <search position="replace" index="1"><![CDATA[$sql = "SELECT COUNT(DISTINCT p.product_id) AS total";]]></search>
            <add><![CDATA[
            if (!empty($data['filter_filter'])) {
                $sql = "SELECT COUNT(*) AS total FROM (SELECT COUNT(DISTINCT p.product_id) AS total";
            } else {
                $sql = "SELECT COUNT(DISTINCT p.product_id) AS total";
            }
        ]]></add>
        </operation>
        <operation error="abort" info="Adding AND logic for standart opencart filter module">
            <search position="before" index="3"><![CDATA[$query = $this->db->query($sql);]]></search>
            <add><![CDATA[
            if (!empty($data['filter_filter'])) {
                $this->load->model('catalog/filter');
                $filter_group_count = $this->model_catalog_filter->getFilterGroups($data['filter_filter']);
                $sql .= " GROUP BY p.product_id HAVING COUNT(DISTINCT f.filter_group_id) = '" . $filter_group_count . "') t";
            }
        ]]></add>
        </operation>
        <operation error="abort" info="Adding AND logic for standart opencart filter module">
            <search position="replace" index="1"><![CDATA[return $query->row['total'];]]></search>
            <add><![CDATA[
            if ($query->row) {
                return $query->row['total'];
            } else {
            return 0;
            }
        ]]></add>
        </operation>
    </file>
</modification>

Буду благодарен Донатом за помощь и поправки.

Так же смотрел и платные модули, но для решения моих потребностей достаточно такого варианта, так как товаров до 1000 и они однотипные... Заранее благодарен всем кто поможет...

[Pavelligaigr]

Разобрался сам, надеюсь что кому то поможет мое решение:

 

Открываем файл catalog\model\catalog\product.php

Вместо строки (примерно 100 строка):

$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)";                      

Вставляем:

$sql .= " LEFT JOIN " . DB_PREFIX . "product_filter pf_1 ON (p2c.product_id = pf_1.product_id) LEFT JOIN " . DB_PREFIX . "product p ON (p2c.product_id = p.product_id)";
                $fid_array = array();
                $filters = explode(',', $data['filter_filter']);
                foreach ($filters as $filter_id) {
                    $fid_array[] = (int)$filter_id;
                }
                $query = $this->db->query("SELECT COUNT(DISTINCT filter_group_id) AS num_fgid FROM " . DB_PREFIX . "filter_description WHERE filter_id IN(" . implode(',', $fid_array) . ")");
                if(!empty($query->row['num_fgid'])) {
                    for ($thisgrp = 2; $thisgrp <= $query->row['num_fgid']; $thisgrp++) {
                        $sql .= " LEFT JOIN " . DB_PREFIX . "product_filter pf_" . $thisgrp . " ON (p2c.product_id = pf_" . $thisgrp . ".product_id) ";
                    }
                }

 

Дальше вместо (Примерно 130 строка):

$implode = array();
				
				$filters = explode(',', $data['filter_filter']);
				
				foreach ($filters as $filter_id) {
					$implode[] = (int)$filter_id;
				}
				
				$sql .= " AND pf.filter_id IN (" . implode(',', $implode) . ")";

 

Вставляем:

$query = $this->db->query("SELECT fd.filter_group_id, fd.filter_id, n.num_sel_filters_in_grp FROM " . DB_PREFIX . "filter_description fd left join (SELECT filter_group_id, count(filter_group_id) as num_sel_filters_in_grp FROM " . DB_PREFIX . "filter_description WHERE filter_id IN (" . implode(',', $fid_array) . ") GROUP BY filter_group_id) as n on fd.filter_group_id = n.filter_group_id WHERE filter_id IN (" . implode(',', $fid_array) . ") GROUP BY fd.filter_group_id, fd.filter_id");
				if ($query->num_rows) {
					$g_count = 0;
					$f_count = 0;
					$last_fgid = -1;
					foreach ($query->rows as $frow) {
						$f_count += 1;
						if ($frow['filter_group_id'] != $last_fgid) {
							$g_count += 1;
							$f_count = 1;
							$last_fgid = $frow['filter_group_id'];
							$sql .= " AND (pf_" . $g_count . ".filter_id = " . $frow['filter_id'];
						}
						else {
							if ($frow['num_sel_filters_in_grp'] > 1) {
								$sql .= " OR pf_" . $g_count . ".filter_id = " . $frow['filter_id'];
							}
						}
						if ($f_count == $frow['num_sel_filters_in_grp']) {
							$sql .= ")";
						}
					}
				}

 

Дальше вместо (примерно 510 строка):

$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)";

 

Вставляем

$sql .= " LEFT JOIN " . DB_PREFIX . "product_filter pf_1 ON (p2c.product_id = pf_1.product_id) LEFT JOIN " . DB_PREFIX . "product p ON (p2c.product_id = p.product_id)";
				$fid_array = array();
				$filters = explode(',', $data['filter_filter']);
				foreach ($filters as $filter_id) {
					$fid_array[] = (int)$filter_id;
				}
				$query = $this->db->query("SELECT COUNT(DISTINCT filter_group_id) AS num_fgid FROM " . DB_PREFIX . "filter_description WHERE filter_id IN(" . implode(',', $fid_array) . ")");
				if(!empty($query->row['num_fgid'])) {
					for ($thisgrp = 2; $thisgrp <= $query->row['num_fgid']; $thisgrp++) {
						$sql .= " LEFT JOIN " . DB_PREFIX . "product_filter pf_" . $thisgrp . " ON (p2c.product_id = pf_" . $thisgrp . ".product_id) ";
					}
				}

 

Дальше вместо (примерно 517 строка):

$implode = array();
				
				$filters = explode(',', $data['filter_filter']);
				
				foreach ($filters as $filter_id) {
					$implode[] = (int)$filter_id;
				}
				
				$sql .= " AND pf.filter_id IN (" . implode(',', $implode) . ")";

 

Вставляем

$query = $this->db->query("SELECT fd.filter_group_id, fd.filter_id, n.num_sel_filters_in_grp FROM " . DB_PREFIX . "filter_description fd left join (SELECT filter_group_id, count(filter_group_id) as num_sel_filters_in_grp FROM " . DB_PREFIX . "filter_description WHERE filter_id IN (" . implode(',', $fid_array) . ") GROUP BY filter_group_id) as n on fd.filter_group_id = n.filter_group_id WHERE filter_id IN (" . implode(',', $fid_array) . ") GROUP BY fd.filter_group_id, fd.filter_id");
				if ($query->num_rows) {
					$g_count = 0;
					$f_count = 0;
					$last_fgid = -1;
					foreach ($query->rows as $frow) {
						$f_count += 1;
						if ($frow['filter_group_id'] != $last_fgid) {
							$g_count += 1;
							$f_count = 1;
							$last_fgid = $frow['filter_group_id'];
							$sql .= " AND (pf_" . $g_count . ".filter_id = " . $frow['filter_id'];
						}
						else {
							if ($frow['num_sel_filters_in_grp'] > 1) {
								$sql .= " OR pf_" . $g_count . ".filter_id = " . $frow['filter_id'];
							}
						}
						if ($f_count == $frow['num_sel_filters_in_grp']) {
							$sql .= ")";
						}
					}
				}

 

У меня заработало на версии Opencart 1.5.5.1, там есть повторяющиеся строки, главное не удалить то что не нужно, ориентируйтесь по примерному номеру строки... Модуль который писал выше можно не использовать, достаточно только такого изменения.
                     

Змінено 30 листопада 2017 користувачем Pavelligaigr