Adding unique constraints to a query

Published 3 months ago by deja

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.

Best Answer (As Selected By deja)
Snapey

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

tykus
tykus
3 months ago (542,850 XP)

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

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

deja

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.

wgmv
wgmv
3 months ago (14,565 XP)

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

deja

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.

Snapey
Snapey
3 months ago (924,105 XP)

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


deja

@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)
Snapey
Snapey
3 months ago (924,105 XP)

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

deja

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)
Snapey
Snapey
3 months ago (924,105 XP)

show your new code please

deja
  $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; 
Snapey
Snapey
3 months ago (924,105 XP)

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

are both columns integer column types?

deja

@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();
        });
Snapey
Snapey
3 months ago (924,105 XP)

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

deja

@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!

Please sign in or create an account to participate in this conversation.