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 […]

This article was posted by Independent Software, a website and database application development company based in Maputo, Mozambique. Our website offers regular write-ups on technical and design issues, ranging from details at code level to 3D Studio Max rendering. Read more about Independent Software's philosophy, or get in touch with Independent Software.

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
  • One query to rule them all and in the darkness bind them

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.

Comments

1 One Response to “Developing a generic count() method for Idiorm/Paris”
  1. Lena says:

    Thanks! Your comment was very helpful for me!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">