DEVELOPING A GENERIC COUNT() METHOD FOR IDIORM/PARIS
The minimalistic object relational mapping libraries for PHP, Idiorm and Paris, are wonderful stuff. They have a very small footprint and make your code a joy to read. They allow you to build queries using method chaining, avoiding tedious string concatenation. They’re safer than string concatenation too, since they use parameterized PDO queries under the hood.
And yet, minimalism comes at its own price. Not everything is possible with Idiorm/Paris. Still, whatever seems impossible could very well result from my not understanding the libraries’ potential, or SQL for that matter.
Here’s one problem I ran into. In a large web application, I had numerous places where I needed to show a table with paging. This would require two queries:
- One query to count the total number of records in the table, using COUNT
- One query to get the set of records for the current page, using LIMIT and OFFSET
The query that populates the table can be quite complex, filtering stuff with GROUP BYs and HAVINGs and whatnot. For instance, I have a question table and an answer table, where questions have zero or more answers. Here is a query that yields all questions with at least one answer:
I have several of these filters, so I use Paris’s filter trick to factor the filter out, and add LIMIT/OFFSET bits for the table paging.
What remains now is counting the number of records in the entire table. I’ll need to do this for whatever filter is applied. This seems reasonable:
However, this yields a completely wrong result. Idiorm simply removes the field names from the SELECT query, and puts in COUNT(*) AS TOTAL which does not play nicely with the GROUP/HAVING going on. What it should be doing is counting the actual number of results.
I figured that a nested query would do what I need, and would be usable with any subquery:
Therefore, I added a new method to Idiorm that does exactly that:
From now on, I can do:
…which yields the correct result.
If anyone has a solution that uses plain Idiorm/Paris, I’d be very interested to know. It’s possible that my solution is heavy on MySQL because of the subquery, but it’s a hack that’s at least applicable to any subquery, thus solving my counting problems fully.