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:

Model::factory("Question")
  ->select("question.*")
  ->left_outer_join("answer", array("answer.question_id", "=", "question.id"))
  ->group_by("question.id")
  ->having_raw("COUNT(answer.id) > 0")
  ->order_by_desc("question.created")
  ->find_all();

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.

Model::factory("Question")
  ->filter("filter_at_least_one_answer")
  ->limit(PAGESIZE)
  ->offset(($this->page-1)*PAGESIZE);

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:

Model::factory("Question")
  ->filter("filter_at_least_one_answer")
  ->count();

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:

SELECT COUNT(*) AS total
FROM (SELECT ...) AS subquery

Therefore, I added a new method to Idiorm that does exactly that:

public function wrapcount() 
{
  // Build the inner select
  $query = $this->_build_select();
  
  // Add a count wrapper around it
  $query = "SELECT COUNT(*) AS wrapcount FROM ({$query}) AS subquery LIMIT 1";
        	
  // Execute the statement:
  self::_execute($query, $this->_values, $this->_connection_name);
  $statement = self::get_last_statement();

  // Get a single row
  $row = $statement->fetch(PDO::FETCH_ASSOC);

  // reset Idiorm after executing the query
  $this->_values = array();
  $this->_result_columns = array('*');
  $this->_using_default_result_columns = true;          

  // Return the count:
  return $row["wrapcount"];
}

From now on, I can do:

$total = Model::factory("Question")
  ->filter("filter_at_least_one_answer")
  ->wrapcount();

…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.