Managing standings in Processwire

[da²]

Hello,

I'm planning to use Processwire for a sim-racing project where we will create events like championships, races and hotlaps.

I'm wondering how to manage results and standings for each event. You can see here an example of a hotlap standings : https://online.racingfr.net/hotlaps/assetto.corsa/roc.2022.bmw.e30.dtm.hotlap/okayama/hotlap-2192.html

Standings need to be editable, for example to add penalty points to a player result.

Usually, without using a CMS, I would create a table "hotlaps" with columns [hotlapID, username, trackID, carID, globalTime, sector1Time, sector2Time, ..., optimalTime] and all of our hotlap results would go in this table. Then I can make SQL queries to find times for a particular hotlapID, track, car...

In Processwire I don't know what is the best choice.

We will have a single PW page for each hotlap, race or championship.

I first thought to create a child page per event result with a template "hotlap_result" corresponding to a single row (a single player result), but we will end with more than 200 000 result pages after years, this is not optimal from an ergonomics point of view in PW admin. And we also have standings for races and championships, so I need a good overall solution.

I see that ProFields Table would make the trick, but will it create a separate database table for each hotlap page? That could be an issue cause actually we have 2000 hotlaps, to add to future hotlaps in next years.

I hope I'm clear, thank you for your advice. ?

[bernhard]

20 hours ago, da² said:

Usually, without using a CMS, I would create a table "hotlaps" with columns [hotlapID, username, trackID, carID, globalTime, sector1Time, sector2Time, ..., optimalTime] and all of our hotlap results would go in this table. Then I can make SQL queries to find times for a particular hotlapID, track, car...

In Processwire I don't know what is the best choice.

You can do the same in PW. You just don't get the page editor and have to build a GUI for editing that table on your own.

20 hours ago, da² said:

this is not optimal from an ergonomics point of view in PW admin

Why not?

Maybe you are just missing a tabular view like shown here? https://processwire.com/talk/topic/26663-need-help-making-sse-work/?do=findComment&comment=220918

20 hours ago, da² said:

I hope I'm clear

For me: Not really ? Those things are always really hard to understand if you are not into the topic. So for me it would help a lot to get more details or specific examples. Or to get screenshots of how it is done at the moment. But I'm quite sure there's no easy peasy answer to your questions as it is a complex case ? 

[Jan Romero]

22 hours ago, da² said:

I see that ProFields Table would make the trick, but will it create a separate database table for each hotlap page?

No, it will create a single database table. If you use the table field on multiple pages, all records will go into the same database table, just with a foreign key connecting each record to its page. So perhaps you will make a Track template and add the Hotlaps field (Profields Table) to it. That way you don’t need a custom trackID column because that will just be the page’s ID. Because it’s a single database table you will still be able to figure out, for example, the car with the most hotlaps across all tracks, by just doing very simple custom SQL:

$sql = <<<SQL
    select car, count(id) as hotlaps
    from field_hotlaps
    group by car
    order by count(id) desc
SQL

$query = wire('database')->prepare($sql);
$query->execute();
$queryResults = $query->fetchAll(\PDO::FETCH_KEY_PAIR);

A car will probably be a page, so you would use a Page Reference column in your table. The query above thus gives you a bunch of page ids you can convert to page objects like this:

wire('pages')->getByIds(array_keys($queryResults));

[da²]

Thank you for your answers @bernhard and @Jan Romero .

The ProFields Table looks like a good solution. ? So I can use PageReference fields for users, cars and tracks, and display the standings in each hotlap page (same for races and other events). Also I prefer to use PW solutions instead of developing my own modules.

I already used PW for some projects, but never at this scale, I'm quite excited to start development but I'm also a bit blind about how to do this or that. ?

[da²]

Where is the "mark as solution" button that the email I just received is talking about? ?

Quote

 

Did any of these replies answer your question? There may be more replies than those shown here.

Help others by going to the topic and use the 'Mark as Solution’ button on the post with the best answer