finding rows in one collection that do not exist in another collection

Ask the community
,
1

I need to retrieve all rows in one collection that do not exist in another collection. This is the code i use, functionally it used to work o.k but now i get “namespace” errors on it and i was told it’s because there are too many calls to the server. So can anyone help me improve this code?

let results = await wixData.query( “chairs” )
.descending( “_createdDate” )
.eq( “status” ,“open”)
.ne( “qualityStatus” , “unknown” )
.lt( “sentDate” ,sinceDate) *3 days ago
.ge( “sentDate” ,notOlderThan) * 3 month ago
.find()
if (results.length > 0 ) {
results.items.forEach( (row) => {

                    wixData.query( "chairsStatus" ) 
                        .eq( "internalComment" , "TBD" ) 
                        .or(wixData.query( "requestStatus" ).eq( "internalComment" , "Not Matching" ) )                
                       .eq( "requestId" ,row._id) 
                        .eq( "status" , "open" ) 
                    .find() 
                        .then( (statusResults) => { 
				**if**  (statusResults.length ===  0 ) { 
                             		   requestTable.push(row) 
                           		 } 
                         		  arrayCounter = arrayCounter +  1 ; 
	                   . **catch**  (( error) => { 
                                console.log(error) 
                        } ); 
                }) 
        }
2

It’ll be much easier (and with better performance) if you use a reference field that joins the collections.

3

There is a reference field that joins the collections. it is the requestId which is part of the second query.

4

So run a single query and use .include(’ requestId’ )

https://www.wix.com/corvid/reference/wix-data.WixDataQuery.html#include

5

I’m not sure how it can help me since it’s a one to many relationship between the first collection and the second one and I need to bring all rows from collection1 that DO NOT have at least one row in collection2 with the specific createrias.
If you can share some code example with .include on how to do this, i will highly appreciate it.

6

Then you can probably use
queryReferenced:
https://www.wix.com/corvid/reference/wix-data.html#queryReferenced

If you still want to use these 2 queries, it’ll be faster if you run them in the backend (so you’ll not have to go to the servers twice).

You can also create an afterQuery data hook for the chairs collection your data.js to retrieve the 2nd query
.
Any way, for the second query you can add .limit(1), because you only want to retrieve a single result.
Another option for the 2nd query is to use .distinct(" requestId ") instead of .find().

7

Thank you for your suggestions, but i need more help since they don’t seem to solve the problem, unless i have missed something

  1. I tried the queryReferenced, but it needs a reference to a specific item in the second parameter, so this means I still need to go over my first query collection and apply the " results.items.forEach( (row) " which causes the namespace error. If I could just place the field name in this second parameter it would do the job… but couldn’t figure out if possible

  2. adding a limit of .limit(1) doesn’t help since that collection brings 2-3 rows and it is not the efficiency problem i’m encountering

  3. again, using the .distinct doesn’t solve the namespace problem

  4. About using them in the backend, i don’t mind trying this but how can I transfer parameters from my client and populate my table from a query that runs in the backend. Can you please refer to some code examples about doing this?
    My code work fine functionality wise, the problem is the namespace error due to too much data processed.

8

First of all, you should consider having an additional filed in the first collection “doesExistInChairStatus” (Boolean) and update it in advance. That will make everything easier.

If you don’t want to have this field and you want to use a backend function you can do one of the followings:

  1. in backend/data.js create a hook like this:
import wixData from 'wix-data';
//....

export function chairs_afterQuery(item, context) {
return wixData.query(chairsStatus).eq('requestId',row._id)
.limit(1)
.find()
.then(r => {
if(r.items.length > 0)
{
item.hasStatus = true;
return item;
}
item.hasStatus = false;
return item;
})
}

//and on the front end:
wixData.query("chairs")                .descending("_createdDate").eq("status","open")                .ne("qualityStatus", "unknown")                .lt("sentDate",sinceDate)              .ge("sentDate",notOlderThan).find()
.then(r => {
let items = r.items;
items = items.filter(e => !e.hasStatus);
})
  1. you can create a jsw on the backend and then you can call it from the front end:
    for example:
    //front end:
import { getChairs } from 'backend/chairs.jsw;
$w.onReady(() => {
getChairs(/*pass parameters*/)
.then( res => {
//do whatever you whish
})
})