Filtering existing query by date

Question

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).

Answer 1

I have made some changes to your existing query,

$timestamp_from = date('Y-m-d', strtotime('-30 days'));

$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(DB::raw("DATE((checkSumID/1000)) >= '{$timestamp_from}'"))
    ->groupBy('playerID')
    ->orderBy('wins','DESC')
    ->get();

In your method getTotalGamesPlayed30DayWinLossTies there is a division by 1000 for checkSumID and I have included it in the query. The where clause for checkSumID has been altered to compare dates.

Try this an comment if you need any assistance.

Answer 2

Try this -

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

Laravel creates the carbon date instance for now() as mentioned in its documentation. Chaining the above carbon methods (carbon docs) returns the date 30 days ago.

All the SELECT statements would convert to something like this (when the current date is '2019-07-06') -

COUNT(CASE WHEN gameResult = 'Win' AND checkSumID > '2019-06-06' THEN 1 END) AS wins

This would count the results which were created after '2019-06-06' (in the last 30 days) including the current date.