Dataset Query for 3000 Items

Ask the community
,
1

I have an issue where i am trying to query a dataset of over 3000 items and return the results to a drop down menu. The issue i’m having is that the query will only lookup the 1st 1000 rows of the dataset. Below is my code, how do i look up the full 3000 rows and return the results to a single drop down menu ?

import wixData from ‘wix-data’;

$w.onReady( function () {

uniqueDropDown();

});

function uniqueDropDown() {

wixData.query("Collection_Database") 

    .limit(1000)  

    .ascending("Column1") 

    .find() 

    .then(results => {

const uniqueTitles = getUniqueTitles(results.items);

        $w("#uniqueDropDown").options = buildOptions(uniqueTitles); 

    });

function getUniqueTitles(items) {

const titlesOnly = items.map(item => item.Column1);

return [… new Set(titlesOnly)];

}

function buildOptions(uniqueList) {

return uniqueList.map(curr => {

return { label: curr, value: curr };

    }); 

}
2

Trying removing the following from your code:

.limit(1000)

and see if that does what you want.

3

No that does not enable the query to search 3000 items. Similarly if i change “1000” to “3000” then it does not return any results it just freezes.

4

There may be a limit in Wix WRT the # of items in a dropdown list …

Even if there isn’t, 3000 is a lot of items for a user to scroll through to select from; you could first filter down the list …

5

They are not scrolling down through 3000 items in the dropdown list, as you can see from my above code I am only showing unique values in the dropdown. There are 3000 items that have to be queried in the dataset but only unique items are returned to the dropdown.

6

That is a Lot of items to search and would cause a freeze (can take 5 + seconds on my site with 50 items) ,
one alternative could be multiple pages of items in the dropdown?

7

Hi All!

The limit of a query is by default = 50 with a maximum of 1000 returned values.
It’s currently not possible to return more than 1000 items at once.

You can work around it by adding the .skip() method to your query and set the number of items you want to skip before the query starts to return items from the collection.

Hope it helps!

Doron.

8

How does the skip method allow me to query a data set of 3000 items and return all unique values to a dropdown ?

9

@mikemoynihan99 You can use one query with .limit(1000),
then another query with .skip(1000) .limit(1000),
and last query with .skip(2000) . limit(1000).

Populate the dropdown with the results of the three queries and voilà! You’ve got 3000 items in your dropdown.

Doron.

10

@doron-alkalay What would that code look like ?

11

If you wanted to fetch 3000 elements, you could:

async function fetchData() {

  const firstPage = await wixData.query('collection')
    .limit(1000)
    .find();

  const secondPage = await wixData.query('collection')
    .limit(1000)
    .skip(1000)
    .find();

  const thirdPage = await wixData.query('collection')
    .limit(1000)
    .skip(2000)
    .find();

  const allItems = firstPage.items.concat(secondPage.items).concat(thirdPage.items);

  return allItems;
}

By the way, I am using async / await feature, to make it more readable compared to nesting .then(…) statements.

There is also an alternative way to do this using next().

async function fetchData() {
  
  let result = await wixData.query('collection')
    .limit(1000)
    .find(); // Result is WixDataQueryResult and has next() method

  let allItems = result.items;

  while (result.hasNext()) { // Repeat while there is a next page
    result = await result.next(); // We fetch next page the easy way, without using skip and limit
    allItems = allItems.concat(result.items); // We add results of the next page to allItems
  }

  return allItems; // In the end we return the list of items from all pages
}

This will fetch all of the items in the collection, no matter the count. However, for a sufficiently large collection, this might take a while, so be sure not to use it with very large collections as the performance of the site will suffer.

12

It took me a few while to get it working but finally got it working today. Thank you very much for this.

13

Hi Mike! How you doing? I was wondering if you could at last fetched 3000 items in a single dropdown.

'Cause I have more or less the same situation here and I could only fetch almost 200 items per dropdown.

Do you still have the code of this?

Thank you in advance!

14

@imnimarketing

The below code will search a database column (“fieldKey”) for items that match the value you type into your input box.

Change the following variables : (DatabaseID, fieldKey, input1, dropdown1)

async function getUniqueListFromDatabase() {

const List1 = await wixData.query(“DatabaseID”)

        .contains("fieldKey", $w("#input1").value) 

        .limit(1000) 

        .ascending("fieldKey") 

        .find()

const List2 = await wixData.query(“DatabaseID”)

        .contains("fieldKey", $w("#input1").value) 

        .limit(1000) 

        .skip(1000) 

        .ascending("fieldKey") 

        .find()

const List3 = await wixData.query(“DatabaseID”)

        .contains("fieldKey", $w("#input1").value) 

        .limit(1000) 

        .skip(2000) 

        .ascending("fieldKey") 

        .find()

const List4 = await wixData.query(“DatabaseID”)

        .contains("fieldKey", $w("#input1").value) 

        .limit(1000) 

        .skip(3000) 

        .ascending("fieldKey") 

        .find()

const mergedLists = List1.items.concat(List2.items).concat(List3.items).concat(List4.items)

const uniqueItems = getUniqueTitles(mergedLists);

    $w("#dropdown1").options = buildOptions(uniqueItems);

function getUniqueTitles(items) {

const titlesOnly = items.map(item => item.fieldKey); // don’t forget to change this field key

return [… new Set(titlesOnly)];

    }

function buildOptions(uniqueList) {

return uniqueList.map(curr => {

return { label: curr, value: curr };

        }); 

    } 
}
15

thanx your a legend mate!

16

As a developer and an analyst dealing with large data sets, having a limit is really annoying. But, that being said, having a limit is also beneficial when trying to bring all that data onto the screen. I find anymore than 100 visible rows tends to really slow the performance.

My recommendation would be to review your data structure and build efficient indexing using relational data.

17

@mikemoynihan99
Hi, Has anyone been able to run this code?. i’m trying to get this thing work for more than a 2 days.
I have a form with more than 3K items and i’m trying to add this code in order to get search box and let the user type what is looking for.
I have an input text “input1” and dropdown menu “dropdown1”.
should i put this code under input1 “keypress” event?.
or should it put it inside the function “when page is ready”?.
Is there a need to connect the input1 field to any dataset?

Thank you!

18

@meirhasin You are asking a question on a very old post and your question will either not be seen or won’t be understood. Please repost your question in a new post and clearly state what you would like to accomplish.