Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-2224

convertToDatabaseValueSQL() is not honored for DQL query parameters

    Details

    • Type: Bug Bug
    • Status: 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

          Activity

          Hide
          Matthieu Napoli added a comment -
          Show
          Matthieu Napoli added a comment - Fix proposal: https://github.com/doctrine/doctrine2/pull/574
          Hide
          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
          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
          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
          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
          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
            • Votes:
              1 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: