Hi. This questions is related to the other one concerned about distinct values. I have a relationship N:1 between 2 entities -> VDI and Host. Y select a VDI but then I create another entity where the Host and the VDI are specified. I must select the Host that has the lowest load of the VDI with the same name. So then, in SQL I have the following query: select anyHostWithTheVDI.id, anyHostWithTheVDI.srvIP from _Host anyHostWithTheVDI, _VDI theVDI where theVDI.name='NAMEOFVDI' AND theVDI.deployedToday='1' AND theVDI.host=anyHostWithTheVDI.id AND not exists (select * from (SELECT count( h.id ) as hostUsage, h.id AS hostID, h.srvDNSname, h.capacity FROM _Host h, _VDI vdi, _VMusage vmusg WHERE vmusg.vdi = vdi.id AND vdi.host = h.id AND vdi.deployedToday=1 AND vmusg.state != 'history' AND (('STARTDATEOFVDI' BETWEEN vmusg.start AND vmusg.plannedstop) OR ('PLANNEDSTOP' BETWEEN vmusg.start AND vmusg.plannedstop) OR ('STARTDATEOFVDI' < vmusg.start AND vmusg.plannedstop < PLANNEDSTOP))) GROUP BY h.id) hostWithTheVDIinUse where hostWithTheVDIinUse.hostUsage>=hostWithTheVDIinUse.capacity AND hostWithTheVDIinUse.hostID=anyHostWithTheVDI.id ) Then I get the host and I create the VMUSAGE object but I don't know how to obtain this information within a microflow. Any idea to obtain the solution?
asked
Manuel Guillen
1 answers
0
Replacing SQL by a microflow can be done, but it will not always perform satisfying.
You can start by replacing the SELECT count( h.id ) with a retrieve and a list aggregate (count). For the other select add the conditions as in the sql statement. And as Pieter says: please explain the purpose of the query.