[Skip to main content](#vessel-content) # Internal server error using filters ## - **[webarts](/forum/user/144-webarts "View webarts's Profile")** - [![webarts's Avatar](https://www.hikashop.com/media/kunena/core/svg/person.svg)](/forum/user/144-webarts "View webarts's Profile") Topic Author - [ Offline](/forum/user/144-webarts) Less More - Posts: 149 - Thank you received: 0 - - [](http://www.webarts.com.cy) - 14 years 4 months ago [\#54782](#54782) by [webarts](/forum/user/144-webarts "View webarts's Profile") *[Internal server error using filters](/forum/3-bug-report/54782-internal-server-error-using-filters#54782 "Hello, We used a filter by price module and when we choose to filter products that match price 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?")* was created by *[webarts](/forum/user/144-webarts "View webarts's Profile")* 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](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzMtYnVnLXJlcG9ydC81NDc4Mi1pbnRlcm5hbC1zZXJ2ZXItZXJyb3ItdXNpbmctZmlsdGVycz90bXBsPW1hcmtkb3du) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. - **[nicolas](/forum/user/63-nicolas "View nicolas's Profile")** - [![nicolas's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar63.png?1793428657)](/forum/user/63-nicolas "View nicolas's Profile") - [ Offline](/forum/user/63-nicolas) Less More - Posts: 86541 - Thank you received: 14304 - - 14 years 4 months ago [\#54828](#54828) by [nicolas](/forum/user/63-nicolas "View nicolas's Profile") Replied by *[nicolas](/forum/user/63-nicolas "View nicolas's Profile")* on topic *[Re: Internal server error using filters](/forum/3-bug-report/54782-internal-server-error-using-filters#54828 "Hello, We used a filter by price module and when we choose to filter products that match price 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?")* 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](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzMtYnVnLXJlcG9ydC81NDc4Mi1pbnRlcm5hbC1zZXJ2ZXItZXJyb3ItdXNpbmctZmlsdGVycz90bXBsPW1hcmtkb3du) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. - **[webarts](/forum/user/144-webarts "View webarts's Profile")** - [![webarts's Avatar](https://www.hikashop.com/media/kunena/core/svg/person.svg)](/forum/user/144-webarts "View webarts's Profile") Topic Author - [ Offline](/forum/user/144-webarts) Less More - Posts: 149 - Thank you received: 0 - - [](http://www.webarts.com.cy) - 14 years 4 months ago [\#54841](#54841) by [webarts](/forum/user/144-webarts "View webarts's Profile") Replied by *[webarts](/forum/user/144-webarts "View webarts's Profile")* on topic *[Re: Internal server error using filters](/forum/3-bug-report/54782-internal-server-error-using-filters#54841 "Hello, We used a filter by price module and when we choose to filter products that match price 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?")* 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](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzMtYnVnLXJlcG9ydC81NDc4Mi1pbnRlcm5hbC1zZXJ2ZXItZXJyb3ItdXNpbmctZmlsdGVycz90bXBsPW1hcmtkb3du) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. - **[nicolas](/forum/user/63-nicolas "View nicolas's Profile")** - [![nicolas's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar63.png?1793428657)](/forum/user/63-nicolas "View nicolas's Profile") - [ Offline](/forum/user/63-nicolas) Less More - Posts: 86541 - Thank you received: 14304 - - 14 years 4 months ago [\#54926](#54926) by [nicolas](/forum/user/63-nicolas "View nicolas's Profile") Replied by *[nicolas](/forum/user/63-nicolas "View nicolas's Profile")* on topic *[Re: Internal server error using filters](/forum/3-bug-report/54782-internal-server-error-using-filters#54926 "Hello, We used a filter by price module and when we choose to filter products that match price 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?")* 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: Code: $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: Code: $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](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzMtYnVnLXJlcG9ydC81NDc4Mi1pbnRlcm5hbC1zZXJ2ZXItZXJyb3ItdXNpbmctZmlsdGVycz90bXBsPW1hcmtkb3du) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. - **[webarts](/forum/user/144-webarts "View webarts's Profile")** - [![webarts's Avatar](https://www.hikashop.com/media/kunena/core/svg/person.svg)](/forum/user/144-webarts "View webarts's Profile") Topic Author - [ Offline](/forum/user/144-webarts) Less More - Posts: 149 - Thank you received: 0 - - [](http://www.webarts.com.cy) - 14 years 2 months ago [\#60697](#60697) by [webarts](/forum/user/144-webarts "View webarts's Profile") Replied by *[webarts](/forum/user/144-webarts "View webarts's Profile")* on topic *[Re: Internal server error using filters](/forum/3-bug-report/54782-internal-server-error-using-filters#60697 "Hello, We used a filter by price module and when we choose to filter products that match price 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?")* 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](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzMtYnVnLXJlcG9ydC81NDc4Mi1pbnRlcm5hbC1zZXJ2ZXItZXJyb3ItdXNpbmctZmlsdGVycz90bXBsPW1hcmtkb3du) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. - **[nicolas](/forum/user/63-nicolas "View nicolas's Profile")** - [![nicolas's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar63.png?1793428657)](/forum/user/63-nicolas "View nicolas's Profile") - [ Offline](/forum/user/63-nicolas) Less More - Posts: 86541 - Thank you received: 14304 - - 14 years 2 months ago [\#60780](#60780) by [nicolas](/forum/user/63-nicolas "View nicolas's Profile") Replied by *[nicolas](/forum/user/63-nicolas "View nicolas's Profile")* on topic *[Re: Internal server error using filters](/forum/3-bug-report/54782-internal-server-error-using-filters#60780 "Hello, We used a filter by price module and when we choose to filter products that match price 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?")* 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](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzMtYnVnLXJlcG9ydC81NDc4Mi1pbnRlcm5hbC1zZXJ2ZXItZXJyb3ItdXNpbmctZmlsdGVycz90bXBsPW1hcmtkb3du) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. - **[webarts](/forum/user/144-webarts "View webarts's Profile")** - [![webarts's Avatar](https://www.hikashop.com/media/kunena/core/svg/person.svg)](/forum/user/144-webarts "View webarts's Profile") Topic Author - [ Offline](/forum/user/144-webarts) Less More - Posts: 149 - Thank you received: 0 - - [](http://www.webarts.com.cy) - 14 years 2 months ago [\#60791](#60791) by [webarts](/forum/user/144-webarts "View webarts's Profile") Replied by *[webarts](/forum/user/144-webarts "View webarts's Profile")* on topic *[Re: Internal server error using filters](/forum/3-bug-report/54782-internal-server-error-using-filters#60791 "Hello, We used a filter by price module and when we choose to filter products that match price 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?")* 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](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzMtYnVnLXJlcG9ydC81NDc4Mi1pbnRlcm5hbC1zZXJ2ZXItZXJyb3ItdXNpbmctZmlsdGVycz90bXBsPW1hcmtkb3du) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. - **[nicolas](/forum/user/63-nicolas "View nicolas's Profile")** - [![nicolas's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar63.png?1793428657)](/forum/user/63-nicolas "View nicolas's Profile") - [ Offline](/forum/user/63-nicolas) Less More - Posts: 86541 - Thank you received: 14304 - - 14 years 2 months ago [\#60798](#60798) by [nicolas](/forum/user/63-nicolas "View nicolas's Profile") Replied by *[nicolas](/forum/user/63-nicolas "View nicolas's Profile")* on topic *[Re: Internal server error using filters](/forum/3-bug-report/54782-internal-server-error-using-filters#60798 "Hello, We used a filter by price module and when we choose to filter products that match price 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?")* Hi, That's great. Now we're moving forward ! ![:)](/media/kunena/emoticons/3.png ":)") 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](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzMtYnVnLXJlcG9ydC81NDc4Mi1pbnRlcm5hbC1zZXJ2ZXItZXJyb3ItdXNpbmctZmlsdGVycz90bXBsPW1hcmtkb3du) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. Board Categories HikaShop Install & Update Product & Category Display Content Creation Sales and Orders Orders management Reports & Statistics Customers Checkout Payment Methods Shipping Methods Import & Export (CSV, XLS…) Specific Features Affilation Taxes Wishlist Vote Carousel & Slider Mass Actions Filters & Search Discount & Coupon Feature Request Integrations Showcase General Talk about HikaShop Development HikaShop 3.0 Beta Translation Commercial Jobs HikaShop Components HikaMarket Market: Feature requests Market: How to? Market: Bug Report HikaSerial General talk about HikaSerial Serial: Bug Report Serial: How to? Support en Français de HikaSerial (French Support) HikaSubscription HikaAuction Auction: Feature requests Auction: How to? Auction: Bug report Hikashop around the world Support en Français (French Support) Russian FAQ. Курс выживания с Hikashop. Частые вопросы и о Расширения Hikashop Наши сайты на Hikashop 日本語版サポート(Japanese Support) Thai German Archives Bug Report How to? [](/forum/topics/feed/mode-topics?format=feed&type=rss) Time to create page: 0.064 seconds [Powered by](/forum/credits) [Kunena Forum](https://www.kunena.org)