coud28098 coud28098 - 3 months ago 16
MySQL Question

Symfony2: comparing a stored datetime object with the current date with doctrine

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.

Answer

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();   
Comments