ORDER BY QUERY BUILDER ISSUE

[mertdogan]

I hava a test query :
Code:
$this->db->where('1 IS NOT NULL',NULL,FALSE)->WHERE('X>','0')->FROM('X')->ORDER_BY('X','ASC')->count_all_results();

and it is generating error:

Code:
Error Number: 42000/1033

[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

SELECT COUNT(*) AS "numrows" FROM ( SELECT * FROM "X" WHERE 1 IS NOT NULL AND "X" > '0' ORDER BY "X" ASC ) CI_count_all_results

[mertdogan]

Solution:
Add this line in DB_query_builder.php in system/database file line 1381:
if(!empty($this->qb_orderby)&&empty($this->qb_limit)&&empty($this->qb_offset))$this->qb_orderby=null;

[aanbar]

Why would you use order_by with count_all_results in the first place?

[mertdogan]

First of all; i have another problem. I have a script. This script stopes good working when i upgrade from 3.0.2 to 3.0.4 . But in another function; all of database functions seems working normally. When i revert my system folder to the previous function; my script is working as it must be again.

Second one; i check this function because you described there is a bug fix for count_all_results used with order_by clause:
Fixed a bug (#4212) - Query Builder method count_all_results() could fail if an ORDER BY condition is used.

[aanbar]

Looks like you're right, the latest change converts it to a subquery which is breaking your query.

[mertdogan]

Yep;

I found its' solution and wrote my 2. message:

Solution:
Add this line in DB_query_builder.php in system/database file line 1381:
if(!empty($this->qb_orderby)&&empty($this->qb_limit)&&empty($this->qb_offset))$this->qb_orderby=null;