Hello good friends,
I have the following queries which run perfectly fine and I can build each one in ORM fine as well.
Query 1 : MySQL
ORM
[2023-06-14 01:27:40] WARNING: Processor.php ComplianceAudit - auditCBCTCountAuthorization() $rowList= [[{"rml.id":"63a9ddc374b63afe0","rml.name":"Downt own Houston","dentalCountInUse":3},{"rml.id":"63b5d70b 22f197ddf","rml.name":"Katy Office","dentalCountInUse":1},{"rml.id":"63b84759b 5690a92a","rml.name":"Sugarland Office","dentalCountInUse":2}]]
Query 2 : MySQL
ORM
[2023-06-14 01:27:40] WARNING: Processor.php ComplianceAudit - auditCBCTCountAuthorization() $rowList= [[{"rml.id":"63a9ddc374b63afe0","rml.name":"Downt own Houston","maxDentalAuthorized":"3"},{"rml.id":"63b 5d70b22f197ddf","rml.name":"Katy Office","maxDentalAuthorized":"1"},{"rml.id":"63b8 4759b5690a92a","rml.name":"Sugarland Office","maxDentalAuthorized":"7"}]]
DESIRED
What I desire is a combined query such that the output is as shown in the bottom of the attached graphic. That is, id, name, dentalCountInUse, maxDentalAuthorized. Preferably, If someone could also provide help on the syntax in ORM that would be beneficial.
I have the following queries which run perfectly fine and I can build each one in ORM fine as well.
Query 1 : MySQL
Code:
SELECT rml.id, rml.name, COUNT(rm.id) AS dentalCountInUse FROM `radiation_machine_location` AS `rml` JOIN `radiation_machine_location_radiation_machine` AS `rmMiddle` ON rml.id = rmMiddle.radiation_machine_location_id AND rmMiddle.deleted = 0 JOIN `radiation_machine` AS `rm` ON rm.id = rmMiddle.radiation_machine_id AND rm.deleted = 0 JOIN `radiation_machine_category` AS `rmc` ON rmc.id = rm.radiation_machine_category_id AND rmc.deleted = 0 WHERE rm.machine_status = 'IN_USE' AND rmc.name = 'Dental' GROUP BY rml.id, rml.name ORDER BY rml.id
PHP Code:
$query1 = (new SelectBuilder())
->select(['rml.id','rml.name',['COUNT:(rm.id)','dentalCountInUse']])
->from('RadiationMachineLocation','rml')
->join('radiationMachines','rm')
->join('RadiationMachineCategory','rmc',
[
'rmc.id:' => 'rm.radiationMachineCategoryId',
'rmc.deleted' => false
],
)
->where([
'rm.machineStatus=' => 'IN_USE',
'rmc.name' => 'Dental',
])
->group(['rml.id','rml.name'])
->order(['rml.id'])
->build();
$rowList = $this->entityManager
->getQueryExecutor()
->execute($query1)
->fetchAll(\PDO::FETCH_ASSOC);
$this->log->warning('Processor.php ComplianceAudit - auditCBCTCountAuthorization() $rowList= ', [$rowList]);
Query 2 : MySQL
Code:
SELECT rml.id, rml.name, SUM(authorizedUses.max_units) AS maxDentalAuthorized FROM `radiation_machine_location` AS `rml` JOIN `radiation_machine_location_radiation_machine_category` AS `authorizedUses` ON rml.id = authorizedUses.radiation_machine_location_id AND authorizedUses.deleted = 0 JOIN `radiation_machine_category` AS `rmc` ON rmc.id = authorizedUses.radiation_machine_category_id AND rmc.deleted = 0 WHERE rmc.name = 'Dental' GROUP BY rml.id, rml.name ORDER BY rml.id
PHP Code:
$query2 = (new SelectBuilder())
->select(['rml.id','rml.name',['SUM:(authorizedUses.max_units)','maxDentalAuthorized']])
->from('RadiationMachineLocation','rml')
->join('radiationMachineLocationRadiationMachineCategories','authorizedUses')
->join('RadiationMachineCategory','rmc',
[
'rmc.id:' => 'authorizedUses.radiationMachineCategoryId',
'rmc.deleted' => false
],
)
->where([
'rmc.name' => 'Dental',
])
->group(['rml.id','rml.name'])
->order(['rml.id'])
->build();
$rowList = $this->entityManager
->getQueryExecutor()
->execute($query2)
->fetchAll(\PDO::FETCH_ASSOC);
$this->log->warning('Processor.php ComplianceAudit - auditCBCTCountAuthorization() $rowList= ', [$rowList]);
DESIRED
What I desire is a combined query such that the output is as shown in the bottom of the attached graphic. That is, id, name, dentalCountInUse, maxDentalAuthorized. Preferably, If someone could also provide help on the syntax in ORM that would be beneficial.
Comment