+- LiveCloud Forums (https://forums.livecloud.io)
+-- Forum: General (https://forums.livecloud.io/forumdisplay.php?fid=1)
+--- Forum: General (https://forums.livecloud.io/forumdisplay.php?fid=3)
+--- Thread: help with query (/showthread.php?tid=471)
help with query - stamatis - 10-07-2020
Hi all,
I’m trying to convert an existing project from a relational database.
I’m hoping I can get some help constructing a query that’s fairly straightforward in SQL...
In this scenario there are 3 tables:
- table 1 has a list of dates
- table 2 has encounter data that includes a foreign key for person and links to table 1 by date (table1.date = table2.date)
- table 3 has person data and links to table 2 with personID (table2.personID = table3.personID)
So basically selecting a date in from table 1 should retrieve all encounters in table 2 with the same date and in each retrieved result, the personID foreign key should retrieve person data from table 3 when presenting the data.
in SQL i’d just use a SELECT with JOIN statements to retrieve this in one go.
Is this feasible with liveCloud? Or would i need to construct an array running 2 separate queries?
Alternatively, should the data structure change to include all person data in the encounter table for example?
grateful for any pointers/suggestions...
RE: help with query - efrain.c - 10-08-2020
Hi Stam,
The following code will work with your existing data model. It will give you an array with the data for the people that match the criteria.
Code:
local tEncounterDataQueryA, tSelectedDate, tForeignKeys, tPersonDataA
put cdb_Query("date", "=", tSelectedDate, "encounterDataTable", "cloud", "recordData") into tEncounterDataQueryA
repeat for each key xRecordID in tEncounterDataQueryA
put tEncounterDataQueryA[xRecordID]["foreignKey"] & lf after tForeignKeys
end repeat
delete char -1 of tForeignKeys
put cdb_read("personDataTable", tForeignKeys, "cloud") into tPersonDataALet me know if you have any questions.
RE: help with query - stamatis - 10-10-2020
(10-08-2020, 04:56 PM)efrain.c Wrote: Hi Stam,
The following code will work with your existing data model. It will give you an array with the data for the people that match the criteria.
Code:local tEncounterDataQueryA, tSelectedDate, tForeignKeys, tPersonDataA
put cdb_Query("date", "=", tSelectedDate, "encounterDataTable", "cloud", "recordData") into tEncounterDataQueryA
repeat for each key xRecordID in tEncounterDataQueryA
put tEncounterDataQueryA[xRecordID]["foreignKey"] & lf after tForeignKeys
end repeat
delete char -1 of tForeignKeys
put cdb_read("personDataTable", tForeignKeys, "cloud") into tPersonDataA
Let me know if you have any questions.
Thanks Efrain - that pretty much matches what i was planning to do (i.e. run two different queries).
Is there a worthwhile speed benefit to changning the data model to include all data in a single table do you think?
Or would any benefit be too negligible to be worth the trouble?
RE: help with query - efrain.c - 10-12-2020
The speed benefits will vary depending on factors like network speed and physical location relative to the servers but it should be roughly by a factor of 2 since it'll be 1 network call instead of 2.
If this is an action that is performed often, you will see a worthwhile speed benefit if you sync the data from the cloud to the device on startup and run local queries instead of running cloud queries.