[eluser]Predator[/eluser]
Hello Team,
The problem I am having is that I am building queries with limit, however I also need to know what is the total result of records that the db would return if there was no limit. As you may guess it is for pagination purposes.
Digging around I couldn't find a way to get the data in any other way other than constructing the active record query and execute the count_all_results() before i apply the limit options to the query and then execute db->get().
The issue I am facing is that count_all_results() is calling _reset_select() internally and thus basically destroying my previously written active record statements.
Is there any special reason why this is happening?
Or better, as I am new to CI.. maybe there is a proper why of achieving what I need?
Regards
[eluser]PhilTem[/eluser]
It's because 'count_all_results()' returns an integer as function-call result. Therefore it adds an
Code: COUNT(*) as total_results // or some other variable
to your query and internally performs a
Code: $query = $this->db->get();
if ( $query->num_rows() > 0 )
{
$row = $query->row();
return $row->total_results();
}
return 0;
At least I'd write the code somehow this way.
The call for _reset_select() is because the coder of this function probably wanted to ensure there are no other selects that may destroy the query except the 'COUNT(*) as total_rows'.
If you want to know how many rows you have without limit, simply use
Code: $this->db->count_all()
which returns the total number of rows.
Hope I shed a little light 
[eluser]Predator[/eluser]
Correct me if I am wrong but count_all() requires a mandatory parameter 'table' to be passed... and this is not the case I am dealing with.. I need to know the total results that would be returned by the query rather than the total records in a table, also the query can be executed agains several tables.
I will check on that later when I get home..
Hopefully somebody can come up with some ideas.. otherwise I appears to me that codeigniter implementation of the active record have some fundamental issues.
Regards
[eluser]WanWizard[/eluser]
count_all() returns the total number of records in a table. So it has to reset the query to remove any defined clauses that could limit the result.
It is also a stand-alone method:
Code: $this->db->where('this', 'that');
// this will not use the where clause defined!
$count = $this->db->count_all('mytable');
count_all_results() doesn't to that, so it returns the count including any clauses you make have already defined.
Code: $this->db->where('this', 'that');
// this will use the defined where clause!
$count = $this->db->from('mytable')->count_all_results();
[eluser]Predator[/eluser]
Thank you for the reply..
There two issues I see:
1. In a normal real life scenario I would normally need to total results that would be returned without the limit clause PLUS the records with the limit clause. This is how pagination works, right?
2. It is very hard for me to imagine a modern application that can be satisfied by queries ran only against one table.
Also in the example with count_all_results() above, I dont see how I can get the data without writing the same query declarations one more time plus the limit declaration and run the query again to fetch the data.
To go around the need of typing the query declarations twice I have created my_count_all_results() function and commented out the _reset_select() call. This is letting me to get the total results and then only add the limit declaration and fetch the data.
To summarize, the ideal solution would be if active record returns also a property called total_results when there is a limit clause defined.
Refer to following documentation of mysql to get better idea of what is needed.
Quote: FOUND_ROWS()
A SELECT statement may include a LIMIT clause to restrict the number of rows the server returns to the client. In some cases, it is desirable to know how many rows the statement would have returned without the LIMIT, but without running the statement again. To obtain this row count, include a SQL_CALC_FOUND_ROWS option in the SELECT statement, and then invoke FOUND_ROWS() afterward:
mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
-> WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();
The second SELECT returns a number indicating how many rows the first SELECT would have returned had it been written without the LIMIT clause.
In the absence of the SQL_CALC_FOUND_ROWS option in the most recent successful SELECT statement, FOUND_ROWS() returns the number of rows in the result set returned by that statement. If the statement includes a LIMIT clause, FOUND_ROWS() returns the number of rows up to the limit. For example, FOUND_ROWS() returns 10 or 60, respectively, if the statement includes LIMIT 10 or LIMIT 50, 10.
The row count available through FOUND_ROWS() is transient and not intended to be available past the statement following the SELECT SQL_CALC_FOUND_ROWS statement. If you need to refer to the value later, save it:
mysql> SELECT SQL_CALC_FOUND_ROWS * FROM ... ;
mysql> SET @rows = FOUND_ROWS();
If you are using SELECT SQL_CALC_FOUND_ROWS, MySQL must calculate how many rows are in the full result set. However, this is faster than running the query again without LIMIT, because the result set need not be sent to the client.
SQL_CALC_FOUND_ROWS and FOUND_ROWS() can be useful in situations when you want to restrict the number of rows that a query returns, but also determine the number of rows in the full result set without running the query again. An example is a Web script that presents a paged display containing links to the pages that show other sections of a search result. Using FOUND_ROWS() enables you to determine how many other pages are needed for the rest of the result.
The use of SQL_CALC_FOUND_ROWS and FOUND_ROWS() is more complex for UNION statements than for simple SELECT statements, because LIMIT may occur at multiple places in a UNION. It may be applied to individual SELECT statements in the UNION, or global to the UNION result as a whole.
The intent of SQL_CALC_FOUND_ROWS for UNION is that it should return the row count that would be returned without a global LIMIT. The conditions for use of SQL_CALC_FOUND_ROWS with UNION are:
The SQL_CALC_FOUND_ROWS keyword must appear in the first SELECT of the UNION.
The value of FOUND_ROWS() is exact only if UNION ALL is used. If UNION without ALL is used, duplicate removal occurs and the value of FOUND_ROWS() is only approximate.
If no LIMIT is present in the UNION, SQL_CALC_FOUND_ROWS is ignored and returns the number of rows in the temporary table that is created to process the UNION.
Beyond the cases described here, the behavior of FOUND_ROWS() is undefined (for example, its value following a SELECT statement that fails with an error).
Important
FOUND_ROWS() is not replicated reliably, and should not be used with databases that are to be replicated.
[eluser]CroNiX[/eluser]
Keep in mind that Active Record has to work on many database platforms and perform the same, not just mysql. It's pretty trivial to use a COUNT(field) in conjunction with joins, where's, etc. on tables that are properly indexed to find the total in the result set for pagination boundaries, or whatever, especially if you use query caching.
[eluser]Predator[/eluser]
Guys,
I am not playing stupid here... English is not my native language, but I think the issue is clearly described in the opening post.
The active record implementation does not allow for getting total results a given query would produce without writing the query two times. Because no matter are you executing it for records with limit set or for total results without limit being set. The query is always destroyed after it calls the database.
I really cannot simplify it further. Sorry if stills is unclear what I am talking about.
I am not saying CI needs to support some exotic type of feature that only mysql have, I also dont have a problem running the query two times(one without limit set and one after that with the limit set) I have a major problem understanding why do I have to write the whole query active record declaration two times.
Current implementation of active record is more of burden than any ease.
I have commented out _reset_select() on count_all_results() in another function just to test it AND it works just fine IMO.
So the question remains: Why does active record count_all_results() is calling _reset_select() ?
Regards
[eluser]CroNiX[/eluser]
You are free to contribute and fix it and submit a pull request on github if you feel strong enough about it.
[eluser]Aken[/eluser]
The reason _reset_select() is called is because count_all_results() is a query executing method, in that by the time that method is called, all parameters for the query should be set (selects, wheres, joins, etc) and the query is executed. After queries are executed, database properties are reset in order to keep any erroneous properties from continuing on to the next query.
Consider this example. I want to select a couple columns from table A.
Code: $this->db->select('id, column')->from('table_a')->get();
Then, right after I run this query, I want to select different columns from a different table.
Code: $this->db->select('id, other_col')->from('table_b')->get();
If reset methods are not called by query executing methods (get() in this example), then the properties / data from the first query is added onto the second query, thus potentially throwing errors or giving me results I'm not expecting. count_all_results() executes a query, then resets the database class so it's ready for whatever I throw at it next. Go ahead - try running count_all_results() without the reset, and then run another simple query after it and see what happens.
Due to this, it is perfectly normal (not ideal, but normal) to generate the same query twice in order to return both the total results and the limit/offset chunk. You can prevent redundant code by creating a protected function that runs the initial parts of the query (select(), where(), join(), etc), and then add your limits or count_all_results() as necessary. Or, as you did, extending the database class and adding a custom function is also suitable.
Expanding the MySQL adapter to provide the SQL_CALC_FOUND_ROWS feature would be nifty, but is not likely to happen unless other databases have similar functionalities. The point of active record is to keep code universal regardless of the DB adapter, so including proprietary MySQL functionality only is not high on the list of things to add.
[eluser]Predator[/eluser]
I totally agree with you on all points besides the idea of isolating queries in separate methods, I've gone this way before and the reason I am using AR is to avoid it on first place, as AR not only is database independent but it is also shorter and nicer to write IMO.
But now I think that each and every time a limit clause is added to the query the result object should contain property holding the actual total records. 99% of the times anyone is using limit clause is with conjunction to some kind of paging and I am not aware of any paging that can work without total results. AR can handle the different dbs in their own way regardless of exotic features being supported or not by all. And still you would require modification to protected method count_all at least to trigger the reset based on parameter.
|
