Translate SQL to ORM

[czcpf]

Hello, I have the following code which needs to use WHERE NOT EXISTS. Can I somehow use ORM to structure this or am I stuck using raw sql ?

// Return all non-deleted radiation machine locations where an active, operator does not exist

SELECT *
FROM radiation_machine_location
WHERE NOT EXISTS (
SELECT * FROM radiation_machine_operator_radiation_machine_location
INNER JOIN radiation_machine_operator ON radiation_machine_operator.id = radiation_machine_operator_radiation_machine_location.radiation_machine_operator_id
WHERE radiation_machine_operator.status = 'ACTIVE'
AND radiation_machine_operator_radiation_machine_location.radiation_machine_location_id = radiation_machine_location.id
AND radiation_machine_operator.deleted = 0
)
AND radiation_machine_location.deleted = 0

[yuri]

Exists: https://github.com/espocrm/espocrm/b...rTest.php#L646

Wrap it with not: https://github.com/espocrm/espocrm/b...erter.php#L423

[czcpf]

Thanks yuri. I'm still struggling here. Following your recommendation, I tested it with a simpler example. Only the MYSQL version is giving me the correct answer (which should be 1 row with inspection number 1074408:

MYSQL WAY

SELECT inspection_number, notice_date
FROM notice_of_violation nov
WHERE NOT EXISTS (
SELECT *
FROM violation_response vr
WHERE vr.notice_of_violation_id = nov.id
AND vr.direction = "TO_REGULATORY_BODY"
AND vr.file_id IS NOT NULL
)
AND nov.deleted = 0​

OUTPUTs 1 row:



ORM WAY

$query = $this->entityManager->getQueryBuilder()
->select(['inspectionNumber','noticeDate'])
->from('NoticeOfViolation','nov')
->where(
Cond::and(
Cond::not(
Cond::exists(
(new SelectBuilder())
->select('*')
->from('ViolationResponse','vr')
->where(
Cond::and(
Cond::equal(Cond::column('vr.noticeOfViolationId'),'nov.id'),
Cond::equal(Cond::column('vr.direction'),'TO_REGULATORY_BODY'),
Cond::notEqual(Cond::column('vr.fileId'),null)
)
)
->build()
)
),
Cond::equal(Cond::column('nov.deleted'),false)
)

)
->build();​

$rowList = $this->entityManager
->getQueryExecutor()
->execute($query)
->fetchAll(\PDO::FETCH_ASSOC);

$this->log->warning('Processor.php ComplianceAudit - auditNoticeOfViolations() $rowList= ', [$rowList]);​ 

OUTPUTs All entries:

[2023-06-08 20:02:32] WARNING: Processor.php ComplianceAudit - auditNoticeOfViolations() $rowList= [[{"inspectionNumber":"1039613","noticeDate":"201 7-02-01"},{"inspectionNumber":"1074408","noticeDate": "2021-10-11"},{"inspectionNumber":"1074408","noticeDate": "2021-09-23"}]]

[czcpf]

I just realized that the first equal condition needed to have Cond::column as well. Here is the updated code which works. Thanks!

$query = $this->entityManager->getQueryBuilder()
->select(['inspectionNumber','noticeDate'])
->from('NoticeOfViolation','nov')
->where(
Cond::and(
Cond::not(
Cond::exists(
(new SelectBuilder())
->select('*')
->from('ViolationResponse','vr')
->where(
Cond::and(
Cond::equal(Cond::column('vr.noticeOfViolationId') ,Cond::column('nov.id')),
Cond::equal(Cond::column('vr.direction'),'TO_REGULATORY_BODY'),
Cond::notEqual(Cond::column('vr.fileId'),null)
)
)
->build()
)
),
Cond::equal(Cond::column('nov.deleted'),false)
)
)
->build();