doctrine / doctrine/phpcr-odm

QueryBuilder : lt() on datetime does not exclude current object

Open
#595 4 comments 0 reactions 0 assignees View on GitHub
bug
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.