CODEWITHSUNDEEP
phpmysqlsymfonydoctrine-orm
Nick
Nick

Reputation: 461

Doctrine query builder ~ datetime

I'm trying to create a simple chatbox in symfony2 / doctrine 2.

For testing I'm checking for new messages every 5 seconds, so in my query I try to get all messages by getting all messages with a datetime greater than the current one minus 5 seconds.

I try to do so the following way, but it returns all messages in the database instead of the ones posted in the last 5 seconds

$em = $this->getDoctrine()->getManager();

$qb = $em->createQueryBuilder();
$qb->select('m')
                 ->from('ChatboxBundle:ChatMessage', 'm')
                 ->where(':new > :last')
                 ->setParameter('new', 'm.postdate' )
                 ->setParameter('last', new \DateTime('-5 second'), \Doctrine\DBAL\Types\Type::DATETIME);
$updatedata = $qb->getQuery()->getResult();

Any ideas on what I'm doing wrong?

Upvotes: 16

Views: 26818

Answers (2)

Bruno
Bruno

Reputation: 41

Note thats You don't need to use \Doctrine\DBAL\Types\Type::DATETIME

The doc say : Calling setParameter() automatically infers which type you are setting as value. This works for integers, arrays of strings/integers, DateTime instances and for managed entities. If you want to set a type explicitly you can call the third argument to setParameter() explicitly. It accepts either a PDO type or a DBAL Type name for conversion.

Upvotes: 0

FuzzyTree
FuzzyTree

Reputation: 32402

m.postdate is a field name and therefore shouldn't be passed in as a parameter. Try this

$qb = $em->createQueryBuilder();
$qb->select('m')
                 ->from('ChatboxBundle:ChatMessage', 'm')
                 ->where('m.postdate > :last')
                 ->setParameter('last', new \DateTime('-5 second'), \Doctrine\DBAL\Types\Type::DATETIME);
$updatedata = $qb->getQuery()->getResult();

Upvotes: 20

Related Questions