3

I have a query that gives me a unix timestamp calculated selecting the datetime value of a table field and then adding another value of the table. The query is something like the following:

SELECT UNIX_TIMESTAMP(DATE_ADD(mydatetimefield, INTERVAL m.myfield1 + m.myfield2 MINUTE)) FROM mytable AS m

this query executes correctly from phpMyAdmin but when I try to use it with the createQueryBuilder method of Doctrine 2 I get an error at "myfield". It seems that it doesn't support a computed value after the INTERVAL keyword

Error: Expected Doctrine\ORM\Query\Lexer::T_COMMA, got 'm'

how can I then get the same query result by using doctrine? I use the querybuilder because I have some named parameters

Stefano
  • 3,213
  • 9
  • 60
  • 101
  • 1
    You should provide the code used with the `createQueryBuilder` also. – dan Nov 21 '12 at 22:36
  • did you simplify the query in your example as well? – kmfk Nov 21 '12 at 22:38
  • How to use DATE_ADD OR DATE_SUB having the interval as number of hours. **Refer to this answer:** http://stackoverflow.com/a/22223127/879708 – Francis Mar 06 '14 at 11:30

2 Answers2

3

By default Doctrine does not support all the functions of a specific vendor, such as the DATE_ADD. However, you can use custom defined functions. That link has an example specifically for date add.

Extending DQL in Doctrine 2: User-Defined Functions

The other option, depending on the complexity of the query, is just to handle this in the application code. if your mydatetimefield is actually a datetime, Doctrine will convert that to a php DateTime in the returned object.

$myDateTimeField = $object->getMyDateTimeField();
$myDateTimeField->add(new DateInterval( $object->getMyField1() + $object->getMyField2() . 'M'));

Lastly, you could always use dbal as well.

Sumit
  • 2,242
  • 1
  • 24
  • 37
kmfk
  • 3,821
  • 2
  • 22
  • 32
  • I get an error when I try to use that DateAdd function: "It is not allowed to overwrite internal function 'date_add' in the DQL parser through user-defined functions". Why? – Stefano Nov 22 '12 at 15:36
  • Looks like they added `DATE_ADD` - though, looking at the docs, Doctrine's implementation only supports adding `day` and `month` intervals - [DQL Functions](http://docs.doctrine-project.org/projects/doctrine-orm/en/2.1/reference/dql-doctrine-query-language.html#dql-functions) - if you wanted to create your own which allowed minutes, you would need to use a different name for the function. – kmfk Nov 26 '12 at 16:36
  • I find really bad that we can't override default functions, however I solved by using a raw sql query instead of DQL – Stefano Nov 26 '12 at 17:02
1

Not sure if this is the cause, but since it is complaining about a missing comma where you have the m alias I suspect that it is not able to parse properly the user defined functions.

Have you defined the DATE_ADD and UNIX_TIMESTAMP as DQL User Defined Functions? See Date Add example for more details.

dan
  • 13,132
  • 3
  • 38
  • 49
  • removing the UNIX_TIMESTAMP custom function doesn't fix the problem – Stefano Nov 22 '12 at 15:20
  • @Stefano And you have `DATE_ADD` defined as a custom function? – dan Nov 22 '12 at 15:23
  • no, I don't. I was trying to use the DATE_ADD function as in MySQL but then I saw Doctrine 2 doesn't support the INTERVAL and also doesn't have full support for all types (hours, minutes, etc.). I will use the function reported by kmfk – Stefano Nov 22 '12 at 15:24
  • @Stefano It's your choice, but you can also use DATE_ADD too, see the example link that I have provided in my answer. – dan Nov 22 '12 at 15:29
  • the code of the DateAdd function you posted is the same that I found on github via kmfk. The author of the function and of the article is the same – Stefano Nov 22 '12 at 15:31