How can I properly specify referenced fields to bound a query?

Ask the community
,
1

I have two tables - one is called SITMembers and holds a single row for each member of my site, the primary key is email address.

My second table is called SITConnections and contains one field called MemberEmail which is an email address, and also contains a reference field back into SITMembers called ConnectedMember. This is a single reference field.

One entry in SITConnections defines the relationship between one member (defined by email address in MemberEmail) and another (referenced by ConnectedMember).

In my processing, I need to be able to update the status of a particular connection between person A and person B.

I have written a function that accepts the email address of the individual A and the email address of the SITMember to whom they are connected. I want to be able to efficiently use these two email addresses in order to find the specific row in the SITConnections table that needs to get updated.

Currently I am doing this by first performing a query against SITConnections using the email address of person A with an .include() to bring in the referenced info (which includes email address) of all individuals to whom A is connected. I then perform a loop against this result to match the email address of the second email address passed in to this function in order to find the row that I need to work on.

The initial query code looks like:

 // Get connections for member accessing this function 
 try {
     memberConnections = await wixData.query(SITCONNECTIONS_TABLE)
         .eq(MEMBEREMAIL_FIELD, memberEmail)
         .include(CONNECTEDMEMBER_FIELD) // This is a reference field, brings in all the SITMembers data 
         .find(options);
 } catch (error) {
     response.body = {[RESULT_KEY]: false,[CONTACTSTATUS_KEY] : CONTACT_CONNECTEDSTATUS_UNKNOWN,[ERRORMSG_KEY]:"I had a processing error at the website, try again.",[CONTACTDEVICES_KEY]:[]};
     return ok(response);
 }

Then the portion where I loop through this result to match against the second email looks like:

 // Try and find the member entry
 console.log("Found " + memberConnections.items.length + " SITConnection(s) for member " + memberEmail );
 for (x = 0; x < memberConnections.items.length; x++) {
     if ( memberConnections.items[x].connectedMember.email === contactEmail) {
        //console.log("Found connection: " + contactEmail + " status is: " + memberConnections.items[x].connectionStatus);
        memberConnectionItem = memberConnections.items[x];
        // Found it - exit              
        break;                              
     }
 }

Obviously, this is not very efficient. Is there a way I can combine the two email addresses into a single query of the SITConnections table, even though the email address of the second party is contained in the referenced collection?

I have looked at both .include() and .queryReferenced(), but they both seem to do the same thing (albeit with slight differences), which is to provide a way to return data in the referenced collection. They don’t see to provide a way to bound the query using data in the referenced collection.

Is there a way to do this?

What I have in mind is something like:

memberConnections = await wixData.query(SITCONNECTIONS_TABLE)
         .eq(MEMBEREMAIL_FIELD, memberEmail)
         .eq(EMAIL_FIELD_FROM_REFERENCED_TABLE, connectedMember.email)
         .find(options);

Thanks!