[SQL] Kan dit in één query?

[posttoast]

Ik ben nu al meer dan een uur bezig om een query van (voor mijn doen) monsterlijke proporties in elkaar te krijgen en zie door de bomen het bos niet meer. Ik begin me inmiddels af te vragen of wat ik wil überhaupt mogelijk is. Maar voor ik de handdoek definitief in de ring gooi, check ik het nog even met de helden op GoT. Kleine noot: ik ben geen zeer ervaren backenddeveloper. Als jullie hieronder structurele denkfouten zien, dan word ik daar graag op gewezen

Scenario in het kort: er is een spel dat bestaat uit 3 rondes. Nadat je een ronde gespeeld hebt kun je iemand uitdagen, die persoon moet dan proberen een hogere score te halen. Andersom kun jij ook uitgedaagd worden (zolang je nog rondes over hebt). Ik heb de volgende tabellen:

• Player: de spelerinformatie
• Score: de gegevens van een gespeelde ronde
• Challenge: een koppeltabel met verwijzingen naar 2 scores (uitdager en uitgedaagde) en 2 spelers (uitdager en uitgedaagde)

Nu wil ik een query hebben waarin ik een overzicht krijg van alle rondes, met daarbij per ronde de scores (van de speler en indien beschikbaar ook van de opponent) en wat gegevens van de opponent.

Nu heb ik de volgende query in elkaar gedraaid, in dit geval voor de speler met id 1:

SQL:

SELECT
  s.id AS id,
  s.round AS round,
  s.score AS score_player,
  x.score AS score_opponent,
  c.accepted,
  o.name AS opponent_name
FROM
  score s
  LEFT JOIN
  challenge c
  ON
  (c.challenger_score_id = s.id)
  LEFT JOIN player o
  ON
  (c.challengee_id = o.id)
  LEFT JOIN score x
  ON
  (c.challengee_score_id = x.id)
WHERE
  s.player_id = 1
ORDER BY
  round ASC

Dit geeft mij netjes alle data terug van de rondes waarin speler 1 de uitdager is. Maar: hoe kom ik nu aan de rondes waarin speler 1 juist de uitgedaagde is? Het enige wat ik me kan bedenken is nóg een join op de challenge- en player-tabel, maar dan krijg ik natuurlijk geen aparte rows terug.

Kortom: ik zit met mijn handen in het haar. Wie geeft mij een zetje in de goede richting? Of is wat ik wil sowieso niet mogelijk in deze opzet?

[NMe]

Waarom zou je met nóg een join geen aparte rijen terug krijgen? Gewoon nogmaals op beide tabellen joinen met een andere alias en een on/where-clause die je de data teruggeeft die je wil en klaar is posttoast hoor.

[posttoast]

Hmm, NMe: jouw antwoord doet me realiseren dat ik wellicht een denkfout heb gemaakt. Als player 1 een andere speler uitdaagt, dan is er voor player 1 een record in de score-tabel. Maar: als iemand anders juist player 1 heeft uitgedaagd, dan is dat niet zo (althans, niet voordat er door speler 1 ook een score is neergezet).

Dus: er valt helemaal niets te joinen, want die hele score-record bestaat op dat moment nog niet.

Volg je wat ik zeg (vind ik lastig inschatten omdat ik inmiddels zo lang aan het worstelen ben dat ik er tot mijn oksels in zit )?

Mocht je naar aanleiding van wat ik nu zeg het idee hebben dat ik het mezelf alleen maar moeilijker maak, dan hoor ik dat ook graag

[Voutloos]

posttoast schreef op zaterdag 26 november 2011 @ 17:31:
Dus: er valt helemaal niets te joinen, want die hele score-record bestaat op dat moment nog niet.

Daar is LEFT JOIN voor uitgevonden, en zo te zien ben je daar al bekend mee, dus hop hop hop doe nou gewoon wat NMe zegt.

[jvd-nl]

Bedoel je zoiets?

SQL:

SELECT
  sc.id AS id, 
  sc.round AS round, 
  sc.score AS score_player, 
  so.score AS score_opponent, 
  c.accepted, 
  po.name AS opponent_name
FROM
  challenge c
  LEFT JOIN
    score sc
    ON
    sc.id = c.challenger_score_id
  LEFT JOIN
    score so
    ON
    so.id = c.challangee_score_id
  LEFT JOIN
    player pc
    ON
    pc.id = c.challenger_id     
  LEFT JOIN
    player po
    ON
    po.id = c.challengee_id
WHERE
  pc.player_id = 1
  OR
  po.player_id = 1
ORDER BY
  round ASC

[posttoast]

Voutloos schreef op zaterdag 26 november 2011 @ 17:44:
[...]
Daar is LEFT JOIN voor uitgevonden, en zo te zien ben je daar al bekend mee, dus hop hop hop doe nou gewoon wat NMe zegt.

Ik weet dat LEFT JOIN daarvoor is bedoeld (zo goed heb ik nog wel opgelet bij de DB-modules op mijn opleiding ), maar: volgens mij gaat die vlieger hier niet op omdat ik met challenges twee kanten op te maken heb (ik word uitgedaagd en ik daag uit). Ik hoop dat ik het een beetje helder verwoord.

jn0077 schreef op zaterdag 26 november 2011 @ 17:46:
Bedoel je zoiets?

SQL:

SELECT
  sc.id AS id, 
  sc.round AS round, 
  sc.score AS score_player, 
  so.score AS score_opponent, 
  c.accepted, 
  po.name AS opponent_name
FROM
  challenge c
  LEFT JOIN
    score sc
    ON
    sc.id = c.challenger_score_id
  LEFT JOIN
    score so
    ON
    so.id = c.challangee_score_id
  LEFT JOIN
    player pc
    ON
    pc.id = c.challenger_id     
  LEFT JOIN
    player po
    ON
    po.id = c.challengee_id
WHERE
  pc.player_id = 1
  OR
  po.player_id = 1
ORDER BY
  round ASC

Ja, zoiets, alleen heb ik nu nog steeds hetzelfde probleem maar dan omgedraaid. Niet elke challenge heeft een score en niet elke score heeft een challenge, maar ik wil ze wel allemaal laten zien. De enige manier om dat voor elkaar te krijgen is door te zorgen dat er bij iedere challenge ook een score-record voor de opponent wordt aangemaakt. Dus daar ben ik nu dan maar even mee aan het vogelen

[ Voor 20% gewijzigd door posttoast op 26-11-2011 18:51 ]

[Keeper]

Puur gebaseerd op je laatste opmerking: kijk dan naar een Full Outer Join.

[posttoast]

Keeper schreef op zaterdag 26 november 2011 @ 19:03:
Puur gebaseerd op je laatste opmerking: kijk dan naar een Full Outer Join.

Dat werkt standaard niet in MySQL zie ik, maar ik geloof dat er wel workarounds zijn. Ga ik even mee spelen

[Voutloos]

OK, stop maar even met in SQL denken, ik denk dat we meer bereiken als je eerst wat voorbeeld data laat zien, en het resultaat dat je voor ogen hebt.

[ Voor 21% gewijzigd door Voutloos op 26-11-2011 19:31 ]

[posttoast]

Ja meneer

Ik zal het nog eens proberen stap voor stap uit te leggen:

• Het spel bestaat uit drie rondes
• Nadat je een ronde gespeeld hebt kun je een andere speler uitdagen
• Zo lang jij nog rondes "over" hebt kunnen andere spelers jou ook uitdagen
• Een "challenge" is niet persé voor beide spelers aan dezelfde ronde gekoppeld. Dus: ik heb ronde 1 gespeeld en daag jou uit, dan kan dat heel goed voor jou ronde 3 zijn.

Qua tabellen sla ik dit op (even vereenvoudigd, ik toon hier alleen wat relevant is voor de query die ik probeer te bouwen):

Player

id	ID van de player
name	Naam van de player

Score

id	ID van de score
player_id	ID van de player
round	Rondenummer
score	Aantal punten

Challenge

id	ID van de challenge
challenger_id	ID van de uitdager
challengee_id	ID van de uitgedaagde
challenger_score_id	ID van de score van de uitdager (is altijd gevuld)
challengee_score_id	ID van de score van de uitgedaagde (is leeg totdat uitgedaagde gespeeld heeft)*

En volgens mij zit hem in dat allerlaatste (die challengee_score_id) het probleem. De query die ik nu probeer te bouwen moet alle rondes tonen die ík voor mij van toepassing zijn. Ook rondes waarbij ik wel ben uitgedaagd maar nog niet zelf heb gespeeld. Alleen: die rondes bestaan nog helemaal niet in de scoretabel. Als ik de scoretabel als uitgangspunt neem voor dit overzicht (wat volgens mij moet), dan gaat dat dus niet werken. Ik denk dus dat ik, zodra iemand een challenge aanmaakt, ik direct een record in de scoretabel moet aanmaken voor de challengee.

Is het zo wat duidelijker uitgelegd?

[Voutloos]

Dat is nog steeds geen voorbeeld data, en voorbeeld resultaat.

Ik zou trouwens bij de player tabel beginnen voor de speler waar je alle stats van wilt hebben. Vervolgens join je de hele reut voor challenges die je gedaan hebt, en de hele reut nogmaals voor challenges die je geaccepteerd hebt. Om te beginnen kan je dat het beste nog even los van elkaar doen, zodat je 2x maar de helft van je probleem hoeft op te lossen. Vervolgens ff bedenken of je het 1 grote query maakt, of dat die 2 delen misschien als UNION makkelijker te verwerken zijn.

In ieder geval: Werk het dus op een minder technische manier uit, of begin met kleine stukjes van het probleem.

[ Voor 21% gewijzigd door Voutloos op 26-11-2011 20:28 ]

[KopjeThee]

posttoast schreef op zaterdag 26 november 2011 @ 19:59:
De query die ik nu probeer te bouwen moet alle rondes tonen die ík voor mij van toepassing zijn. Ook rondes waarbij ik wel ben uitgedaagd maar nog niet zelf heb gespeeld. Alleen: die rondes bestaan nog helemaal niet in de scoretabel.

Ik weet niet of ik het begrijp, een voorbeeld zou inderdaad helpen. Maar om een gokje te wagen. Je hebt 2 relevante momenten in je spel:
1) Je daagt iemand uit of iemand anders daagt jou uit. Op dit moment wil je iets in die challenge tabel wegschrijven.
2) Je heb een wedstrijd uitgevoerd. Op dit moment wil je iets in de score tabel wegschrijven.

In de huidige situatie heb je een probleem als je wel uitgedaagd bent, maar de wedstrijd nog niet gespeeld is. Je hebt dan nog niets in de score tabel, dus je weet niet bij welke ronde een wedstrijd hoort voor jou.

Dit kan je volgens mij op verschillende manieren oplossen, maar wat je zelf voorstelt kan volgens mij:
1) Als je iemand uitdaagt of een ander daagt jou uit, dan schrijf je direct 2 regels weg in de score tabel, met een ronde, maar zonder score uiteraard.
2) Als de challenge gespeeld is, dan update je de beide regels in de score tabel met de uitslag.

Maar misschien is het ook niet gek om die ronde en score voor beide partijen ook maar in die challenge tabel op te nemen.

[ Voor 47% gewijzigd door KopjeThee op 27-11-2011 08:30 ]