ORM Query to check if joined table DOES NOT entry with Date greater than

[czcpf]

Hello,

I'm trying to create a query which would return all EntityA entries that do not have an entry in `tests` with a date greater than the current year. I think I need something like NOT EXISTS in the query. EntityA has relation called 'tests'

$collection = $this->entityManager
->getRDBRepository('EntityA')
->distinct()
->join('tests')
->where([
'tests.date<' => (new \DateTimeImmutable)->format('Y-01-01 00:00:00'),
])
->find();​ 

[czcpf]

Hello, for anyone else needing something like this. Here is one such solution:

use Espo\ORM\Entity;
use Espo\ORM\EntityManager;
use Espo\Core\Utils\Log;
use Espo\ORM\Query\SelectBuilder;
use Espo\ORM\Query\Part\Condition as Cond;​

//create a subQuery to return all EntityA ids where the relation field tests.date is greater than the current year

$subQuery = SelectBuilder::create()
->select('id')
->distinct()
->from('EntityA')
->join('tests')
->where([
'tests.date>=' => (new \DateTimeImmutable)->format('Y-01-01 00:00:00')
])
->build();​

//return the collection of EntityA whose Id's are not in the the subQuery result and therefore have 'tests' whose dates are prior to the current year.

$collection = $this->entityManager
->getRDBRepository('EntityA')
->distinct()
->where(
Cond::notIn(Cond::column('id'), $subQuery)
)
->find();​ 

If anyone knows a faster way please advise.

[item]

https://github.com/espocrm/espocrm/search?l=PHP&q=orphan

i think, follow Yuri minds

[shalmaxb]

Wouldn`t this be possible by filter?