Internal server error using filters

  • Posts: 149
  • Thank you received: 0
12 years 5 months ago #54782

Hello,

We used a filter by price module and when we choose to filter products that match price<50 EUR the page loading for some seconds and then it expires and gives an internal server error 500. We have other filters on the site also but we dont get this type of error. Maybe is because we have 10 000+ products on the database but im not sure why it timesout and gives internal server error.

anyone knows what might be the issue?

Please Log in or Create an account to join the conversation.

  • Posts: 82863
  • Thank you received: 13372
  • MODERATOR
12 years 5 months ago #54828

Hi,


It's because an extensive processing, so if you have lots of products it's normal that it takes lots of resources. That's because the prices need to be calculated dynamically based on currency rates, fees, discounts, variants, etc. So the SQL query becomes really heavy. The only solution would be to precalculate the prices automatically with a cron so that the results could be used directly for sorting, without having to recalculate everything. That's possible, but not something I can fix for you in a few hours. That would require proper development and is not a small task.

Please Log in or Create an account to join the conversation.

  • Posts: 149
  • Thank you received: 0
12 years 5 months ago #54841

So basically i cannot use price for filtering? If the price is combine with other filders its working but alone its not. I would like to disable calculations of discounts, rates and variants. Just make filtering of normal price. Is tha possible? Or maybe create a cashe on the database to solve this.

Please suggest a solution i cant imagine thrre is not a way to filter 10k products by price

Please Log in or Create an account to join the conversation.

  • Posts: 82863
  • Thank you received: 13372
  • MODERATOR
12 years 5 months ago #54926

The problem is that the MySQL has to make operations on all the prices of all the products which are on the listing regardless of the pagination before it can sort the products.
If you apply another filter and that you only have 100 products, the sorting by price is fast for MySQL. But for a 10K products listing, that's too much.

If you disable the calculations, it could help, but then you might get strange results where the prices are in fact not sorted by the correct value of the price of the products displayed.


First, you can try to change the lines:
$case.= ' end ';
return $case;

to:
return 'b.price_value';

in the file adminsitrator/components/com_hikashop/classes/filter.php
That will deactivate the rates handling in the query.

Then, if that's not enough, you can replace:

$subfilters = array();
				$where = '';
				hikashop_addACLFilters($subfilters,'price_access','price'.$i,2,true);
				$subfilters[]='product'.$i.'.product_type=\'main\'';
				$where = ' WHERE '.implode(' AND ',$subfilters);
				$subquery ='SELECT * FROM '.hikashop_table('product').' AS product'.$i.' LEFT JOIN  '.hikashop_table('price').' AS price'.$i.' ON product'.$i.'.product_id=price'.$i.'.price_product_id '.$where.' GROUP BY product'.$i.'.product_id ORDER BY price'.$i.'.price_min_quantity ASC';
				$a = '('.$subquery.') AS b';
by:
$on.=' LEFT JOIN '.hikashop_table('price').' AS b ON b.price_product_id=a.product_id AND b.price_min_quantity=0';
That will disable the handling of quantity prices, ACL on prices and variants, all at once. That modification should greatly help avoid the performances issues.

Please Log in or Create an account to join the conversation.

  • Posts: 149
  • Thank you received: 0
12 years 3 months ago #60697

Hi Nicolas,

We have tried the above and the results are:

1. Change the lines:
$case.= ' end ';
return $case;
to:
return 'b.price_value';

This did not resolve the issue. We still get timeout of the page
2. Reset point (1) back to what it used to be and perform the second change

This one worked but we do not get any results back. Also changed the b.price_min_quantity=0 to =1 as this is the number used

Please Log in or Create an account to join the conversation.

  • Posts: 82863
  • Thank you received: 13372
  • MODERATOR
12 years 3 months ago #60780

Hi,

Can you try to turn on the debug mode option of the joomla configuration with the second modification ? Do you get any error in that case ? If not, what are the queries which are run on the page ?

Please Log in or Create an account to join the conversation.

  • Posts: 149
  • Thank you received: 0
12 years 3 months ago #60791

Hi nicolas,

We have try to perform the same but for some reason we get an error and the query printed below is:
500
JDatabaseMySQL::query: 1066 - Not unique table/alias: 'b' SQL=SELECT DISTINCT b.* FROM jos_hikashop_product_category AS a LEFT JOIN jos_hikashop_product AS b ON a.product_id=b.product_id LEFT JOIN jos_hikashop_price AS b ON b.price_product_id=a.product_id AND b.price_min_quantity=1 INNER JOIN jos_hikashop_file AS c ON b.product_id=c.file_ref_id AND c.file_type='product' WHERE b.product_published=1 AND b.product_type = 'main' AND a.category_id IN (12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,50,40,41,42,43,44,45,46,47,48,49,51,52,53,2) AND b.product_quantity!=0 AND (b.product_access = 'all' OR b.product_access LIKE '%,29,%') AND ( ( case when b.price_currency_id = '1' then b.price_value when b.price_currency_id = '2' then b.price_value/1 end ) <= 75 ) AND b.product_weight > 0 AND c.file_id>0 ORDER BY a.ordering ASC LIMIT 0, 20

i guess because before we did not have debug mode on we did not catch this and we were only seeing empty page.
I have changed the query to
$on.=' LEFT JOIN '.hikashop_table('price').' AS d ON d.price_product_id=a.product_id AND d.price_min_quantity=1';
but we get the below
JDatabaseMySQL::query: 1054 - Unknown column 'b.price_currency_id' in 'where clause' SQL=SELECT DISTINCT b.* FROM jos_hikashop_product_category AS a LEFT JOIN jos_hikashop_product AS b ON a.product_id=b.product_id LEFT JOIN jos_hikashop_price AS d ON d.price_product_id=a.product_id AND d.price_min_quantity=1 INNER JOIN jos_hikashop_file AS c ON b.product_id=c.file_ref_id AND c.file_type='product' WHERE b.product_published=1 AND b.product_type = 'main' AND a.category_id IN (12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,50,40,41,42,43,44,45,46,47,48,49,51,52,53,2) AND b.product_quantity!=0 AND (b.product_access = 'all' OR b.product_access LIKE '%,29,%') AND ( ( case when b.price_currency_id = '1' then b.price_value when b.price_currency_id = '2' then b.price_value/1 end ) <= 75 ) AND b.product_weight > 0 AND c.file_id>0 ORDER BY a.ordering ASC LIMIT 0, 20


Can you advice?


Thanks

Please Log in or Create an account to join the conversation.

  • Posts: 82863
  • Thank you received: 13372
  • MODERATOR
12 years 3 months ago #60798

Hi,

That's great. Now we're moving forward ! :)

Your modification from b to d is good. That avoids the duplicate alias.

You also need to change the lines:
$case.= ' end ';
return $case;

to:
return 'd.price_value';


That should have for effect to change the query piece:
( ( case when b.price_currency_id = '1' then b.price_value when b.price_currency_id = '2' then b.price_value/1 end ) <= 75 )

to:
d.price_value

and thus solve that second query error.

Please Log in or Create an account to join the conversation.

Time to create page: 0.046 seconds
Powered by Kunena Forum