Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
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.
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?
still a little raw..
$units = Unit::select('location_id', DB::raw('COUNT(DISTINCT(`castle_id`)) as `count`'))
->groupBy('location_id')
->orderBy('count','desc')
->get();
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)
Is it possible that instead of counting the results I could just select them?
I used backticks in the query to distinguish from single quotes. These might not work with your DB?
DB::raw("COUNT(DISTINCT('castle_id')) as 'count'")
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...
$units = Unit::select('location_id', DB::raw("COUNT(DISTINCT('castle_id')) as 'count'"))
->groupBy('location_id')
->orderBy('count','desc')
->get();
Just quote the raw string and remove all the other quotes
DB::raw('COUNT(DISTINCT(castle_id)) as count')
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
}
]
Is there a way to incorporate this logic into my original query to constrain it so that it's only selecting the entries from units where count > 1?
Not sure if a simple ->where('count','>',1) added to the query builder will do it
The alternative is to filter the resulting collection
I'm not trying to get the count itself, but rather the logic that is determining when rows have identical location_id but the castle_id are not identical.
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?
Here's a hypothetical if it helps:
location_id: 1
location_id: 3
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 rows that does, then go on to check the remaining rows.
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!
Please or to participate in this conversation.