Announcement

Collapse
No announcement yet.

MySQL and ORM Help

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • czcpf
    commented on 's reply
    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
    commented on 's reply
    $select = SelectBuilder::create() ->select('id') ->from('TableA') ->join( SelectBuilder::create() ->select('id') ->from('TableB') ->build(), 'b', Condition::equal( Expression::column('b.id'), Expre...

  • yuri
    replied
    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.

    Leave a comment:


  • yuri
    replied
    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:


  • telecastg
    replied
    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:


  • czcpf
    commented on 's reply
    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.

  • yuri
    replied
    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:


  • czcpf
    started a topic MySQL and ORM Help

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

    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​
    ORM
    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]);​ 
    [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.

    Click image for larger version  Name:	OUTPUTS.png Views:	0 Size:	170.7 KB ID:	93719
    Last edited by czcpf; 06-14-2023, 02:39 PM.
Working...
X