Determining which field causes Doctrine to re-query the database

Question

I'm using Doctrine with Symfony in a couple of web app projects.

I've optimised many of the queries in these projects to select just the fields needed from the database. But over time new features have been added and - in a couple of cases - additional fields are used in the code, causing the Doctrine lazy loader to re-query the database and driving the number of queries on some pages from 3 to 100+

So I need to update the original query to include all of the required fields. However, there doesn't seem an easy way for Doctrine to log which field causes the additional query to be issued - so it becomes a painstaking job to sift through the code looking for usage of fields which aren't in the original query.

Is there a way to have Doctrine log when a getter accesses a field that hasn't been hydrated?

Answer 1

I have not had this issue, but just looked at Doctrine_Record class. Have you tried adding some debug output to the _get() method? I think this part is where you should look for a solution:

    if (array_key_exists($fieldName, $this->_data)) {
        // check if the value is the Doctrine_Null object located in self::$_null)
        if ($this->_data[$fieldName] === self::$_null && $load) {
            $this->load();
        }

Answer 2

Just turn on SQL logging and you can deduce the guilty one from alias names. For how to do it in Doctrine 1.2 see this post.

Basically: create a class which extends Doctrine_EventListener:

class QueryDebuggerListener extends Doctrine_EventListener
{
    protected $queries;

    public function preStmtExecute(Doctrine_Event $event)
    {   
        $query = $event->getQuery();
        $params = $event->getParams();

        //the below makes some naive assumptions about the queries being logged
        while (sizeof($params) > 0) {
            $param = array_shift($params); 

            if (!is_numeric($param)) {
                $param = sprintf("'%s'", $param);
            }   

            $query = substr_replace($query, $param, strpos($query, '?'), 1); 
        }   

        $this->queries[] = $query;
    }

    public function getQueries()
    {   
        return $this->queries;
    }
}

And add the event listener:

$c = Doctrine_Manager::connection($conn);
$queryDbg = new QueryDebuggerListener();
$c->addListener($queryDbg);