How do I send a literal parameter via Doctrine to the DB?

Question

If I want to do something like this (notice the expires line):

$duration=24; //in hours

$reset=new PasswordReset();
$reset->code=md5(uniqid());
$reset->expires="now() + interval $duration hours";
$reset->User=$user;
$reset->save();

How can I get Doctrine to NOT quote it before sending it to postgresql? I know I could do this in PHP, but I'd like to know for future reference.

Answer 1

Hi,

You might want to take a look at Using Expression Values

The given example looks like this (quoting the doc) :

$user = new User();
$user->username = 'jwage';
$user->updated_at = new Doctrine_Expression('NOW()');
$user->save();

And generates this SQL query :

INSERT INTO user (username, updated_at_) VALUES ('jwage', NOW())

I Suppose it would do, in your case ?
(I don't have a Doctrine-enabled project right here, so can't test for you)

As a sidenote : I don't have a PostGreSQL server to test on ; but what you propose doesn't work on MySQL : it seems you have to use "now() + interval 2 hour", and not "now() + interval 2 hours" -- still, I don't know about PG

---

EDIT after the comment

Ergh, you're right, this is not a correct solution ; it doesn't work :-(

Well... Interesting question, so I dug a bit more, and went to Doctrine's source-code ; I think I may have find something interesting.

If you look at the source of Doctrine_Query_Where::parseValue source, you'll notice this portion of code :

// If custom sql for custom subquery
// You can specify SQL: followed by any valid sql expression
// FROM User u WHERE u.id = SQL:(select id from user where id = 1)
} elseif (substr($trimmed, 0, 4) == 'SQL:') {
    $rightExpr = '(' . substr($trimmed, 4) . ')';
// simple in expression found

I have absolutly not tried, but this might be interesting...

Maybe you could do something like this :

$reset->expires="SQL:(now() + interval $duration hours)";

If you try that, I'm very interested in knowing if it would work!
Might be useful, one day or another ;-)

BTW, it seems it's used in Doctrine_Search too ; looking at this one, maybe it'll work without the parentheses, like this :

$reset->expires="SQL:now() + interval $duration hours";

Well... I Hope, this time, it helps... Because I don't see much other way to do what you're trying to get (and google doesn't help me either ^^ )

---

EDIT after the second (third, if counting mine) comment.
I will get this working... else I won't sleep well ^^

Well, I might have found a way (and, this time, I tested it ^^ ) ; not really as great as one would like, but, for updates anyway, it seems to be working...

Let's say I have a table created this way :

CREATE TABLE  `test1`.`test` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(32) NOT NULL,
  `value` varchar(128) NOT NULL,
  `date_field` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The corresponding model class looks like this :
Probably not perfect : it's a test-class I've written for something else, that I've mutated to fit this one ^^

class Test extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->setTableName('test');
        $this->hasColumn('id', 'integer', 4, array(
             'type' => 'integer',
             'length' => 4,
             'unsigned' => 0,
             'primary' => true,
             'autoincrement' => true,
             ));
        $this->hasColumn('name', 'string', 32, array(
             'type' => 'string',
             'length' => 32,
             'fixed' => false,
             'notnull' => true,
             ));
        $this->hasColumn('value', 'string', 128, array(
             'type' => 'string',
             'length' => 128,
             'fixed' => false,
             'primary' => false,
             'notnull' => true,
             'autoincrement' => false,
             ));
        $this->hasColumn('date_field', 'integer', 4, array(
             'type' => 'timestamp',
             'notnull' => true,
             ));
    }
}

I'll insert two lines into this table :

$test = new Test();
$test->name = 'Test 1';
$test->value = 'My Value 1';
$test->date_field = "2009-01-30 08:30:00";
$test->save();

$test = new Test();
$test->name = 'Test 2';
$test->value = 'My Value 2';
$test->date_field = "2009-02-05 08:30:00";
$test->save();

Which gets me this data from SQL :
BTW : I don't have a pg server, so I'll test everything with MySQL -- should work on pg too, still...

mysql> select * from test;
+----+--------+------------+---------------------+
| id | name   | value      | date_field          |
+----+--------+------------+---------------------+
|  1 | Test 1 | My Value 1 | 2009-01-30 08:30:00 |
|  2 | Test 2 | My Value 2 | 2009-02-05 08:30:00 |
+----+--------+------------+---------------------+
2 rows in set (0.00 sec)

So, two lines, with dates long ago in the past.

Now, to be sure, let's just fetch the line #1 :

$testBefore = Doctrine::getTable('Test')->find(1);
var_dump($testBefore->toArray());

I'm getting this kind of output :

array
  'id' => string '1' (length=1)
  'name' => string 'Test 1' (length=6)
  'value' => string 'My Value 1' (length=10)
  'date_field' => string '2009-01-30 08:30:00' (length=19)

And, now, the interesting part : let's update that line, using an expression like the one you provided to set the date_field value :

$query = new Doctrine_Query();

$query->update('test')
    ->set('date_field', 'NOW() - interval 2 hour')
    ->where('id = ?', 1)
    ->execute();

var_dump($query->getSql());

The SQL that I get as output is this one :

string 'UPDATE test SET date_field = NOW() - interval 2 hour WHERE id = ?' (length=65)

Which kinda look like what you want, if I'm not mistaken ;-)

And, just to be sure, let's fetch our line once again :

$testAfter = Doctrine::getTable('Test')->find(1);
var_dump($testAfter->toArray());

And I get this result :

array
  'id' => string '1' (length=1)
  'name' => string 'Test 1' (length=6)
  'value' => string 'My Value 1' (length=10)
  'date_field' => string '2009-08-04 21:26:30' (length=19)

Considering the date and time, it seems this worked -- hoorray !

And, to be sure, let's query the data directly from the DB :

mysql> select * from test;
+----+--------+------------+---------------------+
| id | name   | value      | date_field          |
+----+--------+------------+---------------------+
|  1 | Test 1 | My Value 1 | 2009-08-04 21:26:30 |
|  2 | Test 2 | My Value 2 | 2009-02-05 08:30:00 |
+----+--------+------------+---------------------+
2 rows in set (0.00 sec)

And... Yeeeepe !

Well, now, the not so good parts : to be able to use that syntax, I had to create the query "by hand", to use the set() method, instead of doing it "nicely" with the model class and the save() method :-(

It's now up to you to see you that could be integrated into your model class... Have fun with that ;-)

And if you find a way, some day, to use expressions like this one in other parts of the query, or to do it a cleaner way, I'd really appreciate if you could post a comment to let me know ;-)

And, this, time, I sincerely hope I found the way ^^