Ranking system : pages + API vs custom table + template ?

[Doc]

Hi guys,

I've just discovered Processwire and I'd like to build my new project on it. It seems PW is the tool I was looking for.

It's my first post and I guess many others are coming

A few words about me : I live in France, I used to be a php dev (looong time ago), became a MySQL DBA during a few years and now a little less hands-on (except on that project).

Speaking of the project, it's the complete rewrite of a game related to motorcycle races (you have to guess the podium in different categories). I have players who sign up, record a prediction for the next Grand Prix, get a ranking according to the precision of their prediction (vs real results). They have a profile page, etc.

The frontend is based on data which is updated each Grand Prix (through some pure croned php scripts). The website sends automatically newsletters according to the date (vs the races calendar) and the same goes for the email which warns players their score was updated. It runs for years now. My goal is to reach thousands of players on this new version.  I'm not anxious about speed here, I guess it will be ok, and I'll probably be a future client of ProCache and ProdevTools (yes I saw the coupon code :). That also will be my way to support the work which has been done here.

Actually I'm rewriting the whole stuff (based on pure Php) and I will host more races next year (more categories, more players, more... everything).

I'm interested in PW because I'm looking a modern way to rebuild my website without reinvent the wheel : using all the built-in stuff from PW (routing system, session/authentication, and all what the other modules can offer !).

OK, enough for the project

Although I've been through some different tutorials and read tons of thread on this forum for the last 3 days... I didn't yet make my mind on the solution to choose : "pure" SQL queries or use the API/pages. As I said before, I'm a DBA, I'm ok with queries and I've already wrote most of them.

Actually, I see how to import my tables into the PW's database, so I can directly connect to my old tables (you call that custom tables I think) with the db handler which is passed to every page.

I see how to create a template, I see how to choose the ouput strategy, but I have some difficulties to see how to add content to each page. I don't know if I need all this actually.

I've read this very useful thread : https://processwire.com/talk/topic/18-how-do-i-import-lots-of-data-into-pages/ from @ryan, who answered a very good question from @jbroussia

 

Technically speaking I understand the stuff but perhaps it's overkill for me.

I'm looking for the easiest/quickest solution (I have so many things to code before launch :-/).

According to you, can I survive without importing all my data in PW fields by using this way :

$result = $db->query("SELECT id, name, data FROM some_table"); 
while($row = $result->fetch_array()) print_r($row);

As I said the DB is updated frequently (each Grand Prix) and each player's score is updated in the same time, that goes for the different categories, well a lot of stuff is moving at each Grand Prix.

According to me it should work with template + page using that template but without filling the fields... ?

Moreover I'm afraid of losing some PW functionnalities here :

Accross my readings I've picked up some quotes which I haven't completely understood, perhaps you can help me on that.

- "If you want to present your data at unique URLs, then pages is the way to go.... "

(from https://processwire.com/talk/topic/5325-database-table-versus-pages/)

- "While it's there and ready for you to use, it's always preferable (not to mention easier and safer) to use ProcessWire's API for accessing any of it's data."

- "If you went the pages route, you can also harness the extended power of templates, e.g. using templates to control page access..."

(from https://processwire.com/talk/topic/17-functionsmethods-to-access-the-db/

 

-> I understand it's "easier" to use the API, but why is it "safer" than my own SQL queries ? I still have to sanitize myself any user entries in both case for example no ?

-> Won't I be able to control page access if I'm not using the API ? I didn't dig into control access yet, please forgive me if the question is a nonsense.

-> What about the uniqueness of URL that is mentionned in my first link ?

Well, I think that's enough for a first post and thanks in advance for your answers.

[BitPoet]

Welcome to the ProcessWire forums then! You've certainly found the right tool for such a task.

As to your questions, you're of course right. Under the hood, it all boils down to MySQL statements, so for a reasonably experienced DBA, sanitizing data and optimizing indexes isn't magical. A lot of the terms and descriptions you've encountered are thus meant for frontend developers or those who aren't able to breathe SQL instead of oxygen

The "easier and safer" part means that you should PW's API to access PW's pages and fields, not invent your own SQL to do so when there's a perfectly simple API already in place that sanitizes its selectors and builds the correct JOINs, and if you have means like calling $page->children("rank=".$sanitizer->selectorValue($input->get->rank)) or iterating $page->somepagefield you don't have to worry about someone sneaking a POST or GET value past you that lets them view data not meant for them, nor do you have to worry about accidentally removing valid characters, all the while the code is wonderfully descriptive about what it intends to do.

I guess you could go with storing most of the relevant data for your game in custom tables, and as long as you're the only one managing the system, you'll be fine. If, however, things keep growing, you either need someone equally versed in databases (especially if something goes wrong unexpectedly), or you need a good visual representation of your data in the backend - that's where pages come in. Since pages are just DB entries in the "pages" table and their fields associated rows in a field_FIELDNAME table, there's not much overhead but a lot of visible gain. In case something unpredictable happens (like a winner being disqualified afterwards) you can simply go into the backend, open the page for the race, edit the "results" field and remove the participant in question. That's a simplified example, of course (you'll likely use something more elaborate than a simple page field, e.g. a PageTable or Repeater so you can also store the time and perhaps starting slot and completed rounds), but the gist holds true. Being able to see your data at first glance and being able to use the backend to modify and reorder it is the main advantage of using Pages and Fields.

Think of pages not just as display interfaces in the frontend, but more as links between your data. A page's template is its data model and the page id the key that links the instance data. A pagefield links to another model's instance. You get all that referential stuff with a nice GUI.

There are a few more obvious advantages of pages, like their status flags. Set a page to hidden and it's not listed in $pages->find() calls. Set it to unpublished and it doesn't show up at all.

Of course page access can be limited in any number of ways, but to use PWs built-in access system, you need to have pages. There are pages for displaying data, which you'll have anyway, and there are pages only for storing data to be associated with other pages but not to be displayed on their own. Again, don't just think of a single admin scenario. With multiple editors/admins (like customary for a larger CMS) you can for example limit just who can modify these data pages - this might be a lot fewer people than those who can update the pages including their values.

Avoiding name clashes is always an issue when you import pages. Each page name under the same parent has to be unique - it's used to build the URL to the page, after all, no matter if the page is accessible through the web or not (yet). The logic for page names and the danger of duplicates differ from case to case, so it's always something you'll have to determine yourself how to deal with. Ryan's approach in the linked topic is ProcessWire's default logic, but if a name already exists, you could also let your import script create a completely different name with a microtime-based timestamp with a fixed prefix so you can find those later and deal with them manually, or you could avoid importing them at all and just log a warning, or you could put in your own logic to create a unique name. In case of races if you might e.g. be normally using location-date as the name pattern, but you might have two races on the same day there and append the race number. Whatever fits your need.

I'd suggest you play around a little with field templates / partials. Your site might be a perfect use case for those and explain far better than any prose we write here what the real advantage of using pages and fields for any kind of data can be.

[Doc]

Thanks for the detailed answer @BitPoet !

I'm going to get my hands dirty right away and see how things go. The journey begins