Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

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

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

HASEGAWA Tomoki

April 24, 2019
Tweet

More Decks by HASEGAWA Tomoki

Other Decks in Technology

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