[DDC-1503] Add support for executing custom SQL functions on custom types Created: 02/Jun/10  Updated: 09/Mar/12  Resolved: 21/Nov/11

Status: Closed
Project: Doctrine 2 - ORM
Component/s: None
Affects Version/s: None
Fix Version/s: 2.2
Security Level: All

Type: New Feature Priority: Minor
Reporter: Dylan Arnold Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 6
Labels: None

Attachments: File 022-doctrine-basic-entity-persister.diff     File 024-doctrine-many-to-many-persister.diff     File 025-doctrine-unit-of-work.diff     File 029-doctrine-query.diff     File doctrine-type.diff    
Issue Links:
Duplicate
is duplicated by DBAL-53 Missing convertToDatabase-calls when ... Resolved

 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 . ')';
}


 Comments   
Comment by Benjamin Eberlei [ 04/Jun/10 ]

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.

Comment by Benjamin Eberlei [ 04/Jun/10 ]

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

Comment by Dylan Arnold [ 04/Jun/10 ]

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

Comment by Benjamin Eberlei [ 12/Jul/10 ]

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

Comment by Benjamin Eberlei [ 20/Sep/10 ]

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

Comment by Benjamin Eberlei [ 24/Dec/10 ]

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.

Comment by Benjamin Eberlei [ 24/Dec/10 ]

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)
Comment by Benjamin Eberlei [ 10/Mar/11 ]

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.

Comment by Lars Strojny [ 02/May/11 ]

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

Comment by Benjamin Eberlei [ 15/May/11 ]

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.

Comment by Benjamin Morel [ 14/Oct/11 ]

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

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

Comment by Benjamin Eberlei [ 21/Nov/11 ]

Implemented.

Comment by Benjamin Morel [ 09/Mar/12 ]

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)

Generated at Wed Jul 30 05:11:12 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.