Adding custom data type (geometry) in Doctrine 2.1.7. Method canRequireSQLConversion() is not called

Question

I am trying to add Geometry type to Doctrine. My Doctrine DBAL version and ORM versions are 2.1.7.

I tried to follow the instructions here: Doctrine 2 Types - Custom Mapping Types.

I successfully created the new datatype, but I have problems with convertToPHPValueSQL method. I want function ST_AsText(' .. ') to always be called when getting the geometry column from database (database is PostgreSQL 9.1 + PostGIS 2.0.0).

Doctrine DBAL 2.1 documentation says like this:

The job of Doctrine-DBAL is to transform your type into SQL declaration. You can modify the SQL declaration Doctrine will produce. At first, you must to enable this feature by overriding the canRequireSQLConversion method:

<?php
public function canRequireSQLConversion()
{
    return true;
}  

Then you override the methods convertToPhpValueSQL and convertToDatabaseValueSQL :

<?php
public function convertToPHPValueSQL($sqlExpr, $platform)
{
    return 'MyMoneyFunction(\''.$sqlExpr.'\') ';
}

public function convertToDatabaseValueSQL($sqlExpr, AbstractPlatform $platform)
{
    return 'MyFunction('.$sqlExpr.')';
}

Now we have to register this type with the Doctrine Type system and hook it into the database platform:

<?php
Type::addType('money', 'My\Project\Types\MoneyType');
$conn->getDatabasePlatform()->registerDoctrineTypeMapping('MyMoney', 'money');

I did like this (lot of code is placeholder code, but if I did something stupid, all advice is welcome):

<?php

namespace Minupeenrad\Types;

use Doctrine\DBAL\Types\Type;
use Doctrine\DBAL\Platforms\AbstractPlatform;

/**
 * Class for database column "geometry".
 *
 * @author Rauni Lillemets
 */
class GeometryType extends Type {
    const GEOMETRY = 'geometry';
    const SRID = 3301;

    public function getSqlDeclaration(array $fieldDeclaration, AbstractPlatform $platform) {
        return 'geometry';
    }

    //Should create WKT object from WKT string. (or leave as WKT string)
    public function convertToPHPValue($value, AbstractPlatform $platform) {
        return $value; //+ 
    }

    //Should create WKT string from WKT object. (or leave as WKT string)
    public function convertToDatabaseValue($value, AbstractPlatform $platform) {
        return $value; //+
    }

    public function getName() {
        return self::GEOMETRY;
    }

    public function canRequireSQLConversion() {
        return true;
    }

    //Should give WKT
    public function convertToPHPValueSQL($sqlExpr, $platform) {
        return 'ST_AsText(\''.$sqlExpr.'\') '; //+
    }

    //Should create WKB
    public function convertToDatabaseValueSQL($sqlExpr, AbstractPlatform $platform) {
        return 'ST_GeomFromText(\''.$sqlExpr.'\', '.self::SRID.')'; //+
    }
}

Now I added Entity that uses this column:

<?php
namespace Minupeenrad\Entities;

/**
 * Field
 *
 * @author Rauni Lillemets
 * @Entity
 * @Table(name="myfields.fields")
 */
class Field extends GeometryObject {
    /**
     * @Id
     * @Column(type="integer")
     * @GeneratedValue
     */
    private $id;

    /** 
     * @ManyToOne(targetEntity="User") 
     */
    private $user;

    /**
     * @Column(type = "string", length = "40")
     */
    private $fieldNumber;

    public function getId() {
        return $this->id;
    }

    public function getUser() {
        return $this->user;
    }

    public function setUser($user) {
        $this->user = $user;
    }

    public function getFieldNumber() {
        return $this->fieldNumber;
    }

    public function setFieldNumber($fieldNumber) {
        $this->fieldNumber = $fieldNumber;
    }
}
?>

But if I do like this:

$entity = $em->find('\Minupeenrad\Entities\Field', 1);

Doctrine does SQL request to database like this:

SELECT t0.id AS id1, t0.fieldNumber AS fieldnumber2, t0.geometry AS geometry3, t0.user_id AS user_id4
FROM myfields.fields t0
WHERE t0.id = ?

Doctrine does not use my convertToPHPValueSQL method, although canRequireSQLConversion() returns true. Furthermore, I added some debug code to see if canRequireSQLConversion() is even called, and it is not called. What am I doing wrong?

PS: I tried to search Stack Overflow, but I only came up with GIS extension for Doctrine 2, which links to Doctrine 2.1.x manual that I already read.

EDIT: I will read here: http://docs.doctrine-project.org/en/latest/cookbook/advanced-field-value-conversion-using-custom-mapping-types.html

EDIT2: Fixed function getSqlDeclaration(), that was wrong in my code. Added comments.

It seems like a more complete tutorial.

Answer 1

Found the answer.

In Doctrine 2.1.7, if I used $em->find(), eventually BasicEntityPersister()_getSelectColumnSQL() was called. It has following code: (taken from https://github.com/doctrine/doctrine2/blob/2.1.x/lib/Doctrine/ORM/Persisters/BasicEntityPersister.php)

/**
 * Gets the SQL snippet of a qualified column name for the given field name.
 *
 * @param string $field The field name.
 * @param ClassMetadata $class The class that declares this field. The table this class is
 * mapped to must own the column for the given field.
 * @param string $alias
 */
protected function _getSelectColumnSQL($field, ClassMetadata $class, $alias = 'r')
{
    $columnName = $class->columnNames[$field];
    $sql = $this->_getSQLTableAlias($class->name, $alias == 'r' ? '' : $alias) . '.' . $class->getQuotedColumnName($field, $this->_platform);
    $columnAlias = $this->_platform->getSQLResultCasing($columnName . $this->_sqlAliasCounter++);
    $this->_rsm->addFieldResult($alias, $columnAlias, $field);

    return "$sql AS $columnAlias";
}

This code obviously does not respect method "canRequireSQLConversion"

In latest Doctrine version, 2.3.1 (see https://github.com/doctrine/doctrine2/blob/2.3/lib/Doctrine/ORM/Persisters/BasicEntityPersister.php):

/**
 * Gets the SQL snippet of a qualified column name for the given field name.
 *
 * @param string $field The field name.
 * @param ClassMetadata $class The class that declares this field. The table this class is
 * mapped to must own the column for the given field.
 * @param string $alias
 */
protected function _getSelectColumnSQL($field, ClassMetadata $class, $alias = 'r')
{
    $sql = $this->_getSQLTableAlias($class->name, $alias == 'r' ? '' : $alias)
         . '.' . $this->quoteStrategy->getColumnName($field, $class, $this->_platform);
    $columnAlias = $this->getSQLColumnAlias($class->columnNames[$field]);

    $this->_rsm->addFieldResult($alias, $columnAlias, $field);

    if (isset($class->fieldMappings[$field]['requireSQLConversion'])) {
        $type = Type::getType($class->getTypeOfField($field));
        $sql = $type->convertToPHPValueSQL($sql, $this->_platform);
    }

    return $sql . ' AS ' . $columnAlias;
}

So the answer is to update my ORM.