UPDATE (9/7/2020):
In reading more about the difference between include() and queryReference(), I realized the code I had originally written had an extra query in it for each device referenced by SITMembers. I was performing an unnecessary third query on the SITDevices table for each SITMember referenced in each SITConnections returned row. It was unnecessary, because I already had this information by virtue of the include() argument from the call into SITMembers. So the approach is now:
- Query SITConnections with an include(), in order to get the referenced email address into SITMembers for each connected member.
- For each member in the resulting list, query SITMembers with an include() in order to get the associated list of SITDevice rows.
- Create an array of the device information and add it to the JSON object that is returned.
No need to make that third call into SITDevices.
I have refactored the code to this:
breakNow = false;
console.log("connections function called.");
response.body = {[RESULT_KEY]:false}; // default false
memberEmail = request.query[EMAIL_ARG];
console.log("Got email argument: " + memberEmail);
deviceId = request.query[DEVICEID_ARG];
// Check the token for deviceId
await isLoginForDeviceValid(deviceId, memberEmail, response);
if (!response.body.result) {
// Failed to validate caller
response.body = {[RESULT_KEY]: false, [ERRORMSG_KEY]:"Invalid website request detected for: " + memberEmail};
return ok(response);
}
connectionList = new Array();
await wixData.query(SITCONNECTIONS_TABLE)
.eq(MEMBEREMAIL_FIELD, memberEmail)
.include(CONNECTEDMEMBER_FIELD) // This is a reference field, brings in all the SITMember data for that connected member
.find(options)
.then( (connections)=>{
if ( connections.items.length > 0 ) {
console.log("Found " + connections.items.length + " SITConnections for member with email: " + memberEmail);
connectionList = connections.items;
} else {
console.log("Did not find any connections for member: " + memberEmail);
response.body = {[RESULT_KEY]: false, [ERRORMSG_KEY]:"Did not find any connections for member: " + memberEmail};
breakNow = true;
}
})
.catch( (err) => {
console.log("Got error: " + err);
response.body = {[RESULT_KEY]: false,[ERRORMSG_KEY] : err.message};
breakNow = true;
});
if ( breakNow ) {
return ok(response);
}
// At this point, 'connectionList' has all members connected to 'memberEmail',
// including the SITMember data of the connected member.
for (var connectionCntr = 0; connectionCntr < connectionList.length; connectionCntr++) {
var connection = connectionList[connectionCntr];
let connectedMemberDevices = new Array(); // this may end up being empty.
let currentMemberDeviceIDs = null;
// Get the devices for each connection
await wixData.query(SITMEMBERS_TABLE)
.eq(ID_FIELD,connection.connectedMember._id)
.include(MEMBERDEVICES_FIELD)
.find(options)
.then( (results) => {
if (results.items.length > 0) {
console.log("Found devices for member");
currentMemberDeviceIDs = results.items[0].memberDevices;
} else {
console.log("Did not find any devices for member with ID: " +
connection.connectedMember._id);
currentMemberDeviceIDs = null;
}
})
.catch( (error) => {
console.log("Got error in query for member: " +
connection.connectedMember.email + " error was: " + error);
});
if ( currentMemberDeviceIDs !== null ) {
for (var currentDevicesCntr = 0;
currentDevicesCntr < currentMemberDeviceIDs.length;
currentDevicesCntr++) {
// Add the devices info from the referenced field
connectedMemberDevices.push( {[DEVICEID_FIELD]:currentMemberDeviceIDs[currentDevicesCntr].deviceId,[DEVICEDESCRIPTION_FIELD]:currentMemberDeviceIDs[currentDevicesCntr].deviceDescription,[FCMTOKEN_FIELD]:currentMemberDeviceIDs[currentDevicesCntr].fcmToken});
}
}
// Clone the array, even if empty
connection["connectedMemberDevices"] = connectedMemberDevices.slice(0);
}
response.body = {[RESULT_KEY]: true, [CONNECTIONS_KEY] : connectionList};
return ok(response);
This has sped up the query by almost 50%. Any other comments more than welcome for how I can further optimize.
I have a mobile app that communicates with my website’s back-end using the standard http-functions.js file. This app facilitates sharing media (pictures, videos) between members. One of the APIs that I surface for a standard GET request is for the logged in user of my app to retrieve a list of other members and their registered devices to whom he/she is “connected”. I am starting to see occasional “timed-out” errors when this request is made from my app.
I have read through the various posts in this community forum of others that have experienced this, and noted that in some cases the queries being made to the DB were not efficient, or the approach was not what is recommended. I am wondering if I am somehow making the same mistake, because my data set is quite small at this point (still in development mode). I only have a total of about 15 contacts (testers), and the relationships between them are generally 1:1, and there are only one or two individuals with more than one registered device.
The structure of my database is one table called SITMembers (some date fields, name, email). It also has a ‘memberDevices’ field which is a multi-reference field (because a user can have more than one registered device), that relates to the SITDevices table. SITDevices has one entry per each mobile device that the user has registered my app with for accessing the site. A third table called SITConnections manages the relationship between SITMembers, and it has a reference field “ConnectedMember”, which points back to the SITMembers table. Each record in SITConnections contains a “connection status” between two individuals. So for example, when person “A” requests a connection from person “B”, one record is created in SITConnections that has its “MemberEmail” field as member “A”'s email address, and the “ConnectedMember” as “B”'s email address, and a “status” field which is a numeric value. These values get updated as the request is made from A->B (REQUEST_CONNECT), and B confirms the request of A (CONFIRM_CONNECT).
Here is the code of my API call that handles the request to gather connected user info:
breakNow = false;
console.log("connections function called.");
response.body = {[RESULT_KEY]:false}; // default false
memberEmail = request.query[EMAIL_ARG];
console.log("Got email argument: " + memberEmail);
deviceId = request.query[DEVICEID_ARG];
// Check the token for deviceId
await isLoginForDeviceValid(deviceId, memberEmail, response);
if (!response.body.result) {
// Failed to validate caller
response.body = {[RESULT_KEY]: false,
[ERRORMSG_KEY]:"Invalid website request detected for: " + memberEmail};
return ok(response);
}
let connectionList = new Array();
await wixData.query(SITCONNECTIONS_TABLE)
.eq(MEMBEREMAIL_FIELD, memberEmail)
// This is a reference field, brings in all the SITMember data and devices registered
.include(CONNECTEDMEMBER_FIELD)
.find(options)
.then( (connections)=>{
if ( connections.items.length > 0 ) {
console.log("Found " + connections.items.length + " SITConnections for member
with email: " + memberEmail);
connectionList = connections.items;
} else {
console.log("Did not find any connections for member: " + memberEmail);
response.body = {[RESULT_KEY]: false, [ERRORMSG_KEY]:"Did not find any connections
for member: " + memberEmail};
breakNow = true;
}
})
.catch( (err) => {
console.log("Got error: " + err);
response.body = {[RESULT_KEY]: false,[ERRORMSG_KEY] : err.message};
breakNow = true;
});
if ( breakNow ) {
return ok(response);
}
// Review the connection list and add in the devices for the connectedMember
for (var connectionCntr = 0; connectionCntr < connectionList.length; connectionCntr++) {
var connection = connectionList[connectionCntr];
let connectedMemberDevices = new Array(); // this may end up being empty.
let currentMemberDeviceIDs = null;
// Need the devices for each connection
await wixData.query(SITMEMBERS_TABLE)
.eq(ID_FIELD,connection.connectedMember._id)
.include(MEMBERDEVICES_FIELD)
.find(options)
.then( (results) => {
if (results.items.length > 0) {
console.log("Found devices for member");
currentMemberDeviceIDs = results.items[0].memberDevices;
} else {
console.log("Did not find any devices for member with ID: " +
connection.connectedMember._id);
currentMemberDeviceIDs = null;
}
})
.catch( (error) => {
console.log("Got error in query for member: " + connection.connectedMember.email + "
error was: " + error);
});
if ( currentMemberDeviceIDs !== null ) {
for (var currentDevicesCntr = 0;
currentDevicesCntr < currentMemberDeviceIDs.length;
currentDevicesCntr++) {
var currentDeviceId = currentMemberDeviceIDs[currentDevicesCntr]._id;
// Now find it in the MemberDevices table
await wixData.query(MEMBERDEVICES_TABLE)
.eq(ID_FIELD,currentDeviceId)
.find(options)
.then( (results) => {
if (results.items.length > 0) {
console.log("Found a device in MemberDevices using ID: " + currentDeviceId);
var item = results.items[0]
connectedMemberDevices.push({[DEVICEID_FIELD]:item.deviceId,
[DEVICEDESCRIPTION_FIELD]:item.deviceDescription,
[FCMTOKEN_FIELD]:item.fcmToken});
} else {
console.log("Did not find a device in MemberDevices using ID: " +
currentDeviceId);
}
})
.catch( (error) => {
console.log("Got error in query for existing devices: " + error);
});
}
}
// Clone the array, even if empty
connection["connectedMemberDevices"] = connectedMemberDevices.slice(0);
}
response.body = {[RESULT_KEY]: true, [CONNECTIONS_KEY] : connectionList};
return ok(response);
The "isLoginForDeviceValid() call does a lookup based on the member email and ID for the mobile device making the call to double-check that this user is one we are aware of and is valid, using this approach:
await wixData.query(SITMEMBERS_TABLE)
.eq(EMAIL_ARG, memberEmail)
.include(MEMBERDEVICES_FIELD)
.find(options)
.then( (results) => {
<CHECK THE RESULTS TO INSURE WE KNOW THIS PERSON/DEVICE HERE>
In reading some of the other posts that have gotten this error, the recommendation seems to be to not do that, and instead make the query asynchronously and handle the asynchronous response - however these posts have all been associated with making such a query from within a webpage hosted on Wix. That is not my use case.
Because I am calling this API from a background thread in my mobile app (which handles the asynchronous aspect in the app already and is built to wait for the response before calling back into the UI), the only way I know to accommodate this is by using async/await.
If there is a way to provide asynchronous support from a GET API call back to the outside world (outside of Wix website) from within Wix itself I haven’t found an example of this technique in the Corvid docs - is that what I should be doing, and if so, how do I do it?
Or is there something else I can do to better optimize the queries I am making?
Thanks!