Collection with 1 million rows

Ask the community
,
1

I have a database table csv with 1 million rows. I would like have a user type in a word which would filter this table down to only top 20 or so results. The limit for the import is really small (only 10,000 rows). I really don’t want to make 100 collections.

How can I use this database? Can I connect with Amazon S3? What are my options? 10,000 rows is far from a “database”.

2

A collection has no size limit - with or without Premium.

You can import 10K records at a time (to the same collection) to fully populate your collection.

3

Is there any way to up that or any alternative I have? I don’t really want to manually import over 100 times.

4

@tomorrowviral While there is no size limit on a collection, you are bumping into some of the limitations of actually dealing with large collections, especially the movement of data between front end processes (like Import) and back end processes (code that runs on the servers in the cloud). I have found that for any large data operations, writing web modules is the most efficient way to do this. So it means writing Javascript code and developing the looping logic needed to spin through your 1M rows 10K at a time. Another issue is getting access to your CSV file from Wix Code – this is not necessarily straightforward. Bottom line, an efficient solution needs coding expertise to make it work.

5

@davebogan98 Any simple ish ways to load this into a sql server and query from wix?

6

@tomorrowviral Not sure. Have not tried anything like that. Sorry.

7

The article Sync your MySQL Database to Wix finally working (Article) might help.

I’m quite certain that @andreas-kviby wears a cape.

8

A great turn around is to save 500 (or more) records into one array of objects using json format, and then store it in only one row. Then, you just need to call all the items and concant all the objects of each array in one new array

An array of objects in json format could be something like:

[
    {
        "name": "Joe",
        "lastname": "Doe".
        "age": 15,
        "city": "México"
    },
    {
        "name": "Gabriella",
        "lastname": "Rivers".
        "age": 37,
        "city": "Barcelona"
    }
]

if you stored 500 records inside the array of object, you can save 1000 records in only two rows of the collection.

There are tons of websites that can help you to convert cvs / xls / xsls files to json

______________________
inside onReady within frontend

let collection = "info"

let items = await givemyitems(collection)

____________________

Backend or frontend exported functions
    
export async function givememyitems(collection) {

let items = await wixData.query(collection).find().then((r) => { return r.items })

let newArray = items[0].columnwhostorethearray

for (let i = 1; i < items.length; i++) {

   newArray = newArray.concat(items[i].columnwhostorethearray)
   
   }

return items

}