BlogNifty

Release 10 of Cognifty has a nice little database feature that I haven't noticed in other libraries. I'm not saying they don't have them, but they don't really announce useful tools to developers, just the same old stuff about OOP and some directory structure loosely based on LogiCreate.

The feature about which I'm talking is the getUnlimitedCount() method. When working with data items, or active records, or SQL abstraction, or whatever you want to call it, the getUnlimitedCount method runs the query it would normally run, but it selects the total number of records without including the LIMIT or ORDER BY clauses.

Why is this feature useful? It's very useful for figuring out your own pagination routines. Before I posted this blog entry, I double checked Code Igniter (CI) to see if it had such a feature, and show enough it almost sort of does!

What I mean to say is that, it apparently has a similar method, but it's not quite clear exactly how to use it.

$this->db->count_all_results();
 
Permits you to determine the number of rows in a particular Active Record query. 
Queries will accept Active Record restrictors such as where(), or_where(),
like(), or_like(), etc. 
Example:

echo $this->db->count_all_results('my_table');
// Produces an integer, like 25
 
$this->db->like('title', 'match');

$this->db->from('my_table');
echo $this->db->count_all_results();

// Produces an integer, like 17

So, when reading this, it's not entirely clear if I can use the count_all_results() method from a previously executed query or not. Let me explain by showing how Cognifty's method works.

$ticketsLoader = new Cgn_DataItem('csrv_ticket');
$ticketsLoader->andWhere('is_closed',0);

 
$ticketsLoader->hasOne('cgn_user','cgn_user_id','Tuser', 'owner_id');
$ticketsLoader->hasOne('cgn_account','cgn_account_id','Tacct', 'cgn_account_id');

$ticketsLoader->_cols = array('csrv_ticket.*','Tacct.contact_email');
$ticketsLoader->orderBy('created_on','DESC');

$ticketsLoader->limit(20);
 
//returns a number like 25
$totalRec  = $ticketsLoader->getUnlimitedCount();
//returns database records contained in the bounds of the limit, sorted by the order by clause
$newTickets = $ticketsLoader->find();

//this is the SQL which is run
//SELECT csrv_ticket.*,Tacct.contact_email 
//       FROM csrv_ticket 
//       LEFT JOIN `cgn_user` AS Tuser ON csrv_ticket.owner_id = Tuser.`cgn_user_id` 
//       LEFT JOIN `cgn_account` AS Tacct ON csrv_ticket.cgn_account_id = Tacct.`cgn_account_id` 
//       WHERE is_closed = 0 
//       ORDER BY created_on LIMIT 0, 20

//and the getUnlimitedCount ...
//SELECT count(*) as total_rec 
//       FROM csrv_ticket 
//       LEFT JOIN `cgn_user` AS Tuser ON csrv_ticket.owner_id = Tuser.`cgn_user_id` 
//       LEFT JOIN `cgn_account` AS Tacct ON csrv_ticket.cgn_account_id = Tacct.`cgn_account_id` 
//       WHERE is_closed = 0

In this code example, we are selecting tickets (from the custom service app) where they are not yet closed. We can join the user and account tables with aliases of "Tuser" and "Tacct". Order by "created_on" and limit 20 round out a standard paginated result set page.

Now, the getUnlimitedCount() method returns the total number of records which match the exact query without using the limit and order by clauses. The data item itself is completely reusable. As you can see, the next method is the usual find call which returns an array of data items. So you can use getUnlimitedCount both before or after a regular query to the database.

The re-usability of the $this->db object is what is not apparent from the code igniter documentation. Must I reinitialize the database helper every time I perform a query? Exactly which parts of the statement does the helper keep between queries?

This is just another problem that rears its ugly head when over-using "helpers" and unnatural coding practices. When the programmer never instantiates any objects with the "new" operator, things become confusing and programming tends to become a sort of black box magic trick in which the developer is the unwitting audience member constantly trying to peak inside the magician's pocket.