I'm creating a website for a friend's softball league (currently: https://larrystunts.wixsite.com/stuntsoftballleague). I want to create a page that shows the team standings. There are three database's in play: Games, TeamWinLossTie, TeamStandings. The first one records game information, most importantly each record has HomeTeam, HomeScore, AwayTeam, AwayScore. The second is a "calculated" intermediary to allow me to allocate a win, loss or tie for each game for each team (since the team can be the home team or away team this isn't in one column). The third database is also "calculated". It is the source for the page table. In it is the sum of wins, losses and ties by team and a calculated win percent.
With the help of this community (and Wix Reference) I've figured out async/away, arrays (for performance), aggregate, etc. It all works perfectly in Preview but NOTHING runs in Live. I know this because the first step in the process is to truncate the second and third databases and even that one simple command doesn't happen.
I understand Sandbox versus Live data and I've synced the Games database (that's the only one that needs to start with data - remember the first couple lines should truncate the other databases anyway.) I've set all the databases to have custom permissions with ANYONE having all access rights. (Probably not necessary for the Games table since that is only read.) And I've set all three database connectors to Read & Write mode (again probably not necessary, at least for the Games databases.)
As I said, everything runs perfectly in Preview but not in the Live site. Help would be appreciated!
Thanks,
Bruce
Here's the Pagecode (#dataset1 = TeamWinLossTie, #dataset2 = Games, #dataset3 = TeamStandings):
import wixData from 'wix-data';
import wixWindow from 'wix-window';
$w.onReady(async function ()
{
let trunTeamWinLossTie = await wixData.truncate("TeamWinLossTie");
let trunTeamStandings = await wixData.truncate("TeamStandings");
var allScores=[];
//Begin populate TeamWinLossTie const totalGames = $w("#dataset2").getTotalCount(); let result = await $w("#dataset2").getItems(0, totalGames); let allItems = result.items; for (let i= 0 ; i < result.totalCount ; i++) { let hometeam=allItems[i].homeTeam; let homescore=allItems[i].homeTeamScore; let awayteam=allItems[i].awayTeam; let awayscore=allItems[i].awayTeamScore; if(homescore == null) { } else if(homescore>awayscore) { allScores[i*2]={team: hometeam, win:1, loss:0, tie:0} allScores[i*2+1]={team: awayteam, win:0, loss:1, tie:0} } else if(homescore<awayscore) { allScores[i*2]={team: hometeam, win:0, loss:1, tie:0} allScores[i*2+1]={team: awayteam, win:1, loss:0, tie:0} } else if(homescore===awayscore) { allScores[i*2]={team: hometeam, win:0, loss:0, tie:1} allScores[i*2+1]={team: awayteam, win:0, loss:0, tie:1} } } await wixData.bulkInsert("TeamWinLossTie",allScores); //End populateTeamWinLossTie //Begin populateTeamStandings var allStandings=[]; let aggResults = await wixData.aggregate("TeamWinLossTie") .group("team") .sum("win") .sum("loss") .sum("tie") .run(); let sumItems = aggResults.items; for (let i= 0 ; i < aggResults.items.length ; i++) { var tN = sumItems[0,i].team; var w = sumItems[0,i].winSum; var l = sumItems[0,i].lossSum; var t = sumItems[0,i].tieSum; var tpoints = t/2; var wP = (w+tpoints)/(w+l+t); allStandings[i]= {teamName: tN, wins: w, losses: l, ties: t, winPercent: wP}; } await wixData.bulkInsert("TeamStandings",allStandings); //End populateTeamStandings************************* // Added just in case the page paints before the data in #dataset3 (TeamStandings) is populated
$w("#dataset3").refresh();
$w("#table3").refresh();
//End *************************
});
Like always, very good explanation Yisrael. Have learned new things.
THX.
The best way to display the standings would be to do the calculations - that you are already doing anyhow - and then display them directly in a table without using a database collection. This would be faster, wouldn't matter who views the standings, wouldn't matter how many visitors are viewing the site, and using the collection to set the table contents is just an unnecessary step.
I suspect that it has to do with permissions. As stated in the truncate() API:
truncate() runs when at least one of the following is true:
The current user is the site owner.
The request is performed in backend code with a suppressAuth options value of true.
This means that truncate will not work for any anonymous site visitor since they are not the site owner. This even includes you if you are not logged in as the site owner.
The way you can get around this is to move your database code into the backend, and use the suppressAuth option.
However, I have to admit that I don't understand why you are clearing and populating a standings collection each time the standings are viewed. If more than one user is viewing this at one time it will cause a conflict - who clears? who populates the collection?
I would suggest that all you need to do is to calculate the standings as you already are doing, and then use the calculated results to populate the table. No reason to clear, calculate, and populate a special standings collection for each view of the team standings.
Hello together,
did you already have tried to Log-In as ADMIN with full rights and test it ?
If not yet, then try this first.
And i recognized that you do not use the onReady-command for datasets.
$w.onReady( () => { $w("#myDataset").onReady( () => { }); });
Have you published your site? Your code will only run in preview mode unless you publish your site & reload your live site to see the changes.