How to update table using Doctrine2 ORM and make it automatically convert datatypes?

Question

The question is in the title.

I have a custom IP-Address class, that uses INET_NTOA & INET_ATON functions and extends Doctrine\DBAL\Types\Type.

What i've tried

1

$qb->update(static::class, 't');
$qb->set('t.field', false);
$qb->where($qb->expr()->eq('t.ip', '127.0.0.1'));
$qb->getQuery()->execute();

which causes [Syntax Error] line 0, col 41: Error: Expected Literal, got 'WHERE'

2

$qb->update(static::class, 't');
$qb->set('t.field', $qb->expr()->literal(false));
$qb->where($qb->expr()->eq('t.ip', '127.0.0.1'));
$qb->getQuery()->execute();

which causes [Syntax Error] line 0, col 63: Error: Expected Literal, got '127.0.0.1'

3

$qb->update(static::class, 't');
$qb->set('t.field', $qb->expr()->literal(false));
$criteria = Criteria::create()->where(Criteria::expr()->eq('t.ip', '127.0.0.1'));
$qb->addCriteria($criteria);
$qb->getQuery()->execute();

which causes:

An exception occurred while executing 'UPDATE table SET field = 0 WHERE ip = ?' with params ["127.0.0.1"]:
SQLSTATE[22007]: Invalid datetime format: 1292 Truncated incorrect DOUBLE value: '127.0.0.1'`

UPD:

My custom IPType:

namespace app\types;

use Doctrine\DBAL\Platforms\AbstractPlatform;
use Doctrine\DBAL\Types\Type;

class IpType extends Type
{
    public function getSQLDeclaration(array $fieldDeclaration, AbstractPlatform $platform): string
    {
        return $platform->getIntegerTypeDeclarationSQL(['unsigned' => true]);
    }

    public function canRequireSQLConversion(): bool
    {
        return true;
    }

    public function convertToPHPValueSQL($sqlExpr, $platform): string
    {
        return sprintf('INET_NTOA(%s)', $sqlExpr);
    }

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

    public function getName(): string
    {
        return 'ip_address';
    }
}

and declaration of IP in my Entity class:

/**
 * @Column(type="ip_address")
 * @var string
 */
protected $ip;

Have you tried $qb->expr()->eq('t.ip', $qb->expr()->literal('127.0.0.1')) ? — Tomáš Neumaier, Jan 03 '19 at 18:57
@TomášNeumaier yes, it causes the same error as in the third case — DevilishSkull, Jan 03 '19 at 23:35

score 0 · Answer 1 · answered Jan 03 '19 at 17:56

0

You need to implement custom mapping type class, e.g. IP and use it to define mappings for your entities. Value conversion can be done by using ip2long() and long2ip() functions, please refer this answer for more information on this topic if necessary.

answered Jan 03 '19 at 17:56

Flying

4,422
2
17
25

i want to support ipv6 – DevilishSkull Jan 03 '19 at 17:58
You can support any format that you may need, overall principle is to create custom mapping types if you want automatic values conversion – Flying Jan 03 '19 at 18:01
and i also would like to do as many conversions as possible using MySQL, not PHP – DevilishSkull Jan 03 '19 at 18:01
and yes, i have IPType which extends Doctrine\DBAL\Types\Type (the second line of my question is about that) – DevilishSkull Jan 03 '19 at 18:02
@DevilishSkull Sorry, use of custom type wat not clear from your question – Flying Jan 03 '19 at 18:12
do you have any ideas? – DevilishSkull Jan 08 '19 at 21:47
@DevilishSkull can you update your question and include your custom Doctrine type class? Using of custom type should be correct answer for your own question but since you already have it - maybe there is something wrong with a class itself? It may be also worth to publish at least part of entity that includes property that uses your custom type – Flying Jan 09 '19 at 10:06
updated. I still have no idea why the third error occures. – DevilishSkull Jan 10 '19 at 02:11
@DevilishSkull I have one thought: you're passing your IP address explicitly to `Criteria`, but if you will look into sources how criterias are rendered - you will see that they're simply get merged. I would propose to pass named parameter `:ip` instead and assign it to query through `setParameter`. This method have third argument `$type`, so if it will not work implicitly - you may try to pass its type explicitly. Can you please try? – Flying Jan 10 '19 at 10:44
the following line helped me `$qb->andWhere('t.ip= :ip')->setParameter('ip', $ip, 'ip_address');`. However, is there any way to automatically identify the type of the parameter? In theory it is possible, because i specify the name of the entity and of the column explicitly. – DevilishSkull Jan 10 '19 at 18:01
@DevilishSkull According to Doctrine sources parameters resolving is performed [here](https://github.com/doctrine/orm/blob/10393dca68d48bc4c689f690b4b6af2df1b90740/lib/Doctrine/ORM/Query.php#L433) and it is more or less relies for static logic, probably due to performance reasons. Simplest way that I can see from sources is to create own `Parameter` class and use `getParameters()->add(new IpParameter())` for setting custom parameter. – Flying Jan 10 '19 at 19:51

How to update table using Doctrine2 ORM and make it automatically convert datatypes?

1 Answers1