MySQL and ORM Help

[czcpf]

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

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​

ORM

$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]);​ 

[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

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​

ORM

$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]);​ 

[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.

[yuri]

Hi, You can print your queries to the log https://docs.espocrm.com/administrat...g/#sql-queries. Or use $sql = $entityManager->getQueryComposer()->compose($query); for debugging.

[czcpf]

Thanks. I'm aware of that. The issue here is I don't know how to construct the 'combined' query in mysql or ORM. In the example above, I have two queries which work in ORM and SQL. I desire a single query which gives me the result shown in the bottom of the image.

[telecastg]

You might want to try a sql builder tool like this https://www.activedbsoft.com/download-querytool.html , I have used it several times to produce the SQL code for all kinds of queries, it's free and very simple to use.

Maybe after generating the SQL statement, you can "translate it" to ORM if you want to use that format.

[yuri]

It will be possible in v7.6 with the new ability to use sub-queries in joins. Now, it might be possible too with some trick, but it's more SQL specific question as you didn't provide an actual SQL you would like to have in result. Note that the ORM does not have the ability to have sub-queries in select.

[yuri]

https://github.com/espocrm/espocrm/issues/2756

[czcpf]

Thanks. Yes, I believe a sub-query in a join would work in this case. I'm going to keep playing with the sql a bit to see if I can get the combined result.

[yuri]

If you need this data in the list view, you can opt to use loaders https://docs.espocrm.com/development...rclassnamelist. The downside is that sorting won't work.