#StackBounty: #php #laravel-5 #laravel-5.8 Filtering existing query by date

Bounty: 50

This has yet to be answered with a functional answer.

I have a few methods that Ive put together for some fun stats on a game I play often.

The method below will take the total count of all games played, match a player to the player list then show a summation of the total wins/loss/ties.

This is great, and functional.

However, due to popular demand Ive been asked to adjust the query to now take into account the date in which the game has played. I would like to filter it down to the last 30 days of summation. How can I do this?

I wanted to ask around before spending the time to rewrite the entire thing. Preferably, everything stays the same just filter down by date.

The date key for the database is checkSumID it is a UNIX timestamp.

private function topPlayers() {

        $topPlayersList = array();

        $playersList = DB::table('pickup_results')
            ->select(DB::raw("playerID"),
                DB::raw("COUNT(CASE WHEN gameResult = 'Win'  THEN 1 END) AS wins"),
                DB::raw("COUNT(CASE WHEN gameResult = 'Loss' THEN 1 END) AS loss"),
                DB::raw("COUNT(CASE WHEN gameResult = 'Tie' THEN 1 END) AS tie")
            )
            ->groupBy('playerID')
            ->orderBy('wins','DESC')
            ->get();

        $i = 0;

        foreach ($playersList as $playerListData) {

            if ($playerListData->wins + $playerListData->loss + $playerListData->tie >= 25) {

                $avgPick = $this->getPlayerAvgPickCount($playerListData->playerID);

                $playerRecordID = $playerListData->playerID;

                $playerNameLookup = Players::where([
                    'player_id' => $playerListData->playerID
                ])->first();

                $playerListData->playerID = $playerNameLookup->player_name;

                $topPlayersList[$i] = array(
                    'name' => $playerNameLookup->player_name,
                    'total' => +$playerListData->wins + +$playerListData->loss + +$playerListData->tie,
                    'wins' => +$playerListData->wins,
                    'loss' => +$playerListData->loss,
                    'tie' => +$playerListData->tie,
                    'percent' => +$playerListData->loss == 0 ? 0 : round(
                            (+$playerListData->wins / (+$playerListData->wins + +$playerListData->loss) * 100),
                            2
                        ) . ' %',
                    'avg_pick' => $avgPick[0]->average,
                    'player_id' => $playerRecordID
                );

                $i++;

            }

        }

        return $this->sortArray($topPlayersList,'percent','DESC');
    }

There is a method that I wrote that does something similar, but more on a single person basis, but not sure how I can stitch the two together without a complete rewrite.

Here is that method

private function getTotalGamesPlayed30DayWinLossTies() {

        //PickupResults::where('playerID', '=', $this->getPlayerID())->where('checkSumID', '=', Carbon::now()->subDays(30)->timestamp)->count()
        $results = PickupResults::get();

        //$results = PickupResults::where('playerID', '=', $this->getPlayerID())->get();

        $count = 0;
        $wins = 0;
        $loss = 0;
        $tie = 0;
        foreach ($results as $result) {

            if ($result->playerID === $this->playerID) {
                $timeStamp = $result->checkSumID;

                $converted = date('m/d/Y', $timeStamp / 1000);
                if (strtotime($converted) > strtotime('-30 days')) {
                    $count = $count + 1;
                    if ($result->gameResult === 'Win') {
                        $wins = $wins + 1;
                    }
                    if ($result->gameResult === 'Loss') {
                        $loss = $loss + 1;
                    }
                    if ($result->gameResult === 'Tie') {
                        $tie = $tie + 1;
                    }

                }
            }

        }

        return
            array(
                'total' => $count,
                'wins' => $wins,
                'loss' => $loss,
                'tie' => $tie,
                'percent' => $loss == 0 ? 0 : round(($wins / ( $wins + $loss) * 100 ),2) . ' %'
            );
    }

Any help would be greatly appreciated.


When using the answer by Arun P

$playersList = DB::table('pickup_results')
    ->select(DB::raw("playerID"),
        DB::raw("COUNT(CASE WHEN gameResult = 'Win'  THEN 1 END) AS wins"),
        DB::raw("COUNT(CASE WHEN gameResult = 'Loss' THEN 1 END) AS loss"),
        DB::raw("COUNT(CASE WHEN gameResult = 'Tie' THEN 1 END) AS tie"))
    ->where('checksumID','<',$now)->where('checksumID','>',$thirty_days_ahead)
    ->groupBy('playerID')
    ->orderBy('wins', 'DESC')
    ->get();

It will return 0 results. This is incorrect; I am trying to gather all games a player has played within the last 30 days only. Nothing more, nor less.

You can visit http://www.Krayvok.com/t1 and view the stats page for a working example.

I am trying to take the current leader-boards which displays all players total games played. I would like to filter it down to show only the players whom has had a game played in the last 30 days from today’s date (rolling 30 day).


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.