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.

Answer 2

I dont use array hydration much so maybe i havent noticed but i could have sworn the structure youre looking for is whats produced by:

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

Answer 3

Use setHydrationMode(Doctrine_Core::HYDRATE_SCALAR) instead.

It fetched results in rectangular manner and uses aliases of tables as prefixes and aliases of column as name. So all colunm would be accesible trough tableAlias_columnAlias.

Answer 4

Your example is not entirely correct: if you fetch from the DQL you specified above, you will only get a User object, not an array with a User and a Profile. If you want to access the profile, you will have to do it through the relationship set up in your User, if you specified one in the schema.

Nevertheless, there is a solution to your problem. In DQL, if you use column aliases when selecting things, the aliased columns will be accessible in every instance returned as if they were part of the model. So let's say you do the following:

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

$result = $query->fetchOne();

In this case the result will be a single User object, with all the properties of a user plus 2 extra properties: prof_first_name and prof_last_name. Please note that while I used "prof_" as a prefix, it has no semantic meaning, I could have used anything else as well. So you should be able to do the following:

echo "User " . $result->username ."'s first name is " . $result->prof_first_name;