Doctrine2 native query does not hydrate custom type

Question

In a ZF2 - PostgreSQL application, I want to use Doctrine2 native queries to build paginator lists.

So, it works great if any custom Doctrine / Pgsql type is selected. But for one query, I would use data from a custom type.

I have a Doctrine 2 custom type called AlertRecipient declared in PostgreSQL such as below :

CREATE TYPE alert_recipient AS (
    email text,
    status int
);

This type is used in some tables. example in a clients table :

ID (int) | name (varchar) | alerts (alert_recipients[])
1        | John Doe       | {"([email protected], 1), ([email protected], 1)"} 
2        | Foo Bar        | {"([email protected], 1)"}

(alert_recipient[] extends alert_recipient to store a list of many alert_recipientrecords)

This type is linked to an entity, for hydration :

class AlertRecipient
{
    protected $email;

    protected $status;

    // ... with accessors
}

Doctrine type registration is made on the onBoostrap event :

// ...
if (!Type::hasType('alert_recipient'))
{
    Type::addType('alert_recipient', AlertRecipient::class);
}
$platform->registerDoctrineTypeMapping('alert_recipient', 'alert_recipient');

if (!Type::hasType('alert_recipient[]'))
{
    Type::addType('alert_recipient[]', AlertRecipients::class);
}
$platform->registerDoctrineTypeMapping('_alert_recipient', 'alert_recipient[]');
// ...

Custom type doctrine adapter has been written such as the doc http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/cookbook/custom-mapping-types.html

And the query looks like :

$rsm = new ResultSetMappingBuilder($em);

$rsm->addRootEntityFromClassMetadata(Client::class, 'c');
// ... some other data from join entity (e.g)

$query = 'SELECT c.* FROM clients c JOIN ...';
$em->createNativeQuery($query, $rsm);

$results = $query->getResult(NativeQuery::HYDRATE_ARRAY);

Problem is I don't have the same behavior from a find() Doctrine native method, or from the hydration of my native query result.

Debug in custom type adapter :

public function convertToPHPValue($value, AbstractPlatform $platform)
{
    var_dump($value); exit;
    // ...
}

Edit

public function convertToPHPValueSQL($sqlExpr, $platform)
{
    return 'to_json(' . $sqlExpr . ')';
}

From find(), result is correctly hydrated used the AlertRecipient entity : string '[{"email":"[email protected]","status":1}, {"email":"[email protected]","status":1}]'

And from the native query hydration, result['order_emails'] is not hydrated in the AlertRecipient entity : string '{"([email protected], 1), ([email protected], 1)"}'

Then data are not correctly hydrated ...

Thanks for your ideas

Edit

Declaration of alertRecipient property in Client entity :

/* @ORM\Column(type="alert_recipient[]", nullable=true, name="alert_recipients")
 * @Gedmo\Versioned
 */
 protected $alertRecipients = [];

 // ... with accessors

Wilt Wilt · Accepted Answer · 2017-02-06T11:22:32

Did you follow the Doctrine 2 documentation for Custom Mapping Types?

You should methods like convertToPHPValue and convertToDatabaseValue for hydration to work correctly.

You also wrote:

From find(), result is correctly hydrated used the AlertRecipient entity...

As I understood AlertRecipient is a dbal Type class not an Entity class.

Type classes (so your AlertRecipient class) should extend Doctrine\DBAL\Types\Type.

Then you should in the entity definition where you use this type mark the column with the correct type attribute:

/** @Column(type="alert_recipient") */
$alertRecipient;

Since you didn't share all the details (entity definitions where you use your custom type for example) I am not sure where you go wrong, but if you follow the documentation all this should work as expected, so my guess would be you skipped one of those steps.

Doctrine2 native query does not hydrate custom type

1 Answers