ランキング実装に失敗するとこうなるという例 / Broken ranking

PHP勉強会@東京 #134 の資料です。

Transcript

1. ௕୩઒ஐر
   ϥϯΩϯά࣮૷ʹ
   ࣦഊ͢Δͱ͜͏ͳΔͱ͍͏ྫ
   

   View Slide

2. ௕୩઒ஐر
   !UPN[PI
   

   View Slide

3. ϥΠϑϫʔΫ
   ςοΫΧϯϑΝϨϯεӡӦࢀՃ
   8FCJ04ΞϓϦ։ൃ Ϗʔϧ
   $16 ϨτϩήʔϜػ ిࢠ޻࡞ 

   αοΧʔ؍ઓ 

   ϨϯλϧΧʔτϨʔε ʜ
   σδλϧαʔΧεגࣜձࣾ
   ෭ஂ௕$50
   !UPN[PI
   

   View Slide

4. View Slide

5. View Slide

6. 8FC%FWFMPQNFOUXJUI
   8&"3&)*3*/(
   IUUQXXXEHDJSDVTDPN
   0NPUFTBOEP 5PLZP
   

   View Slide

7. ࢿྉެ։ࡁͰ͢
   !UPN[PI
   

   View Slide

8. ࠓ೔ͷςʔϚ
   

   View Slide

9. View Slide

10. ϥϯΩϯά࣮૷ʹ
    ࣦഊ͢Δͱ͜͏ͳΔͱ͍͏ྫ
    

    View Slide

11. View Slide

12. View Slide

13. View Slide

14. View Slide

15. View Slide

16. View Slide

17. SELECT tmp.attendee_id, tmp.score, tmp.rank
    FROM
    (
    select attendee_id, score, @c:=@c+1 rank
    from challenge_players
    where conference_id = :conference_id and challenge_id = :challenge_id
    order by score desc, last_scored asc
    ) tmp
    ', [
    'conference_id' => $challenge->conference_id,
    'challenge_id' => $challenge->id,
    ])
    ->fetchAll('assoc');
    

    View Slide

18. SELECT tmp.attendee_id, tmp.score, tmp.rank
    FROM
    (
    select attendee_id, score, @c:=@c+1 rank
    from challenge_players
    where conference_id = :conference_id and challenge_id = :challenge_id
    order by score desc, last_scored asc
    ) tmp
    ', [
    'conference_id' => $challenge->conference_id,
    'challenge_id' => $challenge->id,
    ])
    ->fetchAll('assoc');
    

    View Slide

19. SELECT tmp.attendee_id, tmp.score, tmp.rank
    FROM
    (
    select attendee_id, score, @c:=@c+1 rank
    from challenge_players
    where conference_id = :conference_id and challenge_id = :challenge_id
    order by score desc, last_scored asc
    ) tmp
    ', [
    'conference_id' => $challenge->conference_id,
    'challenge_id' => $challenge->id,
    ])
    ->fetchAll('assoc');
    είΞͷ߱ॱ
    

    View Slide

20. SELECT tmp.attendee_id, tmp.score, tmp.rank
    FROM
    (
    select attendee_id, score, @c:=@c+1 rank
    from challenge_players
    where conference_id = :conference_id and challenge_id = :challenge_id
    order by score desc, last_scored asc
    ) tmp
    ', [
    'conference_id' => $challenge->conference_id,
    'challenge_id' => $challenge->id,
    ])
    ->fetchAll('assoc');
    είΞͷ߱ॱ
    

    View Slide

21. SELECT tmp.attendee_id, tmp.score, tmp.rank
    FROM
    (
    select attendee_id, score, @c:=@c+1 rank
    from challenge_players
    where conference_id = :conference_id and challenge_id = :challenge_id
    order by score desc, last_scored asc
    ) tmp
    ', [
    'conference_id' => $challenge->conference_id,
    'challenge_id' => $challenge->id,
    ])
    ->fetchAll('assoc');
    είΞͷ߱ॱ ࠷ऴείΞ֫ಘ࣌ࠁͷঢॱ
    

    View Slide

22. SELECT tmp.attendee_id, tmp.score, tmp.rank
    FROM
    (
    select attendee_id, score, @c:=@c+1 rank
    from challenge_players
    where conference_id = :conference_id and challenge_id = :challenge_id
    order by score desc, last_scored asc
    ) tmp
    ', [
    'conference_id' => $challenge->conference_id,
    'challenge_id' => $challenge->id,
    ])
    ->fetchAll('assoc');
    είΞͷ߱ॱ ࠷ऴείΞ֫ಘ࣌ࠁͷঢॱ
    ಉ఺ͳΒઌʹͦͷ఺਺ʹୡͨ͠ํ͕উͪ
    

    View Slide

23. View Slide

24. ͜ΕͰ ఺ʹͳͬͨ
    

    View Slide

25. ͜ΕͰ ఺ʹͳͬͨ
    ͜ΕͰ ఺ʹ௥͍෇͍ͨ
    

    View Slide

26. $challengeInputsQuery = TableRegistry::get('ChallengeInputs')->find()
    ->where([
    'conference_id' => $challengePlayer->conference_id,
    'challenge_id' => $challengePlayer->challenge_id,
    'attendee_id' => $challengePlayer->attendee_id,
    ]);
    $scoreTotal = $challengeInputsQuery
    ->select([
    'score_total' => $challengeInputsQuery->func()->sum('score')
    ])
    ->first();
    if (! is_null($scoreTotal->score_total)){
    $challengePlayer->score = intval($scoreTotal->score_total);
    $challengePlayer->last_scored = new Time();
    } else {
    $challengePlayer->score = 0;
    }
    

    View Slide

27. $challengeInputsQuery = TableRegistry::get('ChallengeInputs')->find()
    ->where([
    'conference_id' => $challengePlayer->conference_id,
    'challenge_id' => $challengePlayer->challenge_id,
    'attendee_id' => $challengePlayer->attendee_id,
    ]);
    $scoreTotal = $challengeInputsQuery
    ->select([
    'score_total' => $challengeInputsQuery->func()->sum('score')
    ])
    ->first();
    if (! is_null($scoreTotal->score_total)){
    $challengePlayer->score = intval($scoreTotal->score_total);
    $challengePlayer->last_scored = new Time();
    } else {
    $challengePlayer->score = 0;
    }
    ਖ਼ղτʔΫϯ͡Όͳͯ͘΋
    MBTU@TDPSFEΛߋ৽ͯ͠Δʜɻ
    

    View Slide

28. View Slide

29. ແ೦
    

    View Slide

30. ·ͱΊ
    

    View Slide

31. wϥϯΩϯά͸είΞτʔλϧΛϨίʔυʹ

    ॻ͍͓͚ͯ͹ൺֱత؆୯ʹ࡞ΕΔΑɻ
    ·ͱΊ
    

    View Slide

32. wϥϯΩϯά͸είΞτʔλϧΛϨίʔυʹ

    ॻ͍͓͚ͯ͹ൺֱత؆୯ʹ࡞ΕΔΑɻ
    wಉ఺͕ൃੜͨ࣌͠ͷϩδοΫΛॻ͘ͳΒςετ΋

    ͠Α͏Ͷɻ
    ·ͱΊ
    

    View Slide

33. wϥϯΩϯά͸είΞτʔλϧΛϨίʔυʹ

    ॻ͍͓͚ͯ͹ൺֱత؆୯ʹ࡞ΕΔΑɻ
    wಉ఺͕ൃੜͨ࣌͠ͷϩδοΫΛॻ͘ͳΒςετ΋

    ͠Α͏Ͷɻ
    w·͋੝Γ্͕͍͍͔ͬͨ͠ɻʢࢮʣ
    ·ͱΊ
    

    View Slide

34. ͓ΘΓ
    !UPN[PI
    ͓ΘΓ
    

    View Slide

35. ༨ஊ
    

    View Slide

36. View Slide

37. 
    

    View Slide

38. 
    

    View Slide

39. View Slide

40. GPSUFFKQ
    ϑ Υ ϧ ς
    wΧϯϑΝϨϯεӡӦࢧԉπʔϧ
    wεϙϯαʔืू ੥ٻॻ࡞੒ʢ.JTPDB࿈ܞʣ ϩΰड͚औΓ Ұ੪ϝʔ
    ϧ εϙϯαʔҰཡ"1*
    wϓϩϙʔβϧืूʢ$G1ʣ બఆ λΠϜςʔϒϧΤσΟλ Ұ੪ϝʔϧ
    ϓϩϙʔβϧҰཡ"1* λΠϜςʔϒϧදࣔ
    w4MBDL࿈ܞͯ͠εϙϯαʔԠื΍ϓϩϙʔβϧԠื࣌ʹօͰ੝Γ্͕Δ
    
    wνέοτൢച࣌&WFOUCSJUF࿈ܞͯ͠ࢀՃऀऔΓࠐΈ 5XJUUFSΞΠί
    ϯऔΓࠐΈ 23ίʔυੜ੒ $47μ΢ϯϩʔυ

    ‎ͦͷ··ҹ࡮԰͞Μʹೖߘͯ͠ΞΠίϯ෇໊͖ࡳ
    wͦͷଞػೳ͍Ζ͍ΖʢνϟϨϯδػೳ΋͋ΔΑʣ
    

    View Slide

41. GPSUFFKQ
    ϑ Υ ϧ ς
    wЌӡ༻த
    w·ͩ࢖͍ʹ͔ͬͨ͘Γ෼͔Γʹ͍͘ͱ͜Ζ΋͋Δ
    wόά͸େ෼ͳ͘ͳͬͨ
    w௕୩઒ͷ஌Γ߹͍ͷํͰ͋Ε͹ൺֱత

    ҆৺ͯ͠࢖͑ΔͷͰ͸

    

    ͝૬ஊ͍ͩ͘͞
    

    View Slide