Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-1503

Add support for executing custom SQL functions on custom types

    Details

    • Type: New Feature New Feature
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 2.2
    • Component/s: None
    • Security Level: All
    • Labels:
      None

      Description

      I have created a custom type called point but need a way to have a function called on columns of this type when fetching / updating / inserting entities.

      For example:

      Consider I have an entity which has a column called the_geom

      This is associated with my custom type point

      A basic select statement for this entity is as follows:

      SELECT id, name, the_geom FROM points where id = 1

      The problem is the_geom is encoded and on the PHP side I want it in human readable form. I would like a way to specify custom functions on SELECT / INSERT / UPDATE for columns of this custom type.

      When selecting my entity I would like to execute the function ST_AsText(the_geom)

      SELECT id, ST_AsText(the_geom) FROM points where id = 1

      The same goes for updates and inserts. I would like to surround the column SQL with other custom functions, to encode my human readable text back to the encoded value for the DB.

      Possibly this could be integrated into Doctrine\DBAL\Types\Type ?

      // Modify the column SQL for insert
      public function getSqlInsertColumn(....)
      
      // Modify the column SQL for update
      public function getSqlUpdateColumn(....)
      
      // Modify the column SQL for select
      public function getSqlSelectColumn(....)
      
      // A possible example of getSqlSelectColumn(...)
      public function getSqlSelectColumn($doctrineSql)
      {
          // $doctrineSql == "p0_.the_geom"
          return 'ST_AsText(' . $doctrineSql . ')';
      }
      
      1. 022-doctrine-basic-entity-persister.diff
        5 kB
        Lars Strojny
      2. 024-doctrine-many-to-many-persister.diff
        3 kB
        Lars Strojny
      3. 025-doctrine-unit-of-work.diff
        2 kB
        Lars Strojny
      4. 029-doctrine-query.diff
        1 kB
        Lars Strojny
      5. doctrine-type.diff
        0.4 kB
        Benjamin Morel

        Issue Links

          Activity

          Hide
          Benjamin Eberlei added a comment -

          You can only fetch entities using the normalized value of any field, that means you have to find a common transformation that is saved in your entities and then converted on the fly.

          There is a default converting method from database to PHP and back you could use, however this is only in one direction. You could define a class for the Point and convert from and to this Call in your Type. This way you could encapsualte all the converting logic in an object and your entities only use the object.

          You can extend DQL to have additional queries, however when you select additional fields in DQL and apply a function to them, they are retrieved as scalar and not inside your entity. How to do this is detailed in the manual and the cookbook.

          Show
          Benjamin Eberlei added a comment - You can only fetch entities using the normalized value of any field, that means you have to find a common transformation that is saved in your entities and then converted on the fly. There is a default converting method from database to PHP and back you could use, however this is only in one direction. You could define a class for the Point and convert from and to this Call in your Type. This way you could encapsualte all the converting logic in an object and your entities only use the object. You can extend DQL to have additional queries, however when you select additional fields in DQL and apply a function to them, they are retrieved as scalar and not inside your entity. How to do this is detailed in the manual and the cookbook.
          Hide
          Benjamin Eberlei added a comment -

          Closed, there are already extension points that help with this matter-

          Show
          Benjamin Eberlei added a comment - Closed, there are already extension points that help with this matter-
          Hide
          Dylan Arnold added a comment -

          Hi Benjamin.

          Thanks for your response.

          I don't fully understand what you mean. As far as I can tell this is a specific use case that is not supported by doctrine.

          What I have tried is creating an entity that contains a field named $point. This is of my custom type PointType which extends Doctrine\Dbal\Types\Type

          In this method I intended to create my own Geometry\Point object by parsing a human readable value returned from the database and seamlessly converting to and from this value in convertToDatabaseValue($value, AbstractPlatform $platform) and convertToPHPValue($value, AbstractPlatform $platform)

          The problem is that the column is in binary format and I need the database to decode it for me.

          I also understand about extending DQL to use custom functions. If I went down this route, every time I fetch my entities, I would have to do an extra step to parse the scalar. Suddenly my domain logic is not encapsulated within my entity and I have a feeling this could end up being messy.

          There is also another possibility as far as I can tell, using a NativeQuery, however this would mean that every time I fetch an entity I would have to do field mapping, and any time I didn't map the field to a function my entity would fail to decode the point and break, this route would also mean a lot of extra coding and care.

          I am happy to accept that this use case may not be useful to a large portion of doctrine users and therefore may not be a justified addition to doctrine. I do however believe that this is a feature that would be nice to have, and others will miss it in the future, particularly in postgis integration.

          Doctrine already has custom DQL and custom types. As a new user my opinion is that there is definitely some room for some advanced functionality in this area. However, you guys are the core devs, you understand the project and its internals so if this is a feature that should be left out then so be it. (I may just have to look into my own hack).

          Kind regards

          Show
          Dylan Arnold added a comment - Hi Benjamin. Thanks for your response. I don't fully understand what you mean. As far as I can tell this is a specific use case that is not supported by doctrine. What I have tried is creating an entity that contains a field named $point. This is of my custom type PointType which extends Doctrine\Dbal\Types\Type In this method I intended to create my own Geometry\Point object by parsing a human readable value returned from the database and seamlessly converting to and from this value in convertToDatabaseValue($value, AbstractPlatform $platform) and convertToPHPValue($value, AbstractPlatform $platform) The problem is that the column is in binary format and I need the database to decode it for me. I also understand about extending DQL to use custom functions. If I went down this route, every time I fetch my entities, I would have to do an extra step to parse the scalar. Suddenly my domain logic is not encapsulated within my entity and I have a feeling this could end up being messy. There is also another possibility as far as I can tell, using a NativeQuery, however this would mean that every time I fetch an entity I would have to do field mapping, and any time I didn't map the field to a function my entity would fail to decode the point and break, this route would also mean a lot of extra coding and care. I am happy to accept that this use case may not be useful to a large portion of doctrine users and therefore may not be a justified addition to doctrine. I do however believe that this is a feature that would be nice to have, and others will miss it in the future, particularly in postgis integration. Doctrine already has custom DQL and custom types. As a new user my opinion is that there is definitely some room for some advanced functionality in this area. However, you guys are the core devs, you understand the project and its internals so if this is a feature that should be left out then so be it. (I may just have to look into my own hack). Kind regards
          Hide
          Benjamin Eberlei added a comment -

          Re-opened, although this might possibly be a better ORM than DBAL extension. We have to re-evaluate this for 2.1

          Show
          Benjamin Eberlei added a comment - Re-opened, although this might possibly be a better ORM than DBAL extension. We have to re-evaluate this for 2.1
          Hide
          Benjamin Eberlei added a comment -

          DBAL-53 highlightes a possible problem, how to handle foreign keys in this regard?

          Show
          Benjamin Eberlei added a comment - DBAL-53 highlightes a possible problem, how to handle foreign keys in this regard?
          Hide
          Benjamin Eberlei added a comment -

          Idea: For each type, wrap the column in a possible conversion snippet:

          class Type
          {
              public function convertToDatabaseValueSQL($sqlExpr, $platform)
              {
                  return $sqlExpr;
              }
          
              public function convertToPHPValueSQL($sqlExpr, $platform)
              {
                  return $sqlExpr;
              }
          }
          

          Example:

          class IPAddress extends Type
          {
              public function convertToDatabaseValueSQL($sqlExpr, $platform)
              {
                  return "INET_ATON($sqlExpr)";
              }
          
              public function convertToPHPValueSQL($sqlExpr, $platform)
              {
                  return "INET_PTON($sqlExpr)";
              }
          }
          

          Conversion inside the ORM would take place at the following locations:

          1. When generating SELECT SQL inside the persister, use the conversion in the SELECT clause.
          2. When generating SELECT SQL from a DQL, use conversion in the SELECT clause.
          3. When updating an entity inside the persister use this in the SET clause and WHERE clause for the ID.
          4. When inserting an entity inside the persister use this in the VALUES clause.

          Open questions:

          1. Should conversion also be done for occurances in any other clauses other than SELECT?
          2. Can we bear the performance impact of this on SQL generation.

          Show
          Benjamin Eberlei added a comment - Idea: For each type, wrap the column in a possible conversion snippet: class Type { public function convertToDatabaseValueSQL($sqlExpr, $platform) { return $sqlExpr; } public function convertToPHPValueSQL($sqlExpr, $platform) { return $sqlExpr; } } Example: class IPAddress extends Type { public function convertToDatabaseValueSQL($sqlExpr, $platform) { return "INET_ATON($sqlExpr)" ; } public function convertToPHPValueSQL($sqlExpr, $platform) { return "INET_PTON($sqlExpr)" ; } } Conversion inside the ORM would take place at the following locations: 1. When generating SELECT SQL inside the persister, use the conversion in the SELECT clause. 2. When generating SELECT SQL from a DQL, use conversion in the SELECT clause. 3. When updating an entity inside the persister use this in the SET clause and WHERE clause for the ID. 4. When inserting an entity inside the persister use this in the VALUES clause. Open questions: 1. Should conversion also be done for occurances in any other clauses other than SELECT? 2. Can we bear the performance impact of this on SQL generation.
          Hide
          Benjamin Eberlei added a comment -

          Acttually thinking about it. It has to be everywhere when 3 is necessary (and DELETE WHERE also needs this) then of course the values should be converted for SELECT clauses WHERE also.

          One Exception might be JOIN clauses WHERE its better to have:

          a.foo = b.foo
          

          vs

          FUNC(a.foo) = FUNC(b.foo)
          
          Show
          Benjamin Eberlei added a comment - Acttually thinking about it. It has to be everywhere when 3 is necessary (and DELETE WHERE also needs this) then of course the values should be converted for SELECT clauses WHERE also. One Exception might be JOIN clauses WHERE its better to have: a.foo = b.foo vs FUNC(a.foo) = FUNC(b.foo)
          Hide
          Benjamin Eberlei added a comment -

          I think we can optimize the performance of this considerably in the ORM by adding a field option "requiresSQLConversion" that is only set if a boolean method "requiresSQLConversion()" on the type returns true. This method gets a default implementation in abstract type of:

          public function requiresSQLConversion()
          {
            return false;
          }
          

          Every complex type would need to overwrite this method.

          Then inside ORM we would only need to check if (isset($field['requiresSQLConversion'])) to determine if we have to modify the SQL or not. Also caching structure would only grow by this option if the variable is actually true.

          Show
          Benjamin Eberlei added a comment - I think we can optimize the performance of this considerably in the ORM by adding a field option "requiresSQLConversion" that is only set if a boolean method "requiresSQLConversion()" on the type returns true. This method gets a default implementation in abstract type of: public function requiresSQLConversion() { return false ; } Every complex type would need to overwrite this method. Then inside ORM we would only need to check if (isset($field ['requiresSQLConversion'] )) to determine if we have to modify the SQL or not. Also caching structure would only grow by this option if the variable is actually true.
          Hide
          Lars Strojny added a comment -

          As DBAL-53 has been closed as a duplicate of this one, I'm attaching our patches to make type conversion work

          Show
          Lars Strojny added a comment - As DBAL-53 has been closed as a duplicate of this one, I'm attaching our patches to make type conversion work
          Hide
          Benjamin Eberlei added a comment -

          Thanks Lars, this is something else what i would implement though.

          This ticket gets high priority for 2.2, i will implement and check in directly after 2.1 will be released end of next month.

          Show
          Benjamin Eberlei added a comment - Thanks Lars, this is something else what i would implement though. This ticket gets high priority for 2.2, i will implement and check in directly after 2.1 will be released end of next month.
          Hide
          Benjamin Morel added a comment -

          Added a missing type hint in DBAL\Types\Type.php

          Will this feature be implemented soon? convertToDatabaseValueSQL() and convertToPHPValueSQL() are not used anywhere yet...

          Show
          Benjamin Morel added a comment - Added a missing type hint in DBAL\Types\Type.php Will this feature be implemented soon? convertToDatabaseValueSQL() and convertToPHPValueSQL() are not used anywhere yet...
          Hide
          Benjamin Eberlei added a comment -

          Implemented.

          Show
          Benjamin Eberlei added a comment - Implemented.
          Hide
          Benjamin Morel added a comment -

          What about the missing type hint on convertToPHPValueSQL() ?

          Is there a reason for the inconsistency in these method declarations:

          public function convertToDatabaseValueSQL($sqlExpr, AbstractPlatform $platform)
          public function convertToPHPValueSQL($sqlExpr, $platform)

          Show
          Benjamin Morel added a comment - What about the missing type hint on convertToPHPValueSQL() ? Is there a reason for the inconsistency in these method declarations: public function convertToDatabaseValueSQL($sqlExpr, AbstractPlatform $platform) public function convertToPHPValueSQL($sqlExpr, $platform)

            People

            • Assignee:
              Benjamin Eberlei
              Reporter:
              Dylan Arnold
            • Votes:
              6 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: