doctrine-ormtype-conversionnative-sql

How To Utilize Doctrine2 Type Conversion on Native Query COLUMNS


Could not find anything on this-- seems like it should be straight forward though.

So the example the Doctrine2 docs give for type conversion on bound parameters looks like this:

$date = new \DateTime("2011-03-05 14:00:21");
$stmt = $conn->prepare("SELECT * FROM articles WHERE publish_date > ?");
$stmt->bindValue(1, $date, "datetime");
$stmt->execute();

What I want to do is specify the type conversion for one of the columns, but there is nothing in the documents or on StackOverflow that I could find. A pseudo-example of what this might look like:

$stmt = $conn -> prepare("SELECT datetime FROM articles WHERE id = 1");
$stmt -> setType(0, "date_type"); // 0 being the column position, "date_type" being the PHP type to convert to

If anybody knows how to do this, (this is SQL not DQL), I would greatly appreciate. Thank you.


Solution

  • This is not something that works at DBAL level. If you are using NativeSQL Queries in ORM, you can get that kind of conversion through hydration (see the NativeSQL section in the Doctrine ORM documentation) by using the HYDRATE_ARRAY mode and mapping some of the fetched fields to an entity. The fastest solution (if you don't intend to use ORM) is to iterate over the results and applying the type conversion manually by accessing Doctrine\DBAL\Types\Type::getType($someType)->convertToPhpValue($fetchedValue). It could be a valuable addition to ORM to be able to specify a third parameter stating the fetched type in Doctrine\ORM\Query\ResultSetMapping#addScalarResult.