[SQL] Missing users report systeem + vervanger voor missende

[Wiethoofd]

Voor een pickup website ben ik atm aan het nadenken over een report systeem voor de missende users op de matchservers om deze en de bijbehorende tabel(len) zo efficiënt mogelijk op te zetten.

Match users tabel:
> MatchID, UserID, Class, TeamColor
> Tot 18 unique UserID's per MatchID

We willen bijhouden wie welke user gereport heeft per match:
Theoretische tabel die reports bijhoudt (voor de buitenstaander even logische kolomnamen):
> UserID, MatchID, ReportedUserID
> Zodra het knopje 'report for sub' is ingedrukt en het report succesvol was kan de user niet nogmaals een report voor deze user indienen (bij reporten zit er een check die zorgt dat de combinatie van deze drie waarden niet nogmaals voor kan komen in de tabel), knopje wordt weggehaald.

Mocht een ReportedUserID voor dezelfde MatchID 3 maal of meer voorkomen, dan wordt deze opgepikt door de 'subs check' (3 reports => substitute nodig op de server) (losse query op andere file)

Klikt iemand op het theoretische knopje 'Substitute' om de server te joinen, dan zou ik dat opslaan in een extra tabel:
> SubID (AI), MatchID, ReportedUserID, SubstituteUserID, (TimesReported?)
>> Eventueel zou bij een eerste report voor de tabel die reports bijhoudt in deze tabel die bijhoudt wie subbed een countertje verhoogd kunnen worden, zodat er geen count(UserID) voor de combo MatchID+ReportedUserID nodig is maar een TimesReported > x op deze tabel.

Wat ik wil voor de matchpage (geselecteerd op MatchID): het tonen van alle users uit de match tabel, maar in dezelfde query ook meteen (voor welke user er) door wie gesubbed is.

Het door wie er voor wie gesubbed is is met een extra sub select in de query voor alle users uit de matchtabel voor die specifieke match prima te doen, maar wil ik echter de users uit de subs table voor dezelfde map er bij loopen, dan werkt dat weer niet. Zou een select op de users tabel met de namen etc. (die nu gejoined wordt bij het loopen van de matchtabel) in plaats van op de matchtabel met als filter de MatchID in de subs en match het aantal resultaten terug te brengen naar het gewenste aantal?

Ik heb hier (te)veel over nagedacht, wil het nu eens opzetten en kijken of wat ik wil überhaupt mogelijk is. Mocht er voorbeelden nodig zijn met nep users en een fake report/sub dan zal ik kijken of ik wat uit kan typen.

_{+kudos voor wie van deze gedachtenkronkels iets snapt}

[Wiethoofd]

De tabellen voor de voorgestelde opzet toch aangepast en de players tabel van een unique ID voorzien waarop de missende gebruikers worden gereport.

Players tabel (`match_players`) ID (AI) matchID playerID team class subID 10 4 18 red soldier NULL 11 4 23 red scout NULL 12 4 15 blue soldier NULL 13 5 11 blu soldier NULL	Match info tabel (`match_info`) ID (AI) status serverID date 1 1 1 1367414348 2 0 2 1367415324 3 1 3 1367445676 4 0 1 1367515647
Missende users (`missing`) MissingID ReporterID 12 9 12 65 11 9 12 19

In dit geval heeft een ander script met een count(ReporterID) met een group by MissingID ID 12 uit de Players tabel opgepikt (blue soldier) omdat de status van die match nog 0 is.

Een user kan vervolgens voor player '12' in de players-tabel een substitute zijn. Hiervoor moet de ID van de eigen gebruiker niet in de actieve match voorkomen (als playerID of subID), de match status 0 zijn en subID leeg.

SQL:

SELECT *
 FROM match_players
 JOIN match_info mi ON match_players.matchID = mi.ID
 WHERE matchID = (SELECT matchid FROM match_players WHERE ID = '12')
 AND status = 0

Deze SQL resulteert in alle spelers voor 'matchID' 4. Doe ik echter in de subquery een WHERE playerID != '15', dan krijg ik (in dit geval) alsnog 2 resultaten terug (match_players.ID 10 en 11).

De playerID van de gebruiker zelf en de ID om te vervangen (en dus de subID voor te zijn) zijn in dit geval bekend; is er een query mogelijk waarin dit in 1x te selecteren is of moet er een view van de gebruikers per match gemaakt worden, of alle gebruikers voor die specifieke match opgehaald worden en in de while loop gecheckt worden of de gebruiker in de match speelt?

[pedorus]

offtopic:
Wellicht kun je het proberen met wat meer introductie over welk probleem je nu wil oplossen en kortere vragen die grammaticaal wel kloppen..

[Wiethoofd]

K, nog een poging dan

• Users submiten `match_players`.ID als missend. Deze wordt als `missing`.MissingID met hun userID (als reporterID) opgeslagen.
• Een ajax call checkt vervolgens count(ReporterID) > 2 GROUP BY MissingID
> • Resultaat van bovenstaande toont info uit match_players (team/class) met een 'join as sub' knop
> • Join as sub is een POST van de MissingID (`match_players`.ID) naar een derde pagina
>> • De query hier moet checken of de ID van de user niet voorkomt als playerID/subID in de match waar de sub voor is.
>> • Mocht dit het geval zijn (user zit niet in de match) dan moet deze het matchID opleveren waar de gesubmitte MissingID nog geen subID hebben (NULL)
>> • Is de match_info.status echter 1, dan is de match afgelopen en hoeft de 'join as sub' niet meer geregistreerd te worden.

Kan dit zelfs al in een UPDATE gedaan worden? Die dus een 'affected_rows 0' heeft als de match afgelopen is, of er al een sub gejoined is of de user voorkomt in de match waar de sub voor nodig is.

[Wiethoofd]

Na veel koppen koffie en slapeloze nachten tot de volgende UPDATE query gekomen.

Deze werkt en update de rij waar de speler voor mist alleen als de joinende user nog niet in de match_players of subID voor die specifieke match voorkomt en de match status = 0.

SQL:

/* Als sub joinende user: 8 */
/* Joined voor match_players.ID = 12 */

UPDATE match_players AS mp
    INNER JOIN match_info AS mi ON mi.ID = mp.matchID
    SET subID = '8'
    WHERE mp.ID = '12'
        AND status = 0
        AND subID IS NULL
        AND NOT EXISTS (SELECT * FROM (SELECT matchID
                        FROM match_players
                        WHERE matchID = (SELECT matchID
                                    FROM match_players 
                                    WHERE ID = '12')
                                        AND (playerID = '8'
                                            OR subID = '8')) AS temp)

[Russel88]

Als je hulp wilt hebben zou ik nog een poging wagen om je probleem wat duidelijker te verwoorden.

Ik weet niet hoe vaak je de query gaat runnen. Maar een exist in een exist is niet echt optimaal.