Welcome Guest, Not a member yet? Register   Sign In
Migrating CI3 to CI4, need query->numRows function
#1

There are three other threads on this forum asking some variation of the 'what happened to num_rows' question, but as instructed by includebeer, I am creating a new thread.

I'm migrating a CI3 project to CI4. The old project refers to query->num_rows in about 100 places. This function appears to be completely removed in CI4 according to unspecified "performance issues" as described by kilishan. I would very much like to have a numRows method attached to my query result object and but apparently a pull request to provide this function was rejected. I wasn't sure if I should post in the Feature Request forum. I chose here because I am concerned the feature request will be rejected like it was before so I guess my question is How can extend the database result class so that a query result has a numRows method? I can easily see how I might modify the existing BaseResult or MySQLi/Result classes but I'd like to avoid any changes to CI4's core.

I think there are good reasons to provide this functionality:
* I'm guessing that the vast majority of CodeIgniter projects make use of one of the 3 popular DBMS modules that provide a corresponding function (pg_num_rows, mysqli_num_rows, sqlsrv_num_rows).
* for the DBMSes that support a num_rows function, I'd wager it's a LOT more efficient than builder->countAllResults which, in addition to the code required to modify the builder's query to construct a matching COUNT(*) query/subquery, must send an SQL request to the server and then parse the response. If I'm not mistaken, mysqli_num_rows just checks a memory location for the record count, which seems like a much more efficient operation.
* builder->countAllResults only works if you use the query builder object, and isn't available after a db->query or db->simpleQuery.
* some queries might require a painful table scan or produce a large query result that you might want to iterate one record at a time. Having to run this painful query a second time and/or having to instantiate a very large record set just to count how many records it has seems suboptimal. E.g., imagine if you want to know how many records the query produces specifically to avoid out-of-memory exceptions?
* PDO offers such a function, rowCount.
* it's extremely useful to know if a query you just ran has any records without having to run another query (which might alter the state of your builder object)
* on my busiest websites, the database server (a separate machine) is the busiest component, and often becomes a bottleneck and cost center. It'd be good to ease the burden on the database in any way possible.

I am aware from the PHP PDO documentation that there are some complications with a rowCount function:
Quote:f the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.
I'm also aware the num_rows functions might return different values depending on whether you are using buffered or unbuffered result sets. However, in practice, I'm almost always using this function just to check if a query returned any records or zero records, so an accurate row count is of secondary importance, the primary consideration being whether any records came back or not.
Reply
#2

> How can extend the database result class so that a query result has a numRows method?

It seems there is no way to extend the database result class.
Reply
#3

Can you provide any code demonstrating why/how you are using this? You are performing a SELECT query but can't access the results array to perform a count() in PHP? Which database driver are you using? If MySQL you could probably try $result->resultID->num_rows to access this value.
Reply
#4

(This post was last modified: 12-31-2020, 04:21 AM by seunex.)

It is known as $query->countAllResults()
Reply
#5

PHP Code:
$query->countAllResults(); 
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply
#6

(12-30-2020, 05:38 PM)kenjis Wrote: It seems there is no way to extend the database result class.
This is very disappointing.

(12-30-2020, 10:51 PM)seunex Wrote: It is known as $query->countAllResults()
(12-31-2020, 03:58 AM)InsiteFX Wrote:
PHP Code:
$query->countAllResults(); 
You guys clearly did not read my post. I specifically referred to the differences between countAllResults and numRows.
seunex: countAllResults is a method of the BaseBuilder class, not the BaseResult class.

A new development. It looks like Lonnie (is this kilishan?) has said they would accept a pull request. It should be pretty easy to add the method for the DBMSes that support it.
Reply
#7

Why is it become a problem? If you want to count result from your query, there is count() method from PHP, it's easy to use and don't have to create additional method in database result. Read example below:
PHP Code:
$query $db->query("YOUR QUERY");
$numRows count($query->getResult()); 
Is it not enough for you?
Reply
#8

(01-01-2021, 01:00 AM)adnzaki Wrote: Is it not enough for you?

I can only answer for myself. My take away is that getResult() loops the result set and reads in all the records. In CI3, num_rows() (at least for MySQL) queried mysqli_result::$num_rows and returned that without setting up the resulting records.
Reply
#9

(01-01-2021, 03:14 AM)tgix Wrote:
(01-01-2021, 01:00 AM)adnzaki Wrote: Is it not enough for you?

I can only answer for myself. My take away is that getResult() loops the result set and reads in all the records. In CI3, num_rows() (at least for MySQL) queried mysqli_result::$num_rows and returned that without setting up the resulting records.

It doesn't just loop through the results, it instantiates an object for every single record. This is consuming a lot of memory and CPU cycles just to get some idea of how many records the query returned.
Reply
#10

BaseResult::getNumRows() was added in develop branch at last!
https://github.com/codeigniter4/CodeIgniter4/pull/4049
Reply




Theme © iAndrew 2016 - Forum software by © MyBB