deja

Experience

390

0 Best Reply Awards

  • Member Since 9 Months Ago
  • 0 Lessons Completed
  • 0 Favorites

11th January, 2018

deja left a reply on Trying To Use Query Builder To Constrain By Non-unique Values In A Column • 9 months ago

I've solved this using a suggestion from StackOverflow:

$units = DB::table('units AS u1')
    ->select('u1.location_id', 'u1.previous_location_id', 'u1.id as unit_id', 'u1.castle_id', 'c.guild_id', 'u1.unit_type_id', 'u1.current_health', 'ut.damage', 'ut.range')
    ->join('units AS u2', 'u1.location_id', '=', 'u2.location_id')
    ->join('castles AS c', 'c.id', '=', 'u1.castle_id')
    ->join('unit_types AS ut', 'ut.id', '=', 'u1.unit_type_id')
    ->whereColumn('u1.castle_id', '<>', 'u2.castle_id')
    ->orderBy('u1.id')
    ->get();

Thank you everyone for your help!

deja left a reply on Trying To Use Query Builder To Constrain By Non-unique Values In A Column • 9 months ago

Here's a hypothetical if it helps:

  • units has 100 rows
  • 10 rows have location_id: 1
  • 20 rows have location_id: 3
  • etc...

In the 10 rows that have location_id: 1 all 10 also have castle_id: 76

In the 20 rows that have location_id: 3 15 of them have castle_id: 99 and 5 of them have castle_id: 42

My query would skip over the 10 rows with location_id: 1 because it doesn't meet my criteria and it would select the 20 that do, then go on to check the remaining 70.

deja left a reply on Trying To Use Query Builder To Constrain By Non-unique Values In A Column • 9 months ago

I'm not trying to get the count itself, but rather the logic that is determining when a row has identical location_id and castle_id is a unique value

The raw SQL pseudo code could be like:

SELECT location_id, 
FROM units
WHERE (DISTINCT castle_id);

Do you think I need to just use methods on my collection instead?

deja left a reply on Trying To Use Query Builder To Constrain By Non-unique Values In A Column • 9 months ago

thanks @Snapey

I got results like this:

[
    {
        "location_id": 2,
        "count": 2
    },
    {
        "location_id": 4,
        "count": 2
    },
    {
        "location_id": 1,
        "count": 1
    },
    {
        "location_id": 3,
        "count": 1
    },
    {
        "location_id": 5,
        "count": 1
    }
]

How could I incorporate this logic into my original query to constrain it so that it's only selecting the entries from units where count > 1

deja left a reply on Trying To Use Query Builder To Constrain By Non-unique Values In A Column • 9 months ago

With the above changes I get a new error message:

SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "'count'"
LINE 1: ...ct "location_id", COUNT(DISTINCT('castle_id')) as 'count' fr...
^ (SQL: select "location_id", COUNT(DISTINCT('castle_id')) as 'count' from "units" group by "location_id" order by "count" desc)

I'm using PostgreSQL. It doesn't seem like like the backticks or the double quotes...

deja left a reply on Trying To Use Query Builder To Constrain By Non-unique Values In A Column • 9 months ago

Getting an error:

SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "`"
LINE 1: ...ct "location_id", COUNT(DISTINCT(`castle_id`)) as `count` fr...
^ (SQL: select "location_id", COUNT(DISTINCT(`castle_id`)) as `count` from "units" group by "location_id" order by "count" desc)

deja left a reply on Trying To Use Query Builder To Constrain By Non-unique Values In A Column • 9 months ago

I found out how to do it using raw SQL like this:

SELECT location_id, 
COUNT(DISTINCT castle_id)
FROM units
GROUP BY location_id
ORDER BY COUNT DESC;

which gives me: | location_id | count | | ------------- | ------------- | | 2 | 2 | | 4 | 2 | | 1 | 1 | | 3 | 1 | | 5 | 1 |

Is there a way to do this in the query builder without using raw SQL?

deja started a new conversation Trying To Use Query Builder To Constrain By Non-unique Values In A Column • 9 months ago

This is my query so far:

        $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()
        ->groupBy('location_id', 'castle_id');

        echo $units;

which returns test data like this:

{
    "1": [
        {
            "location_id": 1,
            "previous_location_id": 1,
            "unit_id": 2,
            "castle_id": 1,
            "guild_id": null,
            "unit_type_id": 3,
            "current_health": 90,
            "damage": 10,
            "range": 3
        }
    ],
    "2": {
        "1": {
            "location_id": 2,
            "previous_location_id": 2,
            "unit_id": 3,
            "castle_id": 2,
            "guild_id": null,
            "unit_type_id": 5,
            "current_health": 100,
            "damage": 20,
            "range": 2
        },
        "6": {
            "location_id": 2,
            "previous_location_id": 5,
            "unit_id": 7,
            "castle_id": 5,
            "guild_id": null,
            "unit_type_id": 15,
            "current_health": 180,
            "damage": 20,
            "range": 3
        }
    },
    "3": {
        "2": {
            "location_id": 3,
            "previous_location_id": 3,
            "unit_id": 4,
            "castle_id": 3,
            "guild_id": null,
            "unit_type_id": 1,
            "current_health": 100,
            "damage": 10,
            "range": 1
        },
        "3": {
            "location_id": 3,
            "previous_location_id": 3,
            "unit_id": 5,
            "castle_id": 3,
            "guild_id": null,
            "unit_type_id": 1,
            "current_health": 100,
            "damage": 10,
            "range": 1
        }
    },
    "4": {
        "5": {
            "location_id": 4,
            "previous_location_id": 4,
            "unit_id": 8,
            "castle_id": 4,
            "guild_id": null,
            "unit_type_id": 20,
            "current_health": 300,
            "damage": 40,
            "range": 2
        },
        "7": {
            "location_id": 4,
            "previous_location_id": 1,
            "unit_id": 1,
            "castle_id": 1,
            "guild_id": null,
            "unit_type_id": 1,
            "current_health": 100,
            "damage": 10,
            "range": 1
        }
    },
    "5": {
        "4": {
            "location_id": 5,
            "previous_location_id": 5,
            "unit_id": 6,
            "castle_id": 5,
            "guild_id": null,
            "unit_type_id": 15,
            "current_health": 180,
            "damage": 20,
            "range": 3
        }
    }
}

I'm trying to find a way to constrain the query so that I only get the results where location_id is the same but castle_id is different, which would be these:

{
    "2": {
        "1": {
            "location_id": 2,
            "previous_location_id": 2,
            "unit_id": 3,
            "castle_id": 2,
            "guild_id": null,
            "unit_type_id": 5,
            "current_health": 100,
            "damage": 20,
            "range": 2
        },
        "6": {
            "location_id": 2,
            "previous_location_id": 5,
            "unit_id": 7,
            "castle_id": 5,
            "guild_id": null,
            "unit_type_id": 15,
            "current_health": 180,
            "damage": 20,
            "range": 3
        }
    "4": {
        "5": {
            "location_id": 4,
            "previous_location_id": 4,
            "unit_id": 8,
            "castle_id": 4,
            "guild_id": null,
            "unit_type_id": 20,
            "current_health": 300,
            "damage": 40,
            "range": 2
        },
        "7": {
            "location_id": 4,
            "previous_location_id": 1,
            "unit_id": 1,
            "castle_id": 1,
            "guild_id": null,
            "unit_type_id": 1,
            "current_health": 100,
            "damage": 10,
            "range": 1
        }
    },
}

I tried ->whereColumn('units.location_id', '<>', 'units.castle_id')as suggested in this thread but that didn't work in instances where the castle_id happened to have a similar key.

deja left a reply on Adding Unique Constraints To A Query • 9 months ago

I'm sorry I misunderstood the results at first. This unfortunately doesn't get the query I desired.

I can't use ->whereColumn('units.location_id', '<>', 'units.castle_id') because it will ignore situations where:

there are 10 units with location_id: 1 and 5 of them have castle_id: 1 and the other 5 have castle_id: 123

I would want these 10 units as part of my results because they are all on the same location_id but there are different owners(castle_id).

Guess I'll be back at it tomorrow after work! Thanks anyways @Snapey

deja left a reply on Adding Unique Constraints To A Query • 9 months ago

@Snapey no problem, thanks so much for your help!

This problem has been driving me absolutely mad and I'm elated to be getting some feedback to help give me new ideas or solutions.

Your new suggestion seemed to work mostly. It's removed the correct locations and only focused on the ones I want, but it's only displaying the first unit at each location. I know I've had similar issues before and can probably fix that on my own with enough tinkering.

Here's the results:

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

My next step is to add some more checks for the guild_id to see if they match or not, except when both guild_id are null.

Again though, thank you so much for your time!

deja left a reply on Adding Unique Constraints To A Query • 9 months ago

@Snapey they should be, as far as I know...

        Schema::create('units', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('castle_id')->unsigned();
            $table->foreign('castle_id')->references('id')->on('castles')->onDelete('cascade');
            $table->integer('location_id')->unsigned()->nullable();
            $table->foreign('location_id')->references('id')->on('locations')->onDelete('cascade');
            $table->integer('previous_location_id')->unsigned()->nullable();
            $table->foreign('previous_location_id')->references('id')->on('locations')->onDelete('cascade');
            $table->integer('squad_id')->unsigned()->nullable();
            $table->foreign('squad_id')->references('id')->on('squads')->onDelete('cascade');
            $table->integer('unit_type_id')->unsigned();
            $table->foreign('unit_type_id')->references('id')->on('unit_types')->onDelete('cascade');
            $table->integer('current_health')->unsigned()->nullable();
            $table->integer('tick_created')->unsigned()->nullable();
            $table->timestamps();
        });

deja left a reply on Adding Unique Constraints To A Query • 9 months ago

  $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')
     ->where('units.location_id', '<>', 'units.castle_id')
     ->get();

     
     
     echo $units; 

deja left a reply on Adding Unique Constraints To A Query • 9 months ago

I changed it to <> and the error didn't change

deja left a reply on Adding Unique Constraints To A Query • 9 months ago

I get an error with that @Snapey

SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: "units.castle_id" (SQL: 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" from "units" inner join "locations" on "locations"."id" = "units"."location_id" inner join "castles" on "castles"."id" = "units"."castle_id" inner join "unit_types" on "unit_types"."id" = "units"."unit_type_id" where "units"."location_id" != units.castle_id)

deja left a reply on Adding Unique Constraints To A Query • 9 months ago

I tried that @wgmv and I got some strange results:

{
    "1": [
        {
            "location_id": 1,
            "previous_location_id": 1,
            "unit_id": 2,
            "castle_id": 1,
            "guild_id": 1,
            "unit_type_id": 1,
            "current_health": 100,
            "damage": 10,
            "range": 1
        },
        {
            "location_id": 1,
            "previous_location_id": 1,
            "unit_id": 3,
            "castle_id": 1,
            "guild_id": 1,
            "unit_type_id": 1,
            "current_health": 100,
            "damage": 10,
            "range": 1
        },
        {
            "location_id": 1,
            "previous_location_id": 1,
            "unit_id": 4,
            "castle_id": 1,
            "guild_id": 1,
            "unit_type_id": 1,
            "current_health": 100,
            "damage": 10,
            "range": 1
        }
    ],
    "2": {
        "3": {
            "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
        },
        "4": {
            "location_id": 2,
            "previous_location_id": 2,
            "unit_id": 6,
            "castle_id": 2,
            "guild_id": null,
            "unit_type_id": 1,
            "current_health": 100,
            "damage": 10,
            "range": 1
        },
        "5": {
            "location_id": 2,
            "previous_location_id": 2,
            "unit_id": 7,
            "castle_id": 2,
            "guild_id": null,
            "unit_type_id": 1,
            "current_health": 100,
            "damage": 10,
            "range": 1
        },
        "6": {
            "location_id": 2,
            "previous_location_id": 2,
            "unit_id": 8,
            "castle_id": 2,
            "guild_id": null,
            "unit_type_id": 1,
            "current_health": 100,
            "damage": 10,
            "range": 1
        },
        "7": {
            "location_id": 2,
            "previous_location_id": 2,
            "unit_id": 9,
            "castle_id": 2,
            "guild_id": null,
            "unit_type_id": 1,
            "current_health": 100,
            "damage": 10,
            "range": 1
        },
        "8": {
            "location_id": 2,
            "previous_location_id": 1,
            "unit_id": 1,
            "castle_id": 1,
            "guild_id": 1,
            "unit_type_id": 1,
            "current_health": 100,
            "damage": 10,
            "range": 1
        }
    }
}

It groups by location_id but then instead of grouping by castle_id it groups all units on location_id: 2 into individual groups which is really strange...

I've been researching possible solutions nonstop and I'm now wondering if I need some sort of raw SQL SELECT that will give more control.

10th January, 2018

deja left a reply on Adding Unique Constraints To A Query • 9 months ago

Here's an example of what I'm looking for:

Let's say there's 100 entries in the units table. 10 of those entries have both location_id: 1 and castle_id: 1. I want to ignore those 10 entries and not include them in my query of units.

Now lets say there's 20 entries that have location_id: 2, 10 of which have castle_id: 2, 5 others have castle_id: 3 and the remaining 5 have `castle_id: 99 - that's a query I want to keep.

Is this possible in my query builder or do I need to use methods on my collection instead?

The next step of that query is including a guild_id check, but I'm trying to break this up since it's been so difficult for me.

deja left a reply on Adding Unique Constraints To A Query • 9 months ago

location_id is only specified in the table of units

What I'm trying to do is find every unit which shares a location_id but has a different castle_id.

I'm confused if I should be trying to loop through the collection with foreach to compare each unit to the rest or if I need to groupBy or map in order to compare specific values of $units to other $units

Sorry if this is confusing... I'm having a difficult time concisely communicating what I'm trying to do because I really don't know what approach I should be experimenting with. I've been trying so many different things....

But please let me say I greatly appreciate the reply! I've been desperate for some guidance with this and it's weighing heavily on my mind :D

deja started a new conversation Adding Unique Constraints To A Query • 9 months ago

I have this query:

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

which returns data in this format:

[
    {
        "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": 1,
        "previous_location_id": 2,
        "unit_id": 2,
        "castle_id": 2,
        "guild_id": null,
        "unit_type_id": 1,
        "current_health": 100,
        "damage": 10,
        "range": 1
    },

    ...etc...
]

I'm trying to figure out if it's possible to add additional constraints to the query so that I can limit the results to only get the $units that have identical location_id but different castle_id.

Is it possible to add this constraint to the query or do I need to use methods on the collection instead? Possibly with a foreach loop?

I'm pleading for some direction with this... I've been banging my head against the wall trying to solve this for a couple days now.

9th January, 2018

deja started a new conversation Trying To Search And Sort A Collection By Comparing The Objects To Each Other • 9 months ago

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 this 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

4th January, 2018

deja left a reply on Help Querying Data And Comparing It Using Relationships • 9 months ago

Hi everyone!

This is where I'm at now:

        $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.id as unit_id',
            'units.current_health',
            'units.unit_type_id',
            'units.castle_id',
            'units.location_id',
            'castles.guild_id',
            'unit_types.damage',
            'unit_types.range')
        ->orderBy('units.location_id')
        ->get();

    echo response()->json($locs_with_units);

which results in:

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

I'm getting much closer to my end-goal I can just feel it!

I'm now working to reduce the results to only those location_id's that have more than one castle_id of a different value.

deja left a reply on Help Querying Data And Comparing It Using Relationships • 9 months ago

Still trying to figure this out... it's driving me crazy.

This is what I have now:

        $loc_with_units = Location::with([
            'units' => function ($query) {
                $query->select(['id', 'castle_id', 'unit_type_id']);
            }, 
            'units.castle' => function ($query) {
                $query->select(['id','guild_id']);   
            }
        ])
        ->has('units')->get(['id']);

        return response()->json($loc_with_units);

and my results are:

[
    {
        "id": 1,
        "units": []
    },
    {
        "id": 2,
        "units": []
    }
]

I don't understand why my units arrays are "empty".

I know they're not empty though because my ~~~->has('units')~~~ is working properly and only showing location_id's 1 and 2 because these are the only ones with units. If I remove that line then it returns all locations.

3rd January, 2018

deja left a reply on Help Querying Data And Comparing It Using Relationships • 9 months ago

Okay so I made a little progress...

I added a hasOneThrough to my Unit model for guilds like so:

    public function guild()
    {
        return $this->hasOneThrough('App\Guild', 'App\Castle');
    }

and now I'm able to query a unit's guild relationship using:

$unit = Unit::with('castle')->first();
        return response()->json('Castle ID: ' . $unit->castle_id . ' and Guild ID: ' . $unit->castle->guild_id . ' for Unit ID: ' . $unit->id);

So I've figured out how to refer to something via relationships but I'm still lost as to how I can compare each entry to each other. Should I be grouping them by castle_id first?

deja started a new conversation Help With A Complex Function That Searches Data And Compares It Using Relationships • 9 months ago

Hi everyone,

I'm really struggling with this function I've been working on for a couple days now and I've broken down and am now asking for help. Please be warned I'm a total newbie...

Some background info: I've been developing a game-server api in Lumen for a very simple game I'm working on. I've been having great success so far, if I may so so myself, for a total beginner with Laravel. So far I have many routes for all my needs successfully running. I've been putting off one of the more difficult tasks off until the end and that's where I'm at now.

My game will update and evaluate a bunch of values every few hours in a custom php artisan command I made. At the moment I've just made it a temporary function in one of my controllers so I can easily return some data to help debug my function. Once it's operational I'll move it back into the artisan command.

My desired function: This function needs to search through all of the locations(represented by an ID) and find any location with a unit. Preferably it would skip any locations without units. It then needs to check the unit's castle ID and compare it to any other units to see if they have different castle ID's. If they're different then that means the units have different owners and they should engage in combat.

I have a relationship for castles and guilds and I'd also like to be able to check for the units guild relationship through a shared relationship of castle(I think?) so that ultimately my function will not only check to see if they're different owners but also if they're in different guilds. Units that are owned by different user's(castle ID) but in the same guild should not engage in combat.

I also need to check to see which two(and only 2!) forces(guilds or individual users) have the largest amount of units at any given location because those are the only two that will engage in combat, any lesser forces will flee the battle.

My question: I'm hoping to just get some guidance on where to begin with this. I'm really confused as to how I can compare each unit's values to each other. I'm also very confused as to how I can refer to a unit's guild relationship through the castle, I think it's possible off the tutorials and videos I've seen but I don't know where to begin to make it happen.

snippets of my Models:

class Location extends Model
{
    public function units()
    {
        return $this->hasMany('App\Unit');
    }

    public function castle()
    {
        return $this->hasOne('App\Castle');
    }
}
class Unit extends Model
{
    public function castle()
    {
        return $this->belongsTo('App\Castle');
    }

    public function location()
    {
        return $this->belongsTo('App\Location');
    }
}
class Castle extends Model
{
    public function units()
    {
        return $this->hasMany('App\Unit');
    }

    public function guild()
    {
        return $this->hasOne('App\Guild');
    }

    public function location()
    {
        return $this->belongsTo('App\Location');
    }
}
class Guild extends Model
{
    public function casle()
    {
        return $this->belongsTo('App\Castle');
    }
}

This is my test query just to see all my test data:

public function testCombat(){
    $loc_with_units = Location::with('units')->get();

which results in:

    {
        "id": 1,
        "X": 1,
        "Y": 1,
        "units": [
            {
                "id": 2,
                "castle_id": 1,
                "location_id": 1,
                "squad_id": null,
                "unit_type_id": 1,
                "current_health": 100,
                "tick_created": 2,
                "created_at": "2018-01-03 09:18:46",
                "updated_at": "2018-01-03 09:18:46"
            },
            {
                "id": 3,
                "castle_id": 1,
                "location_id": 1,
                "squad_id": null,
                "unit_type_id": 1,
                "current_health": 100,
                "tick_created": 2,
                "created_at": "2018-01-03 09:18:46",
                "updated_at": "2018-01-03 09:18:46"
            },
        ]
    },
    {
        "id": 2,
        "X": 1,
        "Y": 2,
        "units": [
            {
                "id": 4,
                "castle_id": 2,
                "location_id": 2,
                "squad_id": null,
                "unit_type_id": 1,
                "current_health": 100,
                "tick_created": 2,
                "created_at": "2018-01-03 09:18:46",
                "updated_at": "2018-01-03 09:18:46"
            },
            {
                "id": 5,
                "castle_id": 2,
                "location_id": 2,
                "squad_id": null,
                "unit_type_id": 1,
                "current_health": 100,
                "tick_created": 2,
                "created_at": "2018-01-03 09:18:46",
                "updated_at": "2018-01-03 09:18:46"
            },
            {
                "id": 1,
                "castle_id": 1,
                "location_id": 2,
                "squad_id": 1,
                "unit_type_id": 1,
                "current_health": 100,
                "tick_created": 2,
                "created_at": "2018-01-03 09:18:46",
                "updated_at": "2018-01-03 09:20:02"
            }
        ]
    },
    {
        "id": 3,
        "X": 1,
        "Y": 3,
        "units": []
    },
    {
        "id": 4,
        "X": 2,
        "Y": 1,
        "units": []
    },
    {
        "id": 5,
        "X": 2,
        "Y": 2,
        "units": []
    },

...etc...

]

THANK YOU FOR YOUR TIME!

Edit Your Profile
Update

Want to change your profile photo? We pull from gravatar.com.