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

why don't you add a where clause and compare the timestamp to date_sub(now(), interval 30 day). which will give you the last 30 days of result — MekjkrhG, Jun 20 '19 at 03:42
@MekjkrhG how would I do that? I tried doing a where on it and it gives me an error because of my counts. I couldnt get past the query :/ — levi, Jun 20 '19 at 07:37
@Kray In order to filter out the past 30 days, you need to have a date-column with your data - is that in the `checkSumID` column? (if so, its an odd name for it) -- alternatively, and preferably, can you show your table structure of the `pickup_results` table? — Qirel, Jul 05 '19 at 19:15
In your working example, you do `$timeStamp / 1000` when converting from a timestamp to a date in PHP. I believe you should also be able to do that same division on checkSumID before converting it to a unix time in one of the other examples to adjust for that. i.e. for MekjkrhG's answer I think you can change one line to be this to fix it `->where(DB::raw("from_unixtime(checkSumID / 1000) > date_sub(now(), interval 30 day)"))`. — Patrick Fay, Jul 07 '19 at 07:32

score 1 · Answer 1 · edited Jul 06 '19 at 07:43

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.

score 1 · Accepted Answer · answered Jul 07 '19 at 14:54

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.

score -1 · Answer 3 · answered Jun 20 '19 at 08:01

-1

try this from_unixtime() returns a date/datetime from unix timestamp

$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("from_unixtime(checkSumID) > date_sub(now(), interval 30 day)"))
        ->groupBy('playerID')
        ->orderBy('wins','DESC')
        ->get();

answered Jun 20 '19 at 08:01

MekjkrhG

211
1
8

Nope, doesnt change the query result. – levi Jun 21 '19 at 06:12
nope :'(. It did not change anything about the query. Wonder if it has to do with the fact that the PlayerID is grouped? or the column checkSumID isnt being grabbed? – levi Jun 21 '19 at 16:26
have you tried after removing the group by? the checksumID dont have to be grabbed to do a where clause – MekjkrhG Jun 21 '19 at 20:41
I need to group the playerID to show singular counts for the player. you can view this query result at http://www.Krayvok.com/t1 select "Leaderboards". – levi Jun 22 '19 at 21:53
Any thoughts on what to do @MekjkrhG – levi Jun 27 '19 at 19:35

score -2 · Answer 4 · answered Jul 01 '19 at 10:13

-2

Try this one, Update your query like this

 $thirty_days_ahead = date('Y-m-d H:i:s', strtotime("-30 days"));
    $now = date('Y-m-d H:i:s', strtotime("0 days"));


    $results = 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();

answered Jul 01 '19 at 10:13

Arun P

541
4
11

Any thoughts on what to do @Arun P – levi Jul 02 '19 at 04:22
@kray this will generate a query equallent to "select playerID, COUNT(CASE WHEN gameResult = 'Win' THEN 1 END) AS wins, COUNT(CASE WHEN gameResult = 'Loss' THEN 1 END) AS loss, COUNT(CASE WHEN gameResult = 'Tie' THEN 1 END) AS tie from `pickup_results` where `created_at` < 2019-07-01 10:10:44 and `created_at` > 2019-06-01 10:10:44 group by `playerID` order by `wins` desc" try execution that on your db, the reason that it returns empty might be because there was no entries in the last 30 days – Arun P Jul 02 '19 at 07:34
I will update my OP with what I have and what its doing. – levi Jul 02 '19 at 22:40

Filtering existing query by date

4 Answers4