Post by midgette » Tue May 28, 2013 1:29 pm

Q000, Thank you - Your code is a 'must have'!

SteitSlings, Thank You - That is perfect!

Nicusor (azzido), I will check this thread several times a day until you say your problem is solved.
If you would prefer a vQmod for this, just let me know.
I'm pretty sure the error message you're seeing has truncated SQL. One way to know is to edit your catalog/model/catalog/product.php file as follows: Before the third occurrence of

Code: Select all

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

Code: Select all

      // Save $sql to a file for debugging
      $myFile = "mysql_debug.txt";
      $fh = fopen($myFile, 'w') or die("can't open file");
      fwrite($fh, $sql);
      fclose($fh);
Then run till you see the error and then look at file mysql_debug.txt and see what part of the query is incorrect.

Another way would be to compare your catalog/model/catalog/product.php to the one I use. Here is the 1.5.5.1 catalog/model/catalog/product.php I use: Please let me know if this helps.

Regards,
midgette

Newbie

Posts

Joined
Sun Apr 28, 2013 4:16 pm

Post by robertiulianstoica » Thu May 30, 2013 4:01 pm

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'");
DONE! Now your filters will load in every category depending on the products contained. You no longer need to insert filters into categories, neither will they apear if there are no products returned by the filter.

Ps: if anyone is interested, i will later provide a code to select filters depending on the filtered products loaded in the category.

Cheers!

EDIT 1: I forgot the posibility of a db prefix. Updated.
EDIT 2: Shows only the filters from the active products.
Last edited by robertiulianstoica on Thu Jul 11, 2013 12:55 pm, edited 2 times in total.

robertiulianstoica[at]gmail.com


New member

Posts

Joined
Tue May 07, 2013 8:42 pm

Post by caesar07000 » Tue Jun 25, 2013 5:00 am

Ps: if anyone is interested, i will later provide a code to select filters depending on the filtered products loaded in the category.
i think i am interested...

Newbie

Posts

Joined
Tue Apr 23, 2013 1:58 am

Post by Spiritcraft » Thu Jun 27, 2013 10:06 am

I think I would be interested as well.

I wonder if anything exists or is upcoming that would be like a filters manager. Some sort of extension or something that would allow you to select say twenty products and assign certain filters to them. I really like the filter but with many of my categories it would be a lifes work to open up each product and assign filters to that product. An example... you have a category with 1,000 fabrics and you want to filter for velvets, prints, wovens, colors etc. I have it set up currently for smaller categories like a line of drapery rods with 20 items in the series. Filters works awesome there and is not a chore to implement due to only dealing with 25 or so products.

I am a recovering zencart person, happily building my replacement store in Opencart.

Newbie

Posts

Joined
Sun Jun 23, 2013 12:30 pm

Post by robertiulianstoica » Thu Jun 27, 2013 3:40 pm

I will return with a post about the solution mentioned in the past later this evening.

As for adding multiple filters to products, i have changed the filters main programming. I added a LABEL field for the filter group. I have created buttons for each label, and by pressing them i get the required category of filters loaded with drop down select.

robertiulianstoica[at]gmail.com


New member

Posts

Joined
Tue May 07, 2013 8:42 pm

Post by Girlinabillion » Sat Jun 29, 2013 7:52 am

Yes - it would be great if someone could wrap this all up neatly into an extension: Live filtering, automatic adding of filters to categories and only the ones that will return products in that category, and using the AND function, not OR. Is someone working on making an extension out of this? I would pay for that. Thanks!

New member

Posts

Joined
Sun Jun 02, 2013 12:08 pm

Post by Spiritcraft » Sun Jun 30, 2013 10:58 pm

"As for adding multiple filters to products, i have changed the filters main programming. I added a LABEL field for the filter group. I have created buttons for each label, and by pressing them i get the required category of filters loaded with drop down select."

It really works beautifully but it would be simply incredible to go down a filtered list (by keyword perhaps) and select filters for all of those found keyworded products. Drag and drop? So is what you have done in an extension somewhere?

Newbie

Posts

Joined
Sun Jun 23, 2013 12:30 pm

Post by robertiulianstoica » Sun Jun 30, 2013 11:17 pm

I haven't put it in an extension yet. I used this method on the online shop i work for. It works beautifully, saving a lot of time for the content managers (the people that add products).

robertiulianstoica[at]gmail.com


New member

Posts

Joined
Tue May 07, 2013 8:42 pm

Post by Spiritcraft » Mon Jul 01, 2013 12:13 am

Should I try the code you posted earlier or are you coming out with something else? I would imagine a vqmod extension would be a moneymaker. Otherwise I am interested in whatever you can do. I think the filter module could be a gamechanger in terms of customer experience.

Newbie

Posts

Joined
Sun Jun 23, 2013 12:30 pm

Post by Spiritcraft » Tue Jul 02, 2013 1:12 am

I tried that bit of code and it broke my store... I am using v1.5.5.1 Should this have worked?

Newbie

Posts

Joined
Sun Jun 23, 2013 12:30 pm

Post by robertiulianstoica » Wed Jul 03, 2013 3:54 pm

Problem solved, the code has been updated!
Spiritcraft wrote:I tried that bit of code and it broke my store... I am using v1.5.5.1 Should this have worked?

robertiulianstoica[at]gmail.com


New member

Posts

Joined
Tue May 07, 2013 8:42 pm

Post by Spiritcraft » Sat Jul 06, 2013 9:43 pm

Yes, Robert did fix it. I think the bug came from an incompatibility with "fast product relator" that I have installed via vqmod. It really works well now.

Newbie

Posts

Joined
Sun Jun 23, 2013 12:30 pm

Post by xonium » Mon Jul 08, 2013 9:38 pm

Great work and thanks to everyone who worked on fixing the AND / OR logic, and for robertiulianstoica for a fix that automatically loads the filters based on the products that has them.

This is all how opencart should work by default, and how I think most people actually expect it to work, and also how I know that other e-commerce software work. I really hope this will become the default behaviour for opencart in the future.

The only thing to fix now is to merge attributes and filters, totally superfluous to have it as two different things. It just makes it extra time consuming to set both up if you want attributes to be filterable. A checkbox for each attribute if it should work like a filter would be the solution (as a end user perspective). The code and database work for this should be simpler than what it currently is. Even to merge it with product options could save a lot of time for the actual users of opencart.

Basically you would have only product attributes, and you would have checkboxes for each of them:
[x] also work as a product option
[x] also use as a product filter

So that people won't have to set up both filters, product options and attributes for the same thing.

Newbie

Posts

Joined
Wed Jun 09, 2010 4:32 am

Post by robertiulianstoica » Thu Jul 11, 2013 5:16 am

Thanks for the cheers!

I agree, this is how opencart should work to be less time consuming to users, but if you think it through, combining them would mean a lot of possible bugs.

I think that the main mentality for opencart is "The simpler, the better."
There is no right or wong way to code, just a simpler or a more complex way to do it the result in the end is either simple and stable or complex and rather unstable.
xonium wrote:Great work and thanks to everyone who worked on fixing the AND / OR logic, and for robertiulianstoica for a fix that automatically loads the filters based on the products that has them.

This is all how opencart should work by default, and how I think most people actually expect it to work, and also how I know that other e-commerce software work. I really hope this will become the default behaviour for opencart in the future.

The only thing to fix now is to merge attributes and filters, totally superfluous to have it as two different things. It just makes it extra time consuming to set both up if you want attributes to be filterable. A checkbox for each attribute if it should work like a filter would be the solution (as a end user perspective). The code and database work for this should be simpler than what it currently is. Even to merge it with product options could save a lot of time for the actual users of opencart.

Basically you would have only product attributes, and you would have checkboxes for each of them:
[x] also work as a product option
[x] also use as a product filter

So that people won't have to set up both filters, product options and attributes for the same thing.

robertiulianstoica[at]gmail.com


New member

Posts

Joined
Tue May 07, 2013 8:42 pm

Post by online-trade » Sun Jul 14, 2013 5:10 pm

Hello, everybody!
Thank you for this good solution first.
I am new here and now I am set my filters. Maybe I didn't understand but is possible somehow to connect the filter's group with the options of the products (sample "size">S;M;L) to not put on hand every size for product and when let say size S is out of stock for some product to not show in the filter when is checked "S"(to not need to delete every out of stock size for every product)?
The same question for filter by price and manifacturer?
Thank you very much for the help!

Newbie

Posts

Joined
Sun Jul 14, 2013 4:57 pm

Post by JohnnyNM » Fri Jul 26, 2013 6:23 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'");
DONE! Now your filters will load in every category depending on the products contained. You no longer need to insert filters into categories, neither will they apear if there are no products returned by the filter.

Ps: if anyone is interested, i will later provide a code to select filters depending on the filtered products loaded in the category.

Cheers!

EDIT 1: I forgot the posibility of a db prefix. Updated.
EDIT 2: Shows only the filters from the active products.

Hi robertiulianstoica,
Do you have any idea how this could be changed to pull in all the filters from the sub-categories as well?
I've already got all the products in the sub-categories to show up in their parent, but I need the filters to reflect this.
I'm afraid that sort of SQL is a bit beyond me!
Cheers,
John.

Newbie

Posts

Joined
Fri Dec 09, 2011 8:18 pm

Post by robertiulianstoica » Fri Jul 26, 2013 7:02 pm

Well, in order to do that, you should include your products from you subcategory into your main category. Else, you don't have anything to filter :D . The code from above will do the rest of the work for you.

robertiulianstoica[at]gmail.com


New member

Posts

Joined
Tue May 07, 2013 8:42 pm

Post by JohnnyNM » Fri Jul 26, 2013 7:51 pm

robertiulianstoica wrote:Well, in order to do that, you should include your products from you subcategory into your main category. Else, you don't have anything to filter :D . The code from above will do the rest of the work for you.
I've already got the products from the subs in the parent, by code, rather than by setting them in each product's category section. I've tested the code and if there's a filter two levels down it won't pick it up.

I was thinking a loop to output the sub category id's into the 'WHERE' as 'WHERE... OR...'?
But it's a little more than I think I can handle successfully! :)
Thanks,
J.

Newbie

Posts

Joined
Fri Dec 09, 2011 8:18 pm

Post by bullet187 » Sun Jul 28, 2013 3:42 am

Hi everyone,
This is my first post as I am setting up my first shop ever, so please don't go too hard on me ;)
First, let me say a great THANK YOU to Ganisek fo price slider - i really love it.
But i have two questions regarding this modification:

1st. - slider relates to net prices, but the polish law is that in all prices in the store must be shown with tax, so slider filtering differs from actual prices shown as the result, which I'm sure will be confusing to customers - is there a way to filter gross prices with the slider?

2nd. - this may be quite offending for your knowledge, but I'm a REAL newbie :) - how can i change slider's yellow color to some other one?

Thanx in advance!

Newbie

Posts

Joined
Sun Jul 28, 2013 3:31 am

Post by CaptainHaddock » Thu Aug 01, 2013 5:40 am

JohnnyNM wrote:
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'");
DONE! Now your filters will load in every category depending on the products contained. You no longer need to insert filters into categories, neither will they apear if there are no products returned by the filter.

Ps: if anyone is interested, i will later provide a code to select filters depending on the filtered products loaded in the category.

Cheers!

EDIT 1: I forgot the posibility of a db prefix. Updated.
EDIT 2: Shows only the filters from the active products.

Hi robertiulianstoica,
Do you have any idea how this could be changed to pull in all the filters from the sub-categories as well?
I've already got all the products in the sub-categories to show up in their parent, but I need the filters to reflect this.
I'm afraid that sort of SQL is a bit beyond me!
Cheers,
John.
Hi,
I also have all sub category products showing in parent category by using this extension
http://tinyurl.com/products-in-subcat-in-parent
The problem is the products from subcategories do not show in the parent's filters.
Is this the same as your problem John? Did you find a solution?

E.g. Parent Category "Animals" has subcategories "Dogs" and "Cats". I have set up Animals and Cats and Dogs with filters for colors. If I am in subcategory dog, I can use filter to see only black dogs.
I would like to be able to use filter at parent category level to show all black dogs and black cats.

Did you work out how to make this happen?

Thanks very much
Peter

New member

Posts

Joined
Tue Jul 02, 2013 7:01 am

Who is online

Users browsing this forum: No registered users and 29 guests