I have a query with a nested select that uses the result of the first select. I have the query working perfectly as I tested using Sequel Pro's query checker. It returns my desired results. When I pass this query over to Laravel and run a test on it using a simple echo
statement, it outputs NULL
.
Here is my PHP code and query:
$crons = DB::select('
SELECT @quiet_crons:=ROUND(SUM(TIME_TO_SEC(TIMEDIFF(`end_time`, `start_time`))/60/15))
AS quiet_crons,
FLOOR(SUM(TIME_TO_SEC(TIMEDIFF(TIME('24:00'), TIME(NOW())))/60/15-@quiet_crons))
AS remaining_crons
FROM `quiet_periods`
WHERE `day` = DAYNAME(NOW())
');
And this is the output from the console:
array(1) {
[0]=>
object(stdClass)#689 (2) {
["quiet_crons"]=>
string(2) "24"
["remaining_crons"]=>
NULL
}
}
The remaining_crons
result should never be NULL
.
Please Note: I saw an article on here that talked about user-defined MySQL variables in Laravel and I tried the solution yet it did not solve my problem. This is the link I am referring to: User-defined MySQL variables in Laravel 3?
MySQL user defined variables are a bit tricky. From the manual;
As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement. For example, to increment a variable, this is okay:
SET @a = @a + 1;
For other statements, such as SELECT, you might get the results you expect, but this is not guaranteed. In the following statement, you might think that MySQL will evaluate @a first and then do an assignment second:
...
You can rewrite the statement without the variable though;
SELECT quiet_crons,
FLOOR(SUM(TIME_TO_SEC(TIMEDIFF(TIME('24:00'), TIME(NOW())))/60/15-quiet_crons))
AS remaining_crons
FROM (
SELECT ROUND(SUM(TIME_TO_SEC(TIMEDIFF(`end_time`, `start_time`))/60/15))
AS quiet_crons
FROM `quiet_periods`
WHERE `day` = DAYNAME(NOW())
) a;
Okay, it is a bit weird but as soon as I SET
(initialise) the variable first in a separate query, the resulting query works fine in Laravel.
Here is my code that I added above the previous code:
$crons = DB::select(DB::raw('
SET @quiet_crons = 0;
'));
Maybe someone could explain why this works the way it does in Laravel when MySQL just does it's thing and works anyway...