Product Filters

[Kofod95]

I've been working on a filter that will enable the admin to configure what data they want customers to be able to filter products by. It will automatically show on any product listings that uses the templated-listing component. Also, it allows the admin to set up filters for data from the products-table (as either a range or a id-based filter), for attributes (with automatic check for QTPro) or for S04E10 - Products Specifications. All filters will show as drop-downs, except range-filters, which will show as a minimum or maximum input-field pre-filled with the min and max values in the current listing, with a button to submit.
I'm working on adding tax to the values, if a filter for price is set up, but that is currently not ready.
Also, the support for QTPro needs some optimising of the sql if there are many attributes and stock-levels, so feel free to contribute to that, if any of you have ways to optimise that!

I've included a pdf that hopefully makes it easier to understand how to set up filters, but feel free to ask.

//Daniel

[ecartz]

Does QT Pro have an index on products_stock on products_id and products_stock_quantity?

You might want to change the existing index on products_attributes.products_id to be products_id, options_values_id

As best I can see,

Code: Select all

	  if(!empty($group_by)) $GLOBALS['filter_listing'] .= ' GROUP BY ' . implode(', ', $group_by);

could be

Code: Select all

	  if(!empty($group_by)) $GLOBALS['filter_listing'] .= ' GROUP BY p.products_id';

without any change in functionality.

Code: Select all

	    $active_filters = [];
	    $applied_filters = $_GET['f'];
	    foreach($applied_filters as $k => $v){
		  if($v != '') array_push($active_filters, $k);
	    }

could be

Code: Select all

	    $active_filters = array_keys(array_filter($_GET['f']));

[Kofod95]

Awesome, @ecartz, thank you!
I will apply your suggestions and re-upload.

//Daniel

[Kofod95]

Does QT Pro have an index on products_stock on products_id and products_stock_quantity?

I'm not sure, but I will look for it - I guess that would improve the speed quite a bit, if that's the case.

//Daniel

[Omar_one]

@ecartz @Kofod95
I just applied the changes

As best I can see,

Code: Select all

	could be [code]	  if(!empty($group_by)) $GLOBALS['filter_listing'] .= ' GROUP BY p.products_id');

there is extra ")"
the filter not picking the products just reload the page after making the changes

If I applied the 1st change after deleting the extra ")" and the 2st one changed it to this

Code: Select all

	    $active_filters = array_filter(array_values($_GET['f']));
	    $applied_filters = $_GET['f'];
	    foreach($applied_filters as $k => $v){
		  if($v != '') array_push($active_filters, $k);
	    }

the filter more faster, but loading the products so so slow on categories with have many attributes options, ( I am using QtPro module )
Thank you
Omar

[ecartz]

I updated both changes (in the previous post) based on that feedback.

Note that the code changes probably won't make things faster (at least not noticeably). What should make things faster is modifying/adding the indexes. However, if you really want to make that faster, you might need to build a proper search index.

[Omar_one]

What should make things faster is modifying/adding the indexes. However, if you really want to make that faster, you might need to build a proper search index.

Any idea how to build a search index? On which table/tables?

[Omar_one]

Note that the code changes probably won't make things faster (at least not noticeably). What should make things faster is modifying/adding the indexes. However, if you really want to make that faster, you might need to build a proper search index.

adding the index combination for products_id and products_stock_quantity to the products_stock table didn't make it faster, thanks @raiwa for provide me with the query

I think the issue on the lines 40-45 as if I remove the checking for stock from that query the filter working perfectly and not slow at all

thank you in advance for your help
Omar

[Kofod95]

Sorry for being slow to react here!

Code: Select all

	    $active_filters = array_keys(array_filter($_GET['f']));

This works fine as long as there are no "range"-filters - I guess I could target them afterwards.
I found a couple of bugs as well, so a revised version will come, though it may take a while.

I think the issue on the lines 40-45 as if I remove the checking for stock from that query the filter working perfectly and not slow at all

Both line 42 and line 140 would probably need optimising.
I think I'm in over my head with this issue, sorry. I'm satisfied with how it functions without QTPro, but I agree that it is to slow to be usable with. I wonder if it's worth trying some sort of " AND p.products_id IN ($options_in_stock), and set that variable in a separate query, but I thought JOIN was faster.

//Daniel

[Moxamint]

Hi,

I hope this is a related question - how do I completely remove the core product filter?

Thanks, Eddy