Please wix, I have about 15,000 lines of code and half of them are queries that return a total count of the database for each user or generally, I just noticed that the query limit is 50 and I have not even launched the website. WIX please, Is there any way to increase the default query limit to at least 1,000,000. Please my website really relies on queries and “results.items.length” to function, to start adding .limit(1000) will be extremely tasking plus 1000 is still extremely small. Please wix.
you can use recursive function logic or loops(e.g while loop) to queries all the items from the database. Also try using combo of Skip and limit it will help you.
This answer is right. The reason Wix does not increase limit is due to performance issue. If it is fast, why don’t Wix open this now? Just because the reality does not give you to load that long - and if your database have more than 200K items, it is likely to hang and not working at all. You are advised to use External Databases to save such large volume of data.
You can try the Velo Packages on backend. Velo has SQL package where the limit default is 50 and no explicitly maximum limit.
Hi there,
You can get all the items of a database with a loop like this:
return wixData.query('collection_name').limit(1000).find().then(async result => {
let all_items = [];
all_items = all_items.concat(result.items);
while (result.hasNext()) {
const temp = await result.next();
all_items = all_items.concat(temp.items);
}
return all_items;
})
These lines of code will get you all the items no matter how big the database is (until the request is timed out).
Hope this helps~!
Ahmad
Thanks for your responses, how is it possible to query a database and use a while loop to get more than the query limit. I really just need a simple solution because my code is soo much.
Please how do I enable this without changing my code?
Hi Ahmad, Thanks for the response, how do I add your solution to a code like this, so that the output of your code can be accessed to get the length, paste in a table and repeater or get the array first element?
$w ( “#dataset103” ). onReady (() => {
let itemObbj = $w ( “#dataset103” ). getCurrentItem ()
let daterQuery = wixData . query ( “FREEMIUM” )
. isNotEmpty ( “_createdDate” )
let ownerquery = wixData . query ( “FREEMIUM” )
. eq ( “_owner” , itemObj . _owner )
daterQuery . and ( ownerquery )
.limit(1000)
. find ()
. then ( async results => {
let x = results.items.length
let y = results.items[o].eligible
$w ( “#repeater” ).data =results.items
$w ( “#table” ).rows =results.items
)})
. catch (( err ) => {
let errorMsg = err ;
});
})
@chidiejike select “Code Files” on left panel and install the Velo package about SQL.
Yes, you need to change your code. SQL uses SQL statements (select, where, delete, group by).
Hi.
@ahmadnasriya I have followed the instructions you provided before to query a database collection, in my case, with more than 18K registers. This is the code:
export async function queryWithWhile ( ){
return wixData . query ( ‘misProveedores’ )
. limit ( 1000 )
. find ()
. then ( async result => {
let all_items = [];
all_items = all_items . concat ( result . items );
**while** ( result . hasNext ()) {
**const** temp = **await** result . next ();
all_items = all_items . concat ( temp . items );
}
**return** all_items ;
})
}
But, I do not receive the response and the function output shows me this message: “WebMethod request timed-out after 14 seconds… Did you forget to resolve a promise?”
BTW I have tried with Promise.all() method and it does not work either. I noticed that I only can get up to 3K registers in the result.
Am I doing something wrong?
Hi @mauvanceb , since the code I wrote queries the items in serial, it will not start the next query untill it finishes the previous one, which can take a long time to query a big database since the server will terminate the process after 14 seconds.
To overcome this issue, we first need to know the actual number of the items we’re going to get, then use the JavaScript map() function to create an array of promises, then finally use Promise.all() to call the promises in parallel.
I’ll give you a simple example:
// This function will get the total number of items that match your criteria
function getTotalItems() {
return wixData.query('col').eq('prop', value).count();
}
// This function will create an array of promises based on the total number
function getPromises(total) {
// Getting the number of promises
const number = total > 1000 ? ((total - total % 1000) / 1000) + 1 : 1;
// Creating an array of numbers
let numbers = [];
for (let i = 0; i < number; i++) { numbers.push(i) }
// Return an array of promises
return numbers.map(num => {
return new Promise((resolve, reject) => {
// Create the query with your own filters, I'll use the simplest one
const query = wixData.query('col1');
query.limit(1000).skip(num * 1000).find().then(x => resolve(x.items))
})
})
}
// This is the main function that will execute everything
async function getItems() {
try {
const total = await getTotalItems();
const promises = getPromises(total);
// Call the promises in paralel.
const raw_items = await Promise.all(promises);
// Combine the items in one array.
let items = [];
for (arrayItems of raw_items) {
items = items.concat(arrayItems);
}
// Return the final array.
return Promise.resolve(items);
} catch(err) {
return Promise.reject(err);
}
}
I haven’t tried it, so it might need a little bit tweaking, so let me know if you have any issues, and hope this helps.
Ahmad
I got it! Thanks
Hi Ahmad,
We followed this approach with the Variants table of WiX Stores. We have 37.000 rows in the concerning project. Your code works, but the while…hasNext() construct runs in an endless loop. To examine it, we stored the retrieved results in a table (with bulkInsert) and saw that the code duplicated all rows. We aborted the loop to get to an end, but it doesn’t stop running on the backend and produces over 700.000 entries (instead of 37.000).
Any ideas?
Regards,
Tom (dMorpheus)
This is our code:
export async function transferVariants ( rowsCount ) {
**return** wixData . query ( 'Stores/Variants' )
// fetch first items. 100 is the limit for variants.
. limit ( 100 )
. find ()
. then ( **async** ( result ) => {
// Init result count
**let** resultCount = result . length ;
// Display total count of variants (36.228)
console . log ( "total count: " + result . totalCount );
// Store first items
storeVariants ( result );
// Fetch and store all remaining results
**while** ( result . hasNext ()) {
// fetch next items
// V1: Delivers right results, but causes error after 10.000 rows. Error: WDE:0053, WDE:0054
//result = await result.next()
// V2: produces a lot of duplicates
**const** nextResult = **await** result . next ()
**const** itemsCount = **await** storeVariants ( nextResult );
// Log results
resultCount = resultCount + itemsCount ;
console . log ( resultCount + " variants processed" )
}
**return** 0 ;
})
// Handle error
. **catch** (( error ) => {
console . error ( error );
**return** - 1 ;
});
}
export async function storeVariants ( variants ) {
**let** allVariants = [];
// Extract subset of data for target table (as array of objects)
variants . items . forEach ( variant => {
**let** toInsert = {
'var_title' : variant . variantName ,
'prod_title' : variant . productName ,
'prod_variant' : variant . _id ,
}
allVariants . push ( toInsert );
});
// Insert object array in target table
**return** wixData . bulkInsert ( "variants_configurator" , allVariants )
. then (() => {
//console.log(allVariants.length + " variants stored");
**return** allVariants . length
})
. **catch** (( error ) => {
console . error ( error );
**return** - 1 ;
})
}
You even did not read the post till end right?
If you would read dthis post till end, you would recognize, that you are following the bad version of Ahmads 2-provided code-versions.
Read the post till end and then try to find out which one is the better version.
…
…
…
Also please do not bump-up old posts.
Always open your own ones with your own in detail described issue-description.
You are right. We are timely under pressure; thus, we grabbed the first possible solution. Now, we will read until the end. Thx Velo-Ninja. 