Adding virtual columns to current table in Doctrine?

Question

I'm using Doctrine 1.2 with Symfony 1.4. Let's say I have a User model, which has one Profile. These are defined as:

User:

• id
• username
• password
• created_at
• updated_at

Profile:

• id
• user_id
• first_name
• last_name
• address
• city
• postal_code

I would normally get data like this:

$query = Doctrine_Query::create()
    ->select('u.id, u.username, p.first_name, p.last_name')
    ->from('User u')
    ->leftJoin('Profile p')
    ->where('u.username = ?', $username);
$result = $query->fetchOne(array(), Doctrine_Core::HYDRATE_ARRAY);
print_r($result);

This would output something like the following:

Array (
    "User" => Array (
        "id" => 1,
        "username" => "jschmoe"
    ),
    "Profile" => Array (
        "first_name" => "Joseph",
        "last_name" => "Schmoe"
    )
)

However, I would like for user to include "virtual" columns (not sure if this is the right term) such that fields from Profile actually look like they're a part of User. In other words, I'd like to see the print_r statement look more like:

Array (
    "User" => Array (
        "id" => 1,
        "username" => "jschmoe",
        "first_name" => "Joseph",
        "last_name" => "Schmoe"
    )
)

Is there a way to do this either via my schema.yml file or via my Doctrine_Query object?

Answer 1

The way to do what you want is to use a custom Hydrator.

class Doctrine_Hydrator_MyHydrator extends Doctrine_Hydrator_ArrayHierarchyDriver
{
    public function hydrateResultSet($stmt)
    {
        $results = parent::hydrateResultSet($stmt);
        $array = array();

        $array[] = array('User' => array(
            'id'         => $results['User']['id'],
            'username'   => $results['User']['username'],
            'first_name' => $results['Profile']['first_name'],
            'last_name'  => $results['Profile']['last_name'],
        ));

        return $array();
    }
}

Then register you hydrator with the connection manager:

$manager->registerHydrator('my_hydrator', 'Doctrine_Hydrator_MyHydrator');

Then you hydrate your query like this:

$query = Doctrine_Query::create()
    ->select('u.id, u.username, p.first_name, p.last_name')
    ->from('User u')
    ->leftJoin('Profile p')
    ->where('u.username = ?', $username);

 $result = $query->fetchOne(array(), 'my_hydrator');
 print_r($result);

/* outputs */
Array (
    "User" => Array (
        "id" => 1,
        "username" => "jschmoe",
        "first_name" => "Joseph",
        "last_name" => "Schmoe"
    )
)

You might have to fines the hyrdator logic a little to get the exact array structure you want. But this the acceptable way to do what you want.