How do I retrieve ALL the rows from a database

Ask the community
,
1

Hi,

I need to retrieve all the rows from a database but my current way of doing it only retrieves 50 rows. I know its because there is a default limit of 50 rows and I need to change that but as the database grows this number would change so hard coding it is not ideal. Can someone point me to a good tutorial video or an article that explains how would I go about doing this?

below is the code I use. How would I change it so it handles many records

wixData.qury("myCollection").find()
.then( (result) => {
	//do something with the result
})

Thank you in advance!

2

Hi, You can get access upto 1k records using .limit().

wixData.query("myCollection")
.limit(1000)
.find()
.then( (results) => {//do something with the result})

And you can use pagination bar element to get access to all the records.

3

This example demonstrates how to get all query results, bypassing the maximum limit of 1000, to iterate through all pages of query results.

async function retrieveAllItems() { 
  let results = await wixData.query("myCollection") 
     .limit(1000) 
     .find(); 
  let allItems = results.items; 
  while (results.hasNext()) { 
      results = await results.next(); 
      allItems = allItems.concat(results.items);
  } 
  return allItems; 
}

Ref: https://www.wix.com/velo/reference/wix-data/wixdataquery/limit

Hope this helps!

4

Why do you need to retrieve all of the records from the collection? As the collection gets bigger, it will impact the performance of your site.

You should first consider what you want to do with these records. For example, if you want to display them on the screen in a table or a Repeater, then you will get better results by paging or appending results from additional queries.

5

I understand getting all the records will impact the performance but this is a job that will be running in the backend on a given schedule so it will not affect user experience. Ultimately I will use things such as “updateDate” to filter the data so its doesn’t have to retrieve all the rows, but I want to make it so its able to handle any number of rows.

Thank you

6

@joshimayank123 If you need to retrieve all of the records at once, you can create a loop that gets a chunk (eg. 500) of records, appends them to an array, and keep looping till you’ve retrieved them all.

If you have a large database collection, Retrieving all of the records at once can bring the system to its knees. I know this from experience from mySQL systems I’ve worked on.