checking array length doing a wixdata.query

Ask the community
,
1

So ive been playing with making more advanced queries for wixdata to reduce after query filters, So example we have a call log and in there I use array type to make a object array with history of the calls.

[
  {
    "InsertDate": "2022-09-13T23:05:16.245Z",
    "CallResult": "VM"
  }
]

Now doing this of course result in i cant use basic queries so ive been testing what I can do and not. resulting in this actually works

let possibleCallhistoryToNotReCall = ["Spoke - Resolved", "Spoke - in process to resolve", "Spoke - Churn risk", "Spoke - Canceled", "Spoke - don't call again"];
    
    //here is the query start i wont share because its not needed
    
    query = query.not(wixData.query('theContentIDofMyCallLog').hasSome('callHistory.CallResult', possibleCallhistoryToNotReCall));

As you can see im actually able to check for the object array contains a value.

But then we have a scenario of VM (voice mail) and if it happens 3 times we should also not include this. Now i can take the result and filter them but i want to find a way to make wixdata query able to do it for me.

So my initial thought was

query = query.lt('callHistory.length', 3)

unfortunately that does not really work. and neither does any of my other attempts
So anyone have an idea to check the array length of a wix query array item.

2

One of solution is to use a beforeInsert/beforeUpdate to add a computed field that keeps track of the lenght.

function beforeInsert/Update(item) {
   item.callHistoryLength = item.callHistory.lenght
   return item;
}

Then you can do you query based on the computed field

query= query.not(wixData.query('theContentIDofMyCallLog').hasSome('callHistory.CallResult',possibleCallhistoryToNotReCall).or(query.gt("callHistoryLenght",3));
3

That would be the most natural way of doing it, but does involve adding a new field for the value.
Kinda hope there is a trick to query with the length im missing :smiley:

4

Have you tried to save the field as an object rather than a array?

5

its an object array as i have multiple objects cotaining the insert time and the result of call :slight_smile: using the whole “push mechanic” of an array :smiley: