I wanted to avoid loading complete records from a collection (in some cases with many fields and extensive data) for a list of _ids in order to update a single field using Wix data query.
I’d hoped Wix SQL would make this easy, but the “in” operator does not appear to be supported–so I’m doubting that would be an efficient way to update a list of records (i.e. , without “in” operator, perhaps looping over the _id list and perform SQL update one-by-one.)
With Wixdata.query, I would retrieve all the records needing to be updated (using “hasSome” operator with the _id list), then in a loop update the single field in each of those records, and then use bulk update of those records to the collection.
This latter is what I was seeking to avoid, but is it the best option? Would it be more efficient to use a loop with Wix SQL? In my current case, I just need to assign the same value in a single field for every _id in the list, so perhaps use a single-field “temp” collection to store the list of _ids and then perform the update using a join on the _ids between the collection and the “temp” collection?
I’m not clear how much overhead there is in querying and manipulating entire records (with many fields) with Wix query vs. alternatives using only a list of _ids with Wix SQL. If Wix SQL is just an overlay interface on the Wix data API, perhaps no efficiency gain in using Wix SQL in my case?
Indeed I believe Wix SQL is just an overlay on top of wixData API to help SQL program onboard.
Your solution, query a bulk of value (about 50) update the value then bulkUpdate them is the recommended approach
If the value is shared across many record you might want to split that field into its own collection and use a reference field. Therefor you only update one record.
If you collection is large, it also a good thing to split it into multiple collections so that you query only the data you need to each query
Your additional comments about data structure point to further concerns.
What would you consider a “large” collection? My collections will have some 1000-2000 records each, where each collection is used to populate dynamic pages with filtered datasets to display subsets from the collection. That seems like a modest number of records in a data table and, in this case, it is not practical to split them up. But what collection “size” (number of rows? rows# x fields# ? ) will cause significant performance degradation?
Also, I am unclear about limiting Wix data query results to 50 records. What happens if I return 200 records (or more) for backend processing? Is there a number above which it is imperative that the data queries be parsed (I expect using the “limit” and “skip” query functions)?
I would try to build the app for the best UX first (no need to download 2000 items to display only 50 at the time) then adapt it so that performance is enhanced, IF REQUIRED .
Keys factor are the complexity of your request (filtering) as well as the size of each record (a collection with 5 column vs a collection with 50 column).
The main benefit of splitting data across multiple collection is that you only query data you need instead of returning 45 columns of unused data.
It also helps to separate public data and private data.
And, in your case, if multiple records share the same ressource, you only update 1 record instead of multiple record.