[DDC-2224] convertToDatabaseValueSQL() is not honored for DQL query parameters Created: 05/Jan/13  Updated: 04/Apr/13  Resolved: 04/Apr/13

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: Git Master, 2.3.1
Fix Version/s: None
Security Level: All

Type: Bug Priority: Critical
Reporter: Benjamin Morel Assignee: Benjamin Eberlei
Resolution: Invalid Votes: 1
Labels: None

Attachments: File DDC2224Test.php    
Issue Links:
Duplicate
duplicates DDC-2240 Inconsistent querying for parameter t... Resolved

 Description   

Following discussion on Google Groups:
https://groups.google.com/d/msg/doctrine-dev/-/gG-VGiAGQiMJ

When using a mapping type which declares convertToDatabaseValueSQL(), this method is not invoked when passing a value as parameter to a DQL query.

For example, if I declare a mapping type MyType:

class MyType extends \Doctrine\DBAL\Types\Type
{
    public function canRequireSQLConversion()
    {
        return true;
    }

    public function convertToDatabaseValueSQL($sqlExpr, AbstractPlatform $platform)
    {
        return sprintf('FUNCTION(%s)', $sqlExpr);
    }

    // ...
}

And pass a parameter with this type to a DQL query:

$query = $em->createQuery('SELECT e FROM Entity e WHERE e.field = :field');
$query->setParameter('field', $value, 'MyType');

I would expect the following SQL to be generated:

SELECT ... WHERE ... = FUNCTION(?)

But the current SQL generated is the following:

SELECT ... WHERE ... = ?


 Comments   
Comment by Matthieu Napoli [ 08/Feb/13 ]

Fix proposal: https://github.com/doctrine/doctrine2/pull/574

Comment by Matthieu Napoli [ 08/Feb/13 ]

It turns out convertToDatabaseValue() is not called as well.

For example:

class MyType extends \Doctrine\DBAL\Types\Type
{
    public function convertToDatabaseValue($value, AbstractPlatform $platform)
    {
        return serialize($value);
    }

    // ...
}

The SQL generated should not change, but the parameter should go through "convertToDatabaseValue", which is not the case.

Comment by Benjamin Morel [ 08/Feb/13 ]

Have you passed the type as the third parameter to setParameter() ?
Because this works fine for me!

Comment by Matthieu Napoli [ 08/Feb/13 ]

You are right!

But shouldn't the Type mapping be taken into account anyway? I makes sense to me at least (a column has a type, when I specify a parameter for this column, it is for this type), but maybe that was just designed that way?

Comment by Benjamin Morel [ 08/Feb/13 ]

That would make sense, but I think that because of the flexibility of DQL, it's not always obvious what type you want to use.
Say you have some complex expression like:

WHERE entity.field = SomeDQLFunction(:value, entity.otherField)

Then we can't be sure what mapping type to use here (unless we can infer it from SomeDQLFunction, but this is yet another story).
Though I do agree that would be great to have the type inferred automatically when you do a simple:

WHERE entity.field = :value

Here, there's no ambiguity, and if entity.field has a custom mapping type, then I can't see a reason why it shouldn't be applied to the parameter by default.

Comment by Matthieu Napoli [ 08/Feb/13 ]

I made a failing test case, I'll see if I can work on that.

I will open a separate ticket for this.

Edit: http://www.doctrine-project.org/jira/browse/DDC-2290

Generated at Fri May 24 05:43:24 UTC 2013 using JIRA 5.2.7#850-sha1:b2af0c8dc8537b36121c6a579fabbdf79fc919e5.