Post by nongtinh10 » Tue Jun 17, 2014 12:09 pm

any solution ? I also need several condition in same time.

công ty môi trường | dịch vụ seo | học kế toán thực hành | học kế toán tổng hợp | quan ao tre em | làm bằng đại học


Newbie

Posts

Joined
Tue Jun 17, 2014 12:07 pm

Post by erebus » Sun Jun 22, 2014 5:17 pm

I have opencart 1.5.6.4 and mariadb Ver 15.1 Distrib 10.0.9

There is a small change you need to make in midgette's mod in page 2
(http://forum.opencart.com/viewtopic.php ... 5&start=20)
in order for this fantastic mod to work.

For some reason, midgette's version leaves somewhere an open parenthesis that causes havoc
to newer versions of mysql or mariadb. Thankfully, the syntax error was reported
clearly in the error.log file.

After debugging the $sql output, I found the solution by making the AND / OR
operators look like this:

Code: Select all

... ' AND (pf_1.filter_id = 7 OR pf_1.filter_id = 8) AND (pf_2.filter_id = 17) AND (pf_3.filter_id = 23 OR pf_3.filter_id = 26) GROUP BY ...
So my version of working code is below:

in catalog\model\catalog\product.php

replace (line 81):

Code: Select all

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

Code: Select all

                $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) ";
                   }
                }
replace (lines 99-107):

Code: Select all

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

Code: Select all

                        $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;
                   $last_fgid = -1;
                   foreach ($query->rows as $frow) {
                      if ($frow['filter_group_id'] != $last_fgid) {
                         $g_count++;
                         $last_fgid = $frow['filter_group_id'];
                         if ($g_count==1) $sql .= " ";
                         else $sql .= ") ";
                         $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'];
                         }
                      }
                   }
                   $sql .= ")";
                }
replace (line 482):

Code: Select all

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

Code: Select all

                $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) ";
                   }
                }
replace (lines 500-508):

Code: Select all

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

Code: Select all

                $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;
                   $last_fgid = -1;
                   foreach ($query->rows as $frow) {
                      if ($frow['filter_group_id'] != $last_fgid) {
                         $g_count++;
                         $last_fgid = $frow['filter_group_id'];
                         if ($g_count==1) $sql .= " ";
                         else $sql .= ") ";
                         $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'];
                         }
                      }
                   }
                   $sql .= ")";
                }
Take note that this is the only mod that supports proper pagination.
Existing VQMOD for AND operator between filter groups has serious issues with pagination in opencart 1.5.6.4

Original thanks go to midgette, the code master! 8)

Newbie

Posts

Joined
Thu May 15, 2014 3:06 pm

Post by DAJOGA » Sun Jul 27, 2014 6:48 pm

I have a problem with new filter showing.
Filters created.
Extensions > Modules > Filter installed
Layout: Category, Column left

Category > Data > Filters does not see filter I created.
And autocomplete doesn't work.

What to do?

Newbie

Posts

Joined
Sun Jul 27, 2014 6:41 pm

Post by amj007 » Wed Feb 25, 2015 1:40 pm

rph wrote:1. Go to Catalog > Filters and select Insert to create a filter group. Assign a filter group name (e.g. Color) and add filter name values (e.g. Blue, Red, Yellow).

Hi - I cant save the filter name values - As soon as I click on the save button the created filter names disappear -
I can create as many filter group names without any problems but I am not able to create filter name values in one of my filter groups!!

any help would be much appreciated



2. Go to Catalog > Categories and Edit a category. Under the Data tab add the filters you want to be able to apply to that category (e.g. Color > Blue, Color > Red).
3. Go to Catalog > Products and Edit a product. Under the Links tab add the filters which apply to the product (e.g. Color > Blue). Apply to as many products as applicable.
4. Go to Extensions > Modules > Filter. If not installed select Install. Click Edit. Click the Add Module button and under Layout select Category and set Status to Enabled. Set whatever position and sort order you would like.

Newbie

Posts

Joined
Wed Feb 25, 2015 1:12 pm

Post by seid02 » Fri Mar 13, 2015 10:03 pm

Hi
@erebus

would this mod work in 2.0.1.1 version.
thank you
and sorry for my bad eanglis.

http://www.keramikaoutlet.si/
Pralne plenice
TV Prodaja


New member

Posts

Joined
Wed Mar 11, 2015 10:43 pm


Post by storm-cloud » Thu May 07, 2015 6:31 pm

robertiulianstoica wrote:For all of you who are interested in getting the category filters from the products loaded in the category, not from the table category_filter you must go to catalog/model/catalog/category.php and modify the function "getCategoryFilters" by replacing

Code: Select all

$query = $this->db->query("SELECT filter_id FROM " . DB_PREFIX . "category_filter WHERE category_id = '" . (int)$category_id . "'");
with

Code: Select all

$query = $this->db->query("SELECT pf.filter_id FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id) LEFT JOIN " . DB_PREFIX . "product_filter pf ON (p.product_id = pf.product_id) WHERE p2c.category_id = '" . (int)$category_id . "' AND p.status='1'");
I have just been revisiting this and was wondering if anyone would know how I would modify this code to only display filters applicable to the products displayed after each filter is applied?

To clarify, after a filter is applied this code should run again to load only the filters applicable to the products currently displayed in the refined list.

Active Member

Posts

Joined
Wed Feb 22, 2012 8:07 am

Post by storm-cloud » Wed May 20, 2015 8:02 pm

It's a shame that nobody can help with the above.

The default filter has the potential to be near perfect. However, a better user experience is required and for this to occur, filters that do not apply to the current product list need to be disabled or hidden.

This is the manner in which any decent filter works. The customer makes a filter selection, the product list is updated and the available filters are also updated accordingly.

Active Member

Posts

Joined
Wed Feb 22, 2012 8:07 am

Post by Kenberne1965 » Tue Jun 02, 2015 6:34 pm

erebus wrote:I have opencart 1.5.6.4 and mariadb Ver 15.1 Distrib 10.0.9

There is a small change you need to make in midgette's mod in page 2
(http://forum.opencart.com/viewtopic.php ... 5&start=20)
in order for this fantastic mod to work.

For some reason, midgette's version leaves somewhere an open parenthesis that causes havoc
to newer versions of mysql or mariadb. Thankfully, the syntax error was reported
clearly in the error.log file.

After debugging the $sql output, I found the solution by making the AND / OR
operators look like this:

Code: Select all

... ' AND (pf_1.filter_id = 7 OR pf_1.filter_id = 8) AND (pf_2.filter_id = 17) AND (pf_3.filter_id = 23 OR pf_3.filter_id = 26) GROUP BY ...
So my version of working code is as above ................................
Thanks This works very well :)

Freelance Opencart 1.5.x and 2.x Developer - Available For Hire
Latest Opencart 2 Project --> http://www.falcon-rangecookers.co.uk


User avatar
Active Member

Posts

Joined
Wed Apr 22, 2015 9:37 pm
Location - Doncaster, UK

Post by dangngoclinh247 » Mon Jun 15, 2015 2:43 am

Category > Data > Filters does not see filter I created.
And autocomplete doesn't work.

cac mon an ngon | best wordpress themes |magazine fuse



Posts

Joined
Sat May 30, 2015 9:57 am


Post by Afsal_Y » Wed Nov 18, 2015 3:28 pm


Newbie

Posts

Joined
Wed Nov 18, 2015 3:23 pm
Who is online

Users browsing this forum: No registered users and 31 guests