-1

I would like to compare a datetime value (from a database) with the current time and check whether the current time is before or after the value in the database. This is done in a Symfony project. I tried to follow the instructions on the Symfony website.

So I wrote the following Doctrine query in a Repository Class which checks for a user lockout time and checks if it still lies in the future:

 $user_id = 1; // Just giving $user_id a value for this example     
 $qb = $this->createQueryBuilder('user')
        ->andWhere('user.lockout_time > :time')
        ->setParameter('time', date("Y-m-d H:i:s"))
        ->andWhere('user.user_id = :user_id')
        ->setParameter('user_id', $user_id);
    
 $query = $qb->getQuery();
 echo $query->getSQL();
 die;

When running this, both Where clauses contain "?" in the comparative value (e.g. WHERE user.lockout_time > ?). Obviously I want the actual values to be used in the query.

Initially I thought the date() function might be the issue, but even if I just use the :user_id I get the above error.

If I write ->andWhere('user.user_id = 1') I get the desired result.

If I replace :time with some date in the format 'Y-m-d H:i:s', I get the message "Error: Expected end of string..." (with the ... being the value for the Hour).

So both my setParameter() lines are not passing the values set in them. What am I overlooking?

Edit: The suggested question here is not a duplicate. That just helps me see the query that is sent off. It was helpful in preparation of this question.

Charles
  • 179
  • 1
  • 15
  • 1
    Does this answer your question? [Doctrine - How to print out the real sql, not just the prepared statement?](https://stackoverflow.com/questions/2095394/doctrine-how-to-print-out-the-real-sql-not-just-the-prepared-statement) – Arleigh Hix May 02 '21 at 18:44
  • Also you probably should use `->setParameter('time', new \DateTime())` the object doctrine translates this field to and from. The `setParameter` method will convert it to the proper value for the configured database. – Arleigh Hix May 02 '21 at 18:50
  • @ArleighHix Thank you. I found it before I posted this. It helped me come to the conclusion that only "?" are output for the named placeholders. Thank you also for the tip with new \DateTime(). It is one of the things I tried, but to no avail. I changed it back in my code. – Charles May 02 '21 at 19:34

1 Answers1

0

So here my own answer after some (= way too much) time of digging around.

The "?" are escaped values and do not represent what is actually in the query (which is another reason the above link doesn't help). To resolve this I resorted to monitoring the MySQL general log.

Here how to get to it, if someone has the same question. This log shows the actual SQL query.

Charles
  • 179
  • 1
  • 15