How to update column in table if api results have been modified

Question

My application: My application allows users to predict the scores of all upcoming soccer games for the next matchday. I get this data from an API and store the upcoming games in my database. These upcoming games have a status of Scheduled. Now I want to run a cronjob every few minutes that checks if the status of those matches have been changed to in_play or finished, if this is the case I want to update my status field in my database for the correct match to the status field from the api.

How can I check if the status has been changed and modify the correct match in my database? I have a match_id stored which can maybe be used for this?

My code: updateStatus job

public function handle()
    {
        $this->updateStatus();
    }

    public function updateStatus() {

        $this->getMatches();

        // check if status has been changed from schedulded to 'in_play' or 'finished'
        // update the match status of the right matches in my database
    }

    public function getMatches() {

        $client = new Client();
        $uri = 'http://api.football-data.org/v2/competitions/PL/matches/?matchday=12&season=2018&matches';
        $header = ['headers' => ['X-Auth-Token' => 'My-token']];
        $res = $client->get($uri, $header);
        return json_decode($res->getBody()->getContents(), true);

    }

getMatches job (this job gets the api data and stores it in the database)

 public function handle()
    {
        $this->saveMatches();
    }

    public function saveMatches()
    {
        $matches = $this->getMatches();

        collect($matches['matches'])
            ->each(function ($match, $key) {
                $date = new DateTime($match['utcDate']);
                Match::create([
                    'match_id' => $match['id'],
                    'homeTeam' => $match['homeTeam']['name'],
                    'awayTeam' => $match['awayTeam']['name'],
                    'status'   => $match['status'],
                    'date'     => $date->format('Y-m-d'),
                    'time'     => $date->format('H:i'),
                    'matchday' => $match['matchday'],
                    'homeScore'=> $match['score']['fullTime']['homeTeam'],
                    'awayScore'=> $match['score']['fullTime']['awayTeam']
                ]);
            });


    }
    public function getMatches()
    {
        $client = new Client();
        $uri = 'http://api.football-data.org/v2/competitions/PL/matches/?matchday=12&season=2018&matches';
        $header = ['headers' => ['X-Auth-Token' => 'My-token']];
        $res = $client->get($uri, $header);
        return json_decode($res->getBody()->getContents(), true);
    }

score 1 · Accepted Answer · answered Oct 29 '18 at 17:08

What you probably want to do is utilize Laravel's updateOrCreate() method on your Match object. The uniquely identifying information appears to be the match id. If this doesn't ever change, then when you are looping through your each statement you can do this:

Match::updateOrCreate([
        'id' => $match['id'],
    ],[
        'homeTeam' => $match['homeTeam']['name'],
        'awayTeam' => $match['awayTeam']['name'],
        'status'   => $match['status'],
        'date'     => $date->format('Y-m-d'),
        'time'     => $date->format('H:i'),
        'matchday' => $match['matchday'],
        'homeScore'=> $match['score']['fullTime']['homeTeam'],
        'awayScore'=> $match['score']['fullTime']['awayTeam']
    ]);

What this does is look for an existing match with this same ID. If it already exists, it will simply update it with all of the information provided by the API, including the status of the match and the scores. If it doesn't yet exist, it will instead create it and store it in the database as a new match with all of the provided information. Hope this helps!

The updateOrCreate method is exactly what I need in my situation, very good solution! — Dax, Oct 29 '18 at 17:32

How to update column in table if api results have been modified

1 Answers1