MySQL and ORM Help
Collapse
X
-
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. -
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.Leave a comment:
-
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.Leave a comment:
-
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.Leave a comment:
-
MySQL and ORM Help
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
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.
Last edited by czcpf; 06-14-2023, 02:39 PM.Tags: None
Leave a comment: