Generate a league ladder

Need help Database
1

HI all,

I’m trying to figure out a way to have Bubble generate a league ladder based off of data in a “games” table.

The games table has team 1, team 2, score 1, score 2, result 1, result 2 (and some other data) so I want to generate a ladder which shows:

  • Team name
  • Total Games
  • Wins
  • Losses
  • Ties
  • Points For
  • Points Against
  • League points

And, above all, apply a logic to sort that list based off of the league points, # of wins, etc in a descending order.

I know how to generate it in a complex SQL query using unions, joins, etc but doing it in Bubble is stressing my brain. Is it a simple process that I’m missing or is there something reasonably complex that needs to be applied to get this achieved?

In terms of table heirarchy, the games table is linked to a seasons table, which is linked to a league. The seasons table has a list of teams in it for that season but I’m not entirely certain whether that’s the best thing to have, yet.

2

Hi! Rodrigo Vega from https://bluflame.xyz here.

for the data structure of the app, according to what I understand of what you say, I thing this is a good Relational Model:

Artboard%20Copy%2013
Artboard%20Copy%20131147×377 33 KB

It’s a little messy around the Game attributes since it need an array of the teams in that game, two separate scores, and another team object as winner. But this will allow you to create your league ladder using a repeating group and displaying the data with simple search and format.

example: give me the count of games in which Winner is this repeating group cell´s team:

46
46743×366 30.1 KB

or: give me the count of games in which this repeating group cell’s team participated and was a tie:

43
43911×221 26 KB

hope it helps :slight_smile:

3

Thanks for that, much appreciated. I’m going to give it a go and see what happens.

1 Like
4

I expanded on this info you gave and have created exactly what I needed out of the data. It’s really opened my eyes as to how NoSQL can be used.

I’ve now got triggers and cascading updates which occur which make data access extremely quick. Thanks again.

5

Hi,
I am working on a project just like yoz, but cant seem to get past creating the league table.
any help is appreciated.

6

Ok, so here’s how I built it all. So you understand why I’ve done these things and can hopefully break it down for your own scenario, here’s what data my scenario is: Multiple leagues of the same sport with multiple seasons, each with multiple teams competing over multiple games.

Fun stuff.

So here’s the layout of tables:

  • Teams
    – Name: text
    – Seasons: List of Seasons
    – Data: Team Data

  • Team Data
    – Team: Team

  • Games
    – Home Team
    – Away Team
    – Home Score
    – Away Score
    – Season: Season
    – Data: Game Data

  • Game Data
    – Game: Game
    – Margin: number
    – Points Scored: number
    – Winner: Team
    – Loser: Team

  • Game Team Data
    – Team: Team
    – Season: Season
    – Points Scored: number
    – Points Conceded: number
    – Points Difference: number

  • Seasons
    – Games: List of Games
    – Teams: List of Teams
    – Data: Season Data

  • Season Data
    – Season: Season
    – Winner: Team
    – Runner Up: Team

  • Season Teams
    –Season: Season
    –Team: Team
    – Games Played: number
    – Points Scored: number
    – Points Conceded: number
    – Points Difference: number
    – Wins: number
    – Losses: number
    – Ties: number
    – League Points: number

The Season Teams table is what I use for the league ladder. I simply query it restricting the season that it’s looking for and it will generate me a table very quickly with what I need. This table gets populated by api workflows which get triggered whenever a new result is created/modified in the games table.

I have even more data higher up the hierarchy than teams, too as I deal with clubs (who have multiple teams in different divisions), state teams, state governing bodies and the national body (which has a series of tournaments run where the state team competes) so that’s why I’ve got to have so much detail in the hierarchy of tables as there’s a lot of denormalisation that has to go on in the DB to ensure that data can be retrieved easily at multiple levels.

I hope this helps you.

If anyone sees this and can see a more efficient way of constructing this, please reply as I am always up for restructuring if it makes the whole thing easier and/or cleaner.

7

Thanks,
let me get busy, will post on my results