Database search only sees first 50 records?

I’ve been working on code to search a database and display the results in a table. For development purposes, I created a test database with 25 records and thoroughly tested my code, which did everything required of it. I then satisfactorily uploaded three pre-existing databases with sizes ranging from about 250 records to about 1100 records. The tables created appear to be complete and correct in every respect.

On changing all the applicable variables in my code, I found that only the first 50 records of the new tables was being searched. I might have overlooked something, but I can find no reason why this is happening. Searching with a null-string yields the first 50 records in the table, and searches correctly within those 50 records, but ignores the remainder.

Can the limit be set somewhere, or have I missed something obvious?

Any assistance would be appreciated.

Hey there,

By default, queries are limited to returning 50 items as documented here. You can change the limit using the limit( ) function.

Thank you for reply. I’ll take a look.

Sam,

I’ve taken a look and experimented by incorporating the limit( ) function in my code. As you say, and as the documentation indicates, it limits the number of returned items correctly to the value set in the parentheses. However, it also seems to limit the number of entries searched in the database table to the same number. I.e. If your database has 100 entries and you set the value to 10, it only searches the first 10 entries in the table and matches them as applicable, but it ignores the other 90 entries!

For two of the three databases I am working on, setting the limit to 1000 would work, as they currently have only around 250 and 600 records respectively. However, my largest database has over 1000 records, so searching the complete database is not possible. I would also like to search the entire database, but limit returned matches as per the intention of the limit( ) function.

Your thoughts on this would be much appreciated.

Rod

Hi Rod,

I am unable to reproduce this issue on my end. Could you share the query you are using? Or even the site where it is reproducible? That would help us to identify the issue faster.

Thank you!

Hi Giedrius,

Thank you for your reply.

My site is www.rodblunt.com

To see the problem, go to the COINSEARCHER menu and select LONG CROSS REVERSES. If you type in the search string AD, you will get 6 records returned. On this version I have the .limit() value set to 1000, and as there are only 256 records in the database all is well.

Following your post, I have copied the code across to another page, which I have named LONG CROSS (10), on which the only change I have made is to reduce the .limit() value to 10. If you now go to this page and type in the same search string AD, you will get 0 records returned. It should, of course display the same 6 records as on the page where .limit() is set to 1000, as 6 is less than 10.

Hope this is enough info to help you to identify the issue.

Regards
Rod

Hi Rod,

the trick in this case is, that the limit(n) applies to the Wix Data query results and not your custom filtering logic. You queries are actually fetching the whole collection. You filter results later in your code. If you where to use Wix Data filter options, it would try to match and get n matching results for you.

So, what happens:

  1. You ask for WixData to get all items and set the limit to say 10.
  2. WixData will fetch first 10 items from the database
  3. You will filter the matching items from those 10 results and pass those matching the filter on to the table.

As I understand .contains() will not fit you use case as you expect case insensitive match?

Every query returns WixDataQueryResult . It has two helpful methods hasNext() - to check if there are more results and next() to fetch another page of results (following n items). By using them you can traverse the whole collection.

Let me know if you need any further assistance.

Hi Giedrius

Thank you for your reply.

I came across the problem prior to using .limit() at all in my code, but the default value of 50 affected my results as soon as I switched from a test database of 25 records to the full database of 256.

I’ll take a further look at my code in line with your recommendations.

Regards
Rod