Adding unique constraints to a query

Am pretty sure I do not correctly understand what you mean by:

$units that have identical location_id

(are you specifying the $location_id?). We might need to come back to the where part:,

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

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 @tykus! I've been desperate for some guidance with this and it's weighing heavily on my mind :D

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.

not sure if I grasped the problem but I would suggest a groupBy over location_id and castle_id: groupBy('location_id', 'castle_id');

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": 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 don't really understand why it's numerating castle_id like that...

It seems a bit closer to what I want, it's at least more organized. This is just some random test data that I'm using.

With this result I would only be interested in location_id 2 and 4, I want to ignore 1, 3, and 5.

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.

So why is is not just

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

@Snapey I'm getting an error:

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)

I edited it, it should be <> for inequality in sql

Still getting an error:

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)

show your new code please

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

I had to assume that the two columns in the where statement are both on the units table?

are both columns integer column types?

@Snapey this is my migration for the units table

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

Sorry I've got to go now. Issue is that the query builder is expecting one field in the where to be a column, and the other a value.

Try this instead

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

@Snapey no problem, thanks so much for your help! I'm also going to head out for now.

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

Your new suggestion seemed to work, mostly. It queried the correct locations 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 tomorrow 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!

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