'Checking Whether Table Data Exists, Updating / Inserting Into Two Tables & Posting End Outcome

I am working on my cron system which gathers informaiton via an API call. For most, it has been fairly straight forward, but now I am faced with multiple difficulties, as the API call is dependant on who is making the API request. It runs through each users API Key and certain information will be visible/hidden to them and visaversa to the public.

There are teams, and users are part of teams. A user can stealth their move, however all information will be showed to them and their team, however this will not be visible to their oponent, however both teams share the same id and have access tothe same informaiton, just one can see more of it than the other.

Defendants Point Of View

"attacks": {
    "12345`": {
        "timestamp": 1645345234,
        "attacker_id": "",
        "attacker_team_id": "",
        "defender_id": 321,
        "defender_team_id": 1,
        "stealthed": 1
    }
}

Attackers Point Of View

"attacks": {
    "12345`": {
        "timestamp": 1645345234,
        "attacker_id": 123,
        "attacker_team_id": 2
        "defender_id": 321,
        "defender_team_id": 1,
        "stealthed": 1,
        "boosters": {
            "fair_fight": 3,
            "retaliation": 1,
            "group_attack": 1
        }
    }
}

So, if the defendant's API key is first used, id 12345 will already be in the team_attacks table but will not include the attacker_id and attacker_team_id. For each insert there after, I need to check to see whether the new insert's ID already exist and has any additional information to add to the row.

Here is the part of my code that loops through the API and obtains the data, it loops through all the attacks per API Key;

else if ($category === "attacks") {
    
    $database = new Database();
    
    foreach($data as $attack_id => $info) {
        
        $database->query('INSERT INTO team_attacks (attack_id, attacker_id, attacker_team_id, defender_id, defender_team_id) VALUES (:attack_id, :attacker_id, :attacker_team_id, :defender_id, :defender_team_id)');
        $database->bind(':attack_id', $attack_id);
        $database->bind(':attacker_id', $info["attacker_id"]);
        $database->bind(':attacker_team_id', $info["attacker_team_id"]);
        $database->bind(':defender_id', $info["defender_id"]);
        $database->bind(':defender_team_id', $info["defender_team_id"]);
        $database->execute();
        
    }
    
}

I have also been submitting to the news table, and typically I have simply been submitting X new entries have been added or whatnot, however I haven't a clue if there is a way to check during the above if any new entries and any updated entries to produce two news feeds:

  • 2 attacks have bee updated.
  • 49 new attack information added.

For this part, I was simply counting how many is in the array, but this only works for the first ever upload, I know I cannot simply count the array length on future inserts which require additional checks.

If The attack_id Does NOT Already Exist I also need to submit the boosters into another table, for this I was adding them to an array during the above loop and then looping through them to submit those, but this also depends on the above, not simply attempting to upload for each one without any checks. Boosters will share the attack_id.

With over 1,000 teams who will potentially have at least one members join my site, I need to be as efficient as this as possible. The API will give the last 100 attacks per call and I want this to be within my cron which collects any new data every 30 seconds, so I need to sort through potentially 100,000.

sql


Solution 1:[1]

In SQL, you can check conditions when inserting new data using merge: https://en.wikipedia.org/wiki/Merge_(SQL)

Depending on the database you are using, the name and syntax of the command might be different. Common names for the command are also upsert and replace.

But: If you are seeking for high performance and almost-realtimeness, consider using a cache holding critical aggregated data instead of doing the aggregation 100'000 times per minute.

Solution 2:[2]

This may or may not be the "answer" you're looking for. The question(s) imply use of a single table for both teams. It's worth considering one table per team for writes to avoid write contention altogether. The two data sets could be combined at query time in order to return "team" results via the API. At scale, you could have another process calculating and storing combined team results in an API-specific cache table that serves the API request.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 draz
Solution 2 Jason Weber

Related Questions