Fastest query to find rows of a collection that lack values in a multi-reference field

Ask the community
,
1

My current methodology looks like this:

    _studentsMissingClasses = []
    _studentsMissingContacts = []
    _unusedContacts = []
    for (let i = 0; i < _students.length; ++i) {
        const student = _students[i];
        const classes = await wixData.queryReferenced("Students", student, "classes");
        const contacts = await wixData.queryReferenced("Students", student, "contacts");
    }
    for (let i = 0; i < _contacts.length; ++i) {
        const contact = _contacts[i];
        const matchedStudents = await wixData.queryReferenced("StudentContact", contact, "Students-2");

While this works and gives an accurate accounting, it is very, very slow (many minutes where _students has 164 records, _classes has 8 records and _contacts has 222 records)

Is there a faster way to do this? I would think wix-data would allow querying the reference fields for null but I don’t see that.

2

Hi,
Can you please:

  1. Copy & paste the code from light mode (not dark), so it will be more readable here.

  2. Put the code in code block

  3. Highlight the part of code that we should focus on.

  4. Remove unneeded code like console.log’s

Thanks.

3

Done

4

First of all, I’d create a backend function (in a jsw file ) to run the queries.
You don’t want to go back and forth over and over again, it takes too much time and too many resources.
The query itself depends on how many students you have. If it’s not too many I’d query them in parallel instead of waiting for each student separately.

For example:

// backend/students.jsw
import wixData from 'wix-data';
export function getStudentAddinalInfo(studentIdArray){
return Promise.all(studentIdArray.map(id => wixData.queryReferenced("Students", id,"classes")))
.then(res => {
return res.map(e => e.items);
})
}

//and you can combine other queries into this promise all as well
5

My experience so far is that when you query the same collection too frequently you get continuous errors until you slow down the queries. I’ve actually had to implement a retry mechanism to slow down queries until the server is happy again.