Exceeding query limit when filtering

Ask the community
,
1

Hello. I’m currently working on (and learning!) filtering a collection via numerous dropdowns. I’ve adapted a piece of code that consolidates the functions which is working fine so far

My issue is the collection i’m working with is ~1900 rows of data so i’m hitting the query limit. I’ve found various functions ( https://www.wix.com/corvid/forum/community-discussion/dataset-query-for-3000-items ) that can bypass the limit but can’t for the life of me figure out where i’m supposed to insert it into my code.

This is my code so far…

import wixData from 'wix-data';

$w.onReady(function () {
    uniqueDropdown("collectionName", "dropdown1", "fieldKey");
});

export function dropdown1_change(event, $w) {
 let fvalue1 = $w('#dropdown1').value;
    uniqueDropdown("collectionName", "dropdown2", "fieldKey", "FilterKey", fvalue1)
}

export function dropdown2_change(event, $w) {
 let fvalue2 = $w('#dropdown2').value;
    uniqueDropdown("collectionName", "dropdown3", "fieldKey", "FilterKey", fvalue2)
}

function uniqueDropdown(collectionName, dropdown, fieldKey, FilterKey, FilterValue) {
    $w("#" + dropdown).disable();
    $w("#" + dropdown).value = '';
 var query = wixData.query(collectionName);
 if (FilterKey) {
        query = query.eq(FilterKey, FilterValue);
    }
    query
        .limit(1000)
        .find()
        .then(results => {
 const uniqueTitles = getUniqueTitles(results.items);
            $w("#" + dropdown).options = buildOptions(uniqueTitles);
            $w("#" + dropdown).enable();
        });

 function getUniqueTitles(items) {
 const titlesOnly = items.map(item => item[fieldKey]);
 return [...new Set(titlesOnly)];
    }

 function buildOptions(uniqueList) {
 return uniqueList.map(curr => {
 return { label: curr, value: curr };
        });
    }
}

Thanks in advance!

2

Are you trying to search/filter 1900 lines? Or do you want to actually return 1900 lines.

It is possible to search a database that has a million lines.

However, if you want to return more than a 1000 lines, then you’ll need to use the solution that Giedrius posted .

3

Hi

I’m trying to filter through 1900 lines. The three dropdowns will filter down to one result.

That’s the solution i’ve been trying to use but unfortunately I can’t figure out where it belongs within my code.

I believe it should be somewhere in this section…?

function uniqueDropdown(collectionName, dropdown, fieldKey, FilterKey, FilterValue) {
    $w("#" + dropdown).disable();
    $w("#" + dropdown).value = '';
 var query = wixData.query(collectionName);
 if (FilterKey) {
        query = query.eq(FilterKey, FilterValue);
    }
    query
        .limit(1000)
        .find()
        .then(results => {
 const uniqueTitles = getUniqueTitles(results.items);
            $w("#" + dropdown).options = buildOptions(uniqueTitles);
            $w("#" + dropdown).enable();
        });
4

@jbarton7855 Sorry, but I still don’t understand. Please state what you are trying to accomplish. Which one of the following?

  1. return 1900 lines as a result

  2. search/filter 1900 lines

5

Apologies, #2…search/filter 1900 lines

Thanks

6

You don’t need “that solution”. Wix Data supports very large database collections which can be filtered and searched. A collection of 1900 records is considered small and there would be no problem filtering or searching such a collection.

You apparently have an issue with the query which you will have to figure out.

7

Okay, to clarify; when I use dropdown1 to see the unique values in the selected fieldKey, i’m in fact only seeing the unique values within the first 1,000 lines, not the full 1,900.

Altering this bit is what I need to focus on?

 var query = wixData.query(collectionName);
 if (FilterKey) {
        query = query.eq(FilterKey, FilterValue);
    }
    query
        .limit(1000)
        .find()
        .then(results => {
8

No, the query is performed on the entire collection. The .limit(1000) only limits the number of items returned by the query.

9

@yisrael-wix Ah okay so I think my terminology isn’t up to scratch here, i’ve obviously misunderstood your question earlier. I apologise, i’m very new to using code.

So going back to your question earlier I need to “1. return 1900 lines as a result”.

10

@jbarton7855 What are you going to do with 1900 lines? Most likely you don’t need all 1900 at once, rather you should provide something like paging.

11

Within those 1,900 lines there are only say 10 unique values. From the selection made in dropdown1, dropdown2 will then show the next available options and so on.

Here’s a snip of the collection i’m working with…

974×420 101 KB

and the dropdowns:

757×509 59.2 KB

The idea is to get the user to make a selection which narrows down to the “Groups” field, then a repeater/table will populate based on the “Sizes” within that “Group” field.

12

So you’re right, your " terminology isn’t up to scratch". But that’s OK, your learning…

What you’re trying to do is to filter/search a collection of [about] 1900 records, and you expect about just a few as the result of the query.

What you will need to do is to include in your filter the selected value (if there is one) of each of the dropdowns. So it would be something like this:

var query = wixData.query(collectionName);
if (FilterKey1) {
   query = query.eq(FilterKey1, FilterValue1);
}
if (FilterKey2) {
   query = query.eq(FilterKey2, FilterValue2);
}
if (FilterKey3) {
   query = query.eq(FilterKey3, FilterValue3);
}
query
        .limit(40)
        .find()
        .then(results => {
            const uniqueTitles = getUniqueTitles(results.items);
            $w("#" + dropdown).options = buildOptions(uniqueTitles);
            $w("#" + dropdown).enable();
        });

Disclaimer: the above code has not been test, it’s for illustration purposes. But that’ll give you the general idea on how to build your filter.

See Querying Your Collection for more information on building a query.

13

@yisrael-wix Thanks for your help, i’ll work at integrating something like you suggested,