Doctrine 2 - ORM

convertToDatabaseValueSQL() is not honored for DQL query parameters

Details

  • Type: Bug Bug
  • Status: Resolved Resolved
  • Priority: Critical Critical
  • Resolution: Invalid
  • Affects Version/s: Git Master, 2.3.1
  • Fix Version/s: None
  • Component/s: ORM
  • Security Level: All
  • Labels:
    None

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 ... = ?

Issue Links

duplicates

Bug - A problem which impairs or prevents the functions of the product. DDC-2240 Inconsistent querying for parameter type (from ClassMetadata) between using Find/FindBy and DoctrineQL

  • Major - Major loss of function.
  • Resolved - A resolution has been taken, and it is awaiting verification by reporter. From here issues are either reopened, or are closed.

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Matthieu Napoli added a comment -

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

Show
Matthieu Napoli added a comment - Fix proposal: https://github.com/doctrine/doctrine2/pull/574
Hide
Permalink
Matthieu Napoli added a comment -

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.

Show
Matthieu Napoli added a comment - 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.
Hide
Permalink
Benjamin Morel added a comment -

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

Show
Benjamin Morel added a comment - Have you passed the type as the third parameter to setParameter() ? Because this works fine for me!
Hide
Permalink
Matthieu Napoli added a comment -

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?

Show
Matthieu Napoli added a comment - 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?
Hide
Permalink
Benjamin Morel added a comment - - edited

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.

Show
Benjamin Morel added a comment - - edited 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.
Hide
Permalink
Matthieu Napoli added a comment - - edited

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

Show
Matthieu Napoli added a comment - - edited 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

People

  • Assignee:
    Benjamin Eberlei
    Reporter:
    Benjamin Morel
Vote (1)
Watch (2)

Dates

  • Created:
    Updated:
    Resolved: