0

I want to compare a stored DateTime entry in my Database with the current date using Doctrine.

I have a list of Keys and their relation to a Person. My goal now is to create a list for available keys or not available keys.

For that I have to look at 2 Tables, key and personToKey. Where personToKey has a field rueckgabe(time to return the key) (dateTime) and a field key (stored id of the key) with an OneToMany Relation to the field id of the Table key.

Thanks to some search here, I got a running SQL Query which gives me the values I am looking for. SQL Query:

 SELECT * FROM key LEFT OUTER JOIN personToKey on key.id = personToKey.KeyId 
  WHERE personToKey.rueckgabe < NOW()

My Problem is now, that I don't get this to work using doctrine. I had 2 approaches for this.

  $qb = $em->createQueryBuilder();
  $query = $qb->select('k')
  ->from('MyBundle:key', 'k')
  ->leftjoin('MyBundle:personToKey', 'ptk','WITH', 's.id = ptk.key')
  ->where('ptk.rueckgabe < :date_now')
  ->setParameter('date_now', date('Y-m-d H:i:m'))
  ->getQuery();
  $test = $query->getResult();

And

$sql = 'SELECT k FROM MyBundle:key k LEFT OUTER JOIN MyBundle:PersonToKey ptk WITH k.id = ptk.key WHERE ( ptk.rueckgabe < CURRENT_DATE()) ';
$query = $em->createQuery($sql);
$test = $query->getResult();

Since the SQL Query works I would have thought that at least the second approach should have worked. But in both cases I get the following results:

for ptk.rueckgabe >= :date_now the list is fine, but for ptk.rueckgabe < :date_now I get a mix of available and not available keys. Also the length of the result is below the number of rows the SQL Query finds in PHPMyAdmin.

Thanks in advance.

coud28098
  • 151
  • 6

2 Answers2

0

As the answer here states:

The equivalent of MySQL's NOW() is Doctrine DQL's CURRENT_TIMESTAMP().

CURRENT_DATE() only returns the date part.

This might explain, why the second approach did not give the same result as running a straight SQL query.

For the first approach you could try the following, even though I am not sure if it will make a difference:

->setParameter('date_now', new \DateTime())

Edit: I found another potential problem in your QueryBuilder: Instead of 

->leftjoin('MyBundle:personToKey', 'ptk','WITH', 's.id = ptk.key')

you could try to write:

->leftjoin('k.personToKey', 'ptk')

The s.id was definitely wrong, not sure about the rest.

Community
  • 1
  • 1
Emanuel Oster
  • 1,296
  • 10
  • 21
  • I tried it out the second approach with CURRENT_TIMESTAMP() and the first with ->setParameter('date_now', new \DateTime()), but neither of them worked for me. – coud28098 Aug 09 '16 at 09:13
  • The s.id was my bad, I changed it to k.id. I also changed the Query, but still the same result. – coud28098 Aug 09 '16 at 09:38
  • 1
    Okay then I don't have an idea anymore. You could open the Symfony Profiler (via the toolbar at the bottom) where you can find all executed SQL queries for that page. Find the right query and compare it to your self-written query. Maybe that will give you the right hint. – Emanuel Oster Aug 09 '16 at 09:46
0

I finally found a working solution for my Problem, so I wanted to share it, if other People have a similar problem.

  $now = new \DateTime() ;
  $now1=$now->format('Y-m-d H:i:s');
  $sql = 'SELECT * FROM key AS k
    LEFT OUTER JOIN persontoKey AS ptk ON k.id = ptk.keyId
    WHERE ( ptk.rueckgabe < :now ) ';
  $em = $this->getDoctrine()->getManager();
  $stmt = $em->getConnection()->prepare($sql);
  $stmt->bindValue('now', $now1, \PDO::PARAM_STR);
  $stmt->execute();
  $helper= $stmt->fetchAll();
coud28098
  • 151
  • 6