Users can re-submit results for the competition and it updates the results on the scoreboard, however, it also creates a save of the result for the "Way back when" feature of being able to view results previously to see improvements of scores.

The issue I have is that duplicate entries exist for certain months, the only thing that changes in the database is the primary key id, the created_at and updated_at. If the column values: discipline_n wheren is one through six are all the same as any others should not be queried, ie:

id 1                                     id 2
username foo                             username foo
discipline_one 4                         discipline_one 4
discipline_two 5                         discipline_two 5
discipline_three 8                       discipline_three 8
discipline_four 9                        discipline_four 9
discipline_five 4                        discipline_five 4
discipline_six 6                         discipline_six 6
created_at yesterday for example         created_at today for example
updated_at yesterday for example         updated_at today for example

Should be considered a duplicate and thus be removed from the result. I've tried to use the distinct() function built into Laravel's ORM but I'm still getting duplicates. Can this be done with just SQL? My expected output here would be:

id 2
username foo
discipline_one 4
...

Where id 1 is ignored as a duplicate. I've tried:

$waybackWhen = \App\Models\ResultSave::where('username', $result->username)
    ->distinct([
        'discipline_one',
        'discipline_two',
        'discipline_three',
        'discipline_four',
        'discipline_five',
        'discipline_six',
    ])
    ->get();

CodePudding user response：

I've played around with it a little bit from a question I asked some time back.

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER
    (PARTITION BY username, discipline_one, discipline_two, discipline_three,
     discipline_four, discipline_five, discipline_six ORDER BY id DESC) rn
    FROM results
)

SELECT id, username, discipline_one, discipline_two, discipline_three,
     discipline_four, discipline_five, discipline_six, created_at, updated_at
FROM cte
WHERE rn = 1

Credits: @tim-biegeleisen MySQL : Find duplicate records but EXCLUDE the first one from the list

Before

After

Laravel

    public static function waybackWhen($result)
    {
        $username = $result->username;

        config()->set('database.connections.mysql.strict', false);
        DB::reconnect(); // Important as the existing connection if any would be in strict mode.

        $resultSet = DB::select("
            WITH cte AS (
                SELECT *, ROW_NUMBER() OVER
                (PARTITION BY username, discipline_one, discipline_two, discipline_three,
                 discipline_four, discipline_five, discipline_six ORDER BY id DESC) rn
                FROM results
            )
            
            SELECT id, username, discipline_one, discipline_two, discipline_three,
                 discipline_four, discipline_five, discipline_six, created_at, updated_at
            FROM cte
            WHERE rn = 1 AND username = ? 
       ", [ $username ]);

        // Now changing back the strict ON.
        config()->set('database.connections.mysql.strict', true);
        DB::reconnect();

        return $resultSet;

    }

Output

=> [
     {#3409
        "id": 9,
        "username": "foo",
        "discipline_one": "4",
        "discipline_two": "5",
        "discipline_three": "8",
        "discipline_four": "9",
        "discipline_five": "4",
        "discipline_six": "6",
        "created_at": "2021-11-03 19:45:20",
        "updated_at": "2021-11-03 19:45:50",
     },
   ]