Ad

Laravel - WhereRaw CAST Query Not Working Within While Loop

- 1 answer

I have an SQL query where I am casting a column as SIGNED to compare it to an integer. My table "planets" looks like the following

id | name | galaxy | region
1  | S1   | 00     | 01
2  | S2   | 00     | 01
3  | S3   | 00     | 02
4  | S4   | 01     | 00
5  | S5   | 00     | 00

There are hundreds of entries belonging to different galaxies (0-99) and regions (0-99)

The galaxy and region columns are set as strings to store them with a leading 0. Maybe not the best way to do it but that's how I've done it.

I've got a variable as below

$planet = Planet::where('galaxy', '00')->get();

Then I have the below loop to bring out planets that belong in 00 galaxy and a defined region, cycling through every possible region (0 - 99) and printing the count.

$count = 0;
while($count < 100){

   echo $planet->whereRaw('CAST(region as SIGNED) = '.$count)->count();

   $count++;
}

Thing is, the first interation (where $count equals 0) returns results, but any subsequent loop doesn't return anything. If I set $count to a different value, the same story - first loop works but none of the following loops.

Any ideas as to why this might be the case would be very much appreciated. I have echo'd $count in the loop to double check it definitely prints numbers between 0 - 99 in sequence, and it does, so not sure why it's not returning results in the SQL query beyond the first one.

EDIT: What I'm trying to achieve is pictured below:

enter image description here

Basically there are thousands of planets, each is in a galaxy and a region. Galaxies number 00-99 as do regions.

I will go to a url of /map/00 telling me which galaxy I am looking at, then there will be the above grid of 10 x 10, each section containing the count of planets in that region of that galaxy. Each grid is a div that's made within the while loop.

So the actual while loop looks like this:

$count = 0;
    while($count < 100){
       echo "<div class="inline-flex w-1/10">
       echo $planet->whereRaw('CAST(region as SIGNED) = '.$count)->count();
       echo "</div>
       $count++;
    }

The count is in place so we know which section of the grid we are in and therefore filters the $astros->whereRaw query to focus on that region. Because the actual table is a string rather than an integer, I need to cast is as a SIGNED field for the where query to work. So the SQL query I am actually after is below:

SELECT * FROM planets WHERE galaxy = 00 AND CAST(region as SIGNED) = $count
Ad

Answer

Issue #1 : Collection

You can't use whereRaw(), because $planet is collection, since used ->get().

$planet = Planet::where('galaxy', '00')->get(); // collection

Issue #2 : AND, AND, AND, ...

When you use $planet without ->get(), You will get many where combined.

$planet = Planet::where('galaxy', '00');
$count = 0;

while($count < 100){
   echo $planet->whereRaw('CAST(region as SIGNED) = '. $count)->count();

   $count++;
}

Result

select * from `planets` where (`galaxy` = ?) and CAST(region as SIGNED) = 0

select * from `planets` where (`galaxy` = ?) and CAST(region as SIGNED) = 0 and CAST(region as SIGNED) = 1

select * from `planets` where (`galaxy` = ?) and CAST(region as SIGNED) = 0 and CAST(region as SIGNED) = 1 and CAST(region as SIGNED) = 2

select * from `planets` where (`galaxy` = ?) and CAST(region as SIGNED) = 0 and CAST(region as SIGNED) = 1 and CAST(region as SIGNED) = 2 and CAST(region as SIGNED) = 3

select * from `planets` where (`galaxy` = ?) and CAST(region as SIGNED) = 0 and CAST(region as SIGNED) = 1 and CAST(region as SIGNED) = 2 and CAST(region as SIGNED) = 3 and CAST(region as SIGNED) = 4

select * from `planets` where (`galaxy` = ?) and CAST(region as SIGNED) = 0 and CAST(region as SIGNED) = 1 and CAST(region as SIGNED) = 2 and CAST(region as SIGNED) = 3 and CAST(region as SIGNED) = 4 and CAST(region as SIGNED) = 5

....

select * from `planets` where (`galaxy` = ?) and CAST(region as SIGNED) = 0 and CAST(region as SIGNED) = 1 and CAST(region as SIGNED) = 2 and CAST(region as SIGNED) = 3 and CAST(region as SIGNED) = 4 and CAST(region as SIGNED) = 5 and CAST(region as SIGNED) = 6 and CAST(region as SIGNED) = 7 and CAST(region as SIGNED) = 8 and CAST(region as SIGNED) = 9 and CAST(region as SIGNED) = 10 and CAST(region as SIGNED) = 11 and CAST(region as SIGNED) = 12 and CAST(region as SIGNED) = 13 and CAST(region as SIGNED) = 14 and CAST(region as SIGNED) = 15 and CAST(region as SIGNED) = 16 and CAST(region as SIGNED) = 17 and CAST(region as SIGNED) = 18 and CAST(region as SIGNED) = 19 and CAST(region as SIGNED) = 20 and CAST(region as SIGNED) = 21 and CAST(region as SIGNED) = 22 and CAST(region as SIGNED) = 23 and CAST(region as SIGNED) = 24 and CAST(region as SIGNED) = 25 and CAST(region as SIGNED) = 26 and CAST(region as SIGNED) = 27 and CAST(region as SIGNED) = 28 and CAST(region as SIGNED) = 29 and CAST(region as SIGNED) = 30 and CAST(region as SIGNED) = 31 and CAST(region as SIGNED) = 32 and CAST(region as SIGNED) = 33 and CAST(region as SIGNED) = 34 and CAST(region as SIGNED) = 35 and CAST(region as SIGNED) = 36 and CAST(region as SIGNED) = 37 and CAST(region as SIGNED) = 38 and CAST(region as SIGNED) = 39 and CAST(region as SIGNED) = 40 and CAST(region as SIGNED) = 41 and CAST(region as SIGNED) = 42 and CAST(region as SIGNED) = 43 and CAST(region as SIGNED) = 44 and CAST(region as SIGNED) = 45 and CAST(region as SIGNED) = 46 and CAST(region as SIGNED) = 47 and CAST(region as SIGNED) = 48 and CAST(region as SIGNED) = 49 and CAST(region as SIGNED) = 50 and CAST(region as SIGNED) = 51 and CAST(region as SIGNED) = 52 and CAST(region as SIGNED) = 53 and CAST(region as SIGNED) = 54 and CAST(region as SIGNED) = 55 and CAST(region as SIGNED) = 56 and CAST(region as SIGNED) = 57 and CAST(region as SIGNED) = 58 and CAST(region as SIGNED) = 59 and CAST(region as SIGNED) = 60 and CAST(region as SIGNED) = 61 and CAST(region as SIGNED) = 62 and CAST(region as SIGNED) = 63 and CAST(region as SIGNED) = 64 and CAST(region as SIGNED) = 65 and CAST(region as SIGNED) = 66 and CAST(region as SIGNED) = 67 and CAST(region as SIGNED) = 68 and CAST(region as SIGNED) = 69 and CAST(region as SIGNED) = 70 and CAST(region as SIGNED) = 71 and CAST(region as SIGNED) = 72 and CAST(region as SIGNED) = 73 and CAST(region as SIGNED) = 74 and CAST(region as SIGNED) = 75 and CAST(region as SIGNED) = 76 and CAST(region as SIGNED) = 77 and CAST(region as SIGNED) = 78 and CAST(region as SIGNED) = 79 and CAST(region as SIGNED) = 80 and CAST(region as SIGNED) = 81 and CAST(region as SIGNED) = 82 and CAST(region as SIGNED) = 83 and CAST(region as SIGNED) = 84 and CAST(region as SIGNED) = 85 and CAST(region as SIGNED) = 86 and CAST(region as SIGNED) = 87 and CAST(region as SIGNED) = 88 and CAST(region as SIGNED) = 89 and CAST(region as SIGNED) = 90 and CAST(region as SIGNED) = 91 and CAST(region as SIGNED) = 92 and CAST(region as SIGNED) = 93 and CAST(region as SIGNED) = 94 and CAST(region as SIGNED) = 95 and CAST(region as SIGNED) = 96 and CAST(region as SIGNED) = 97 and CAST(region as SIGNED) = 98 and CAST(region as SIGNED) = 99

Solution #1

$count = 0;

while ($count < 100) {
    $total = Planet::where('galaxy', '00')
        ->whereRaw('CAST(region as SIGNED) = ' . $count)
        ->count();

    echo $total;

    $count++;
}

Result

+--------+-------+
| Region | Count |
+--------+-------+
| 00     | 1     |
| 01     | 2     |
| 02     | 1     |
| 03     | 0     |
| 04     | 0     |

....

| 99     | 0     |
+--------+-------+

Solution #1 (Best way, single query)

$planets = Planet::select('region', \DB::raw('count(*) as total'))
    ->where('galaxy', '00')
    ->groupBy('region')
    ->pluck('total', 'region');

Result

# region => total
array:3 [
    "00" => 1
    "01" => 2
    "02" => 1
]
Ad
source: stackoverflow.com
Ad