How do I assign the first 50 items to a repeater?

Ask the community
, ,
1

Hi,
I have an array of objects allShuffledItems (which is the result of a query to a collection) and I’m looking to return the first fifty items from it and assign them to a repeater. The repeater below works if I assign “allShuffledItems” to it, but would like to assign the first fifty items of allShuffledItems to the repeater. Scratching my head a bit on this…

export function backend_getRepeaterData ( collection , category , country ) {
//
return wixData . query ( collection ). limit ( 1000 )
. eq ( “category” , category )
. eq ( “country” , country )
. find ()

}

/// snip
// snip
// Front end

        export function  shuffle ( array ) {

for ( let i = array . length - 1 ; i > 0 ; i --) {
const j = Math . floor ( Math . random () * ( i + 1 ));
[ array [ i ], array [ j ]] = [ array [ j ], array [ i ]];
}
return array ;
}

// inside the function that picks the first 50…

**let**  allItems  =  **await**  		backend_getRepeaterData ( "Store" , $w ( "#categoryInput" ). value , $w ( "#countryInput" ). value ) 

    // we could have anywhere up to 1000 results back due to a LIMIT in the backend query, so return maxResults 

       **let**  allShuffledItems  =  shuffle ( allItems . items )  <<<<<< this returns an array of objects 

    //let firstFiftyItems = allShuffledItems 
    //let firstFiftyItems = [] 
    **var**  firstFiftyItems  =  allItems [ 0 ] 
  
    **for**  ( **var**  i = 0 ; i <  maxResults ; i ++) { 
        firstFiftyItems [ i ]=  allShuffledItems [ i ] 
    //  firstFiftyItems[i] = allShuffledItems[i] 
    } 

$w ( '#repeater1' ). data  =   firstFiftyItems

Thank you!

2

Solved my own problem.
FYI - on the return from the shuffle just return array.splice(0…whatever).

3

Well done!

4

Hey Kyle, good suggestion on the array.splice() method. I have got to remember that one. I was looking over your code, and was wondering if you return all 1000 items to the frontend, shuffle them around randomly, and then select the 50 items from the shuffled items. I’ve had better luck doing all that in the backend and then only returning the 50 items. I get less data coming back from the backend and faster response times.

5

Hi there, here is the rub… and I have not gotten to think of it yet. Yes, you are 100% right about that. The problem is I “need” (grain of salt) to return all 1000 records to be fair and randomize accordingly. The next step is yes, reducing those to selecting only 50 items in the back end. This is my first draft of the code to test POC. I would have to randomly select records from the collection with the likes of a skip, etc or bring back all record IDs (a lot smaller set) and then pick random IDs, and get. It seems rather slow in my head. I would have to do 50 random gets and build an array from that. Collections and data sets by default are in descending order of creation date I think. So, I can’t sequentially fetch the first 50 each time or they would be basically the same each time.

Anyways, just had another thought, but it is one I think you have given me :slight_smile: What I will do next is fetch the 1,000 records (yes, that is a problem for another day :-)) in the backend and in the same function perform the shuffle, rather than passing 1000 back to the front end, then passing 1,000 to the back end and shuffling and returning 50, I might fetch 1000 in the back end and not return until the shuffle is done and return 50. Thank you very much for that seed of wisdom!

6

I was thinking about your solution and I think there is a much faster way to approach this.

One of the issues with your solution is that you always select the first 1000 records. The problem is you will be sampling only the first 1000 every time. If there are more records added, these will never be included in the pool since they fall outside of the 1000 range.

May I suggest an alternate solution.
To your record set add a new field, an integer number, called something like RowID. Whenever you write a new record out to your table, save the integer RowID along with your data.

This new RowID, needs a new incrementing number every time you save new data.

To manage this new RowID number, store it into a new collection (eg call it autoNumbers). Everytime you need to save a new row into your table, read the number from autoNumbers, increment it and resave it back to autoNumbers. Take this number and then use it for your RowID.

Now that you have each row with a unique number, that is always incrementing, you can fetch the row by its RowID. It might fetch faster, if you set the RowID as your ‘Primary Field’ and sort it ascending.

Rather than read in 1000 rows of data, get your max # by reading it from autoNumber -1. Then generate your random # from 1 to the maximum number.
Then read in that record, retrieving by RowID. Repeat this process 50 times, and you have only read in 50 records that have been randomly generated accross all your rows.

If you delete rows in your dataset, you can compensate for this by checking when you retireve the row, to ensure it exists. If it doesn’t, regenerate the random # and try again.

What do you think? Does that make any sense?

7

Thanks so much. I’ll digest this over the next couple of days as they have just extended lockdown. Very much appreciated. As these 1,000 were going to be in many categories, i.e. CategoryA has 1000, B has 1000, etc, I was going to use the limitation and spin it as an exclusive selling feature :slight_smile: So, reading the first 1000 would always be fine.

Problem is that WIX is a black box and with standard databases, usually a sequential run to return more than say 5% of a recordset is a lot quicker than lots of lookups. As this app will not be write-intensive I don’t see it being an issue with part of your suggestion which is finding MAX(ROWID) and then adding +1 to it. The problem here could be a clash (rare) where two customers read the same ROWID and then increment.

Your solution makes sense, but I think returning all rows where CATEGORY=“CHEESE” for example, may allow for a faster fetch but again we have 1000 records coming back. Going back to classic databases 50 fetches are 50 fetches, index lookups and block lookups unless the data is in the index. Sequential is often parallelized. I wish WIX published a little more of their inner workings as it would be helpful and we wouldn’t have to guess and test. Some companies don’t like showing the secret sauce as often the back end is hung together with string and tin cans and we will find out that 3,000 of us are sharing a single Postgres DB.

This is the problem dealing with a black box. Unknown the logic behind the scenes. I tried having my backend functions call each other, i.e. 1000 feeds into the shuffle and returns 50, but it wouldn’t work.

The other thought I had (have not gotten the maths in my head yet) is using SKIP. So, if the number of records is < 50 then SKIP(0), if < 100 then skip(random number between 0 & 5). I don’t have a mathematics brain.

Another thought was just to return 1000 IDs from the records which exist. Returning an ID is smaller than returning a row with an image. Then just randomizing the selection from the IDs and do 50 fetches.

Thank you very much for your solution as it is certainly one I’ll ponder on for a while and if nothing it has fuelled other avenues of thought.

Thanks,
Kyle

8

Hey Kyle, I wonder what City you live in. I,m close to Toronto, and I think we’ve stopped further opening of the economy as well, as number are starting to go up again.
I fully understand your response regarding the crazy blackbox data system that Wix provides.
I’m a DBA myself and have may years experience with DB2, Oracle and the last 15 years with Microsoft SQL Svr. It is absolutely frustrating that their can’t be simple database variable types such as AutoID, that autoincrements upon record insertion. I could right much more efficient queries with native SQL.
I have seen several people cascade multiple .eq(). I don’t know the limit, or if it even works. My thoughts initially were that it use the last one it hits and uses it. Internally, maybe it’s ANDing the multiple .eq(). A possible answer is to generate the 50 random #s, store them into an array, then build your query with the multiple .eq()s. I personally have not tried this, although I have cheated and combined .eq() with commands such as .like(), .ne(), etc. in order to get multiple equals to work. Using this approach, would allow you to retrieve the set as one read, reducing the overhead of issuing each query individually.

Good luck!!
…Paul

9

Hey Kyle, I was reading through the Wix-data api and it does have the ability to retreive a bunch of records by passing in an array. It will return all records that match. The hasSome method, will find any records that match the array.

wixData.query("myCollection")
6  .hasSome("colors", ["red", "yellow", "blue"])
7  .find()
8  .then( (results) => {
10

@pekrzyz just north of Wellington New Zealand. Yeah many years of database too with the suite including now snowflake And just recently DB2 purescale and salesforce. It is that lack of behind the scenes knowledge that is frustrating. Yes a simple autonumber and field defaults would be good. a little more indexing control And maybe something as simple as date range partitioning would be good. enjoy lockdown!

11

Thanks for that. It has merit if passing a bunch of IDs. Gonna look into it cheers!

Thought of another way. The problem is the shuffle is slow. I tried it and it crashes after 25 records. Your idea about a table gave me this idea. It is not perfect and there could be a clash of the same number but that could be taken account of in an order by. Here it is…
Lets say the collection is name, age, row. Row is an integer. So, when you create the record it first gets a random number between 1 and the number of records in the collection. Now, when you search, if you ORDER by ROW asc or dec then the rows returned will be somewhat random. These rows (limit 50) say are returned to the repeater. Job’s a good one at this point and the repeater now is updated. Next step, just fire off an async lightweight function that now goes through every record and looks at the count, then assigns a random number to each one. It could even add the random number for newly created items there and then.

It will a) be fast as it is one piece of query that just does an order by with a LIMIT and no shuffling. You can limit the rows that come back easily.

12

Sort of working ---- here’s the random query to populate

export async function backend_getRepeaterData ( collection , category , country , maxResults ) {
//

    **return**  wixData . query ( collection ). limit ( maxResults ). skip ( 0 ) 
    . ascending ( "randomRowNum" ) 
    . eq ( "category" , category ) 
    . eq ( "country" , country ) 
    . find ()

}

Here’s the reset code

export function backend_resetRowNumbers ( collection , category , country ) {

    console . log ( "Resetting Random Numbers for records" ) 
    wixData . query ( collection ). limit ( 1000 ) 
    . eq ( "category" , category ) 
    . eq ( "country" , country ) 
    . find (). then (( results ) => { 
        results . items . forEach (( item ) => { 
            **if**  (! item . randomRowNum ) { 
                item . randomRowNum  =  Math . floor ( Math . random ()* 10000 ) 
            }  
            **else** 
            { 
            item . randomRowNum  =  Math . floor ( Math . random ()* 10000 ) 
                  } 
             **return**  wixData . update ( collection , item ) 
        }) 
    })

}

13

Am I missing something in your thought process. I was trying to follow along with your example. When inserting and generating a random #.

Here goes:
Insert first record (blank database), random # between 1 and 0 (max is zero)

  • 50/50 chance of zero or 1
    Insert second record (db now has one row), random # between 1 and 1 (max is 1) - 100% chance of getting 1
    Insert third record (db now has 2 rows), random # between 1 and 2 (max is 2)
  • 50% chance of 1 or 2
    Insert 4th record (db now has 3 rows), random # between 1 and 3 (max is 3)
  • 2 in 3 chances of getting 1 or 2
    Insert 5th record (db now has 4 rows), random # between 1 and 4 (max is 4)

Ah, I see you provided a solution. Here you show that you actually are inserting the RandomID in an existing table with all the rows already in it. Ok, forgot my confusion based on your original description.

I looked at your solution and it looks quite good!!! The backend_getRepeaterData function looks bang on.

There was an if block in your resetRowNumbers, that confused me a bit.
You generate a random # if it doesn’t exist in the row, and if it exists, (else), you still generate a random #. If it already exists. Why generate the random # again, or bother even updating the row, if it already exists.
My thought is that you’ll probably only run the resetRowNumbers function once, to setup your random ids andway.

Great job, that was quick to come up with the solution.

14

Basically the bigger the number you are using the less chance of a clash. even if there is for me ther is not important. so for each insert generate a random number between say 1 and 100000. this goes into a field called randomrownum. When a query is made it may take a while to warm up but order by this rownum. The first time might be a little off but once you have done your return and ordered by rownum then you update all rows with a new random number.
got a proto type of this working. Sorry I’m not the best at explaining it.
I changed my thinking sorry about the row count first and just use a large number as less Chance of clash. Even if there are two the same for me they just fall next to each other in a sort. It may not work for everyone but this method has now Removed the need for random look up and any slow shuffling of arrays
I had

15

Also as for the initial Load of the table they are basically random to the searcher so that is a win as I dont have to populate immediately. the asynchronous function then sweeps the table and generates the random numbers.

16

Absolutly right. I thought the exact thing, when I saw your max value for your random # was at least 10 times higher, than than the actual rows. That should absolutly minimize chances of duplicate numbers.

17

@pekrzyz indeed thank you for your jazz session that really helped