Trying to search and sort a collection by comparing the objects to each other

I have this query:

        $locs_with_units = DB::table('units')
        
        ->join('locations', 'locations.id', '=', 'units.location_id')
        ->join('castles', 'castles.id', '=', 'units.castle_id')
        ->join('unit_types', 'unit_types.id', '=', 'units.unit_type_id')
        ->select('units.location_id',
            'units.previous_location_id',
            'units.id as unit_id',
            'units.castle_id',
            'castles.guild_id',
            'units.unit_type_id',    
            'units.current_health',
            'unit_types.damage',
            'unit_types.range')
        ->get();
            
        echo response()->json($locs_with_units);

which results in some data like this:

[
    {
        "location_id": 1,
        "previous_location_id": 1,
        "unit_id": 1,
        "castle_id": 1,
        "guild_id": 1,
        "unit_type_id": 1,
        "current_health": 100,
        "damage": 10,
        "range": 1
    },
    {
        "location_id": 2,
        "previous_location_id": 2,
        "unit_id": 2,
        "castle_id": 2,
        "guild_id": null,
        "unit_type_id": 1,
        "current_health": 100,
        "damage": 10,
        "range": 1
    },
    {
        "location_id": 1,
        "previous_location_id": 3,
        "unit_id": 3,
        "castle_id": 3,
        "guild_id": 1,
        "unit_type_id": 1,
        "current_health": 100,
        "damage": 10,
        "range": 1
    },
    {
        "location_id": 2,
        "previous_location_id": 3,
        "unit_id": 4,
        "castle_id": 3,
        "guild_id": 1,
        "unit_type_id": 1,
        "current_health": 100,
        "damage": 10,
        "range": 1
    },
    {
        "location_id": 2,
        "previous_location_id": 2,
        "unit_id": 5,
        "castle_id": 2,
        "guild_id": null,
        "unit_type_id": 1,
        "current_health": 100,
        "damage": 10,
        "range": 1
    }
]

I'm completely stuck as to how I can go about it, but in pseudo code I need to:

• Find each location_id where there are Units from different castle_id and different guild_id (except in cases where both guild_id are null)

• Then group the units on that location_id by "defenders" and "attackers".

  • Defenders will have previous_location_id equal to location_id and attackers will not.
  • Any Units with a previous_location_id that's not equal to the location_id(an "attacker") BUT has the same guild_id as any of the defenders should be considered a defender as well.

Once I have the defenders and attackers for a given location_id identified I'll apply the combat logic.

If there are no more attackers left after combat then it will end there and move on to the next location_id and repeat the above process again.

• If there are no more defenders left and only one attacker castle_id then they will win ownership of that location_id

• If there are no more defenders left and more than one attacker left of different castle_id but the same guild_id the ownership of that location_id will go to the castle_id of whoever has the largest amount of units left.

• If there are no more defenders left and the remaining attackers have different guild_id then find the two largest groups of units from each guild_id and apply combat logic to only those two groups, all others will be sent away from the location_id