QueryBuilder : lt() on datetime does not exclude current object
- Dominant language
- PHP
- Stars
- 181
- Forks
- 102
- PR merge metrics
- No merged PRs in 30d
Description
Hello.
I have a problem with a filter in the query builder on a datetime field. I want the object with the date just before the given one, and the query returns an object with the same datetime (exactly the same)
``` php
public function getPreviousNews(News $news)
{
$qb = $this->createQueryBuilder('news');
$qb->where()->lt()->field('news.date')->literal($news->getDate())->end(); // Here
$qb->orderBy()->desc()->field('news.date');
$qb->setMaxResults(1);
try {
return $qb->getQuery()->getSingleResult();
} catch (\Exception $ex) {
return null;
}
}
```
Query generated
```
SELECT * FROM [nt:unstructured] AS news WHERE (news.date < CAST('1996-02-16T10:20:39.000+01:00' AS DATE) AND (news.[phpcr:class] = 'xxx\AppBundle\Document\News' OR news.[phpcr:classparents] = 'xxx\AppBundle\Document\News')) ORDER BY news.date DESC
```
And var_dump of the datetime from the object that I get
```
object(DateTime)#1573 (3) { ["date"]=> string(26) "1996-02-16 10:20:39.000000" ["timezone_type"]=> int(1) ["timezone"]=> string(6) "+01:00" }
```
The problem does not appears with gt() method.
Thank you !
Contributor guide
Research direction
Start with the QueryBuilder lt() path shown in getPreviousNews(), then compare its generated DATE query with the gt() path, using the supplied datetime and var_dump as the reproduction. Determine why lt() returns a record with an equal datetime and verify the corrected comparison with an equivalent query test; done means the current object is excluded while the immediately preceding News is returned.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- php
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100