Level 102
First of start by comparing the queries it is generating. The working version without the fix, the broken one and the broken one with the fix
You can use debugbar for this
Feb 5, 2023
17
Level 17
Inconsistent behaviour when using enums in where query
I'm working on a website for a simracing hotlapping competition. Participants can submit an unlimited amount of lap times for a specific round in a specific season, and admins can either approve or deny these lap times. The fastest approved lap time for each participant is shown on that round's leaderboard.
In case it's relevant, I'm using this package to generate Snowflake IDs instead of incremental IDs or GUIDs.
I'm using a pretty simple enum for the lap time status;
enum LapTimeStatus: int
{
case SUBMITTED = 0;
case APPROVED = 1;
case DENIED = 2;
}
In the LapTime model I cast this field to the enum, and I've of course added the relationship to the Round model;
class LapTime extends Model
{
use HasFactory, Snowflake;
protected $casts = [
'status' => LapTimeStatus::class,
];
public function round(): BelongsTo
{
return $this->belongsTo(Round::class);
}
}
In the Round model, I've set up the inverse of the relationship, as well as a method to fetch the lap times for the aforementioned leaderboard;
class Round extends Model
{
use HasFactory, Snowflake;
public function times(): HasMany
{
return $this->hasMany(LapTime::class);
}
public function timesForLeaderboard(): array
{
$times = $this->times()
->with('user')
->orderBy('lap_time')
->where('status', LapTimeStatus::APPROVED)
->get();
return $times->unique('user_id')->values()->toArray();
}
}
timesForLeaderboard gets all approved times for that round, orders them by laptime (laptimes are stored in milliseconds for easy sorting), and makes sure only one lap time for each user is selected.
However, the ->where('status', LapTimeStatus::APPROVED) line is causing issues. It works perfectly fine for the first season, however for whatever reason this line returns 0 models for each round, despite all of them having at least one approved lap time. I've been able to fix it by changing the line to ->where('status', (string) LapTimeStatus::APPROVED->value), but
- this seems like a band aid fix for a deeper issue, as it behaves correctly elsewhere and
- I'd have to make this change in a few other places, which I'd like to avoid.
The (string) cast is also required to make it work, just using ->value isn't enough.
Any idea what could be causing this inconsistent behaviour despite near-identical circumstances?
Level 17
@Sinnbeck I completely forgot about Debugbar until now, usually it's one of the first dependencies I install. Can't do it now, but I'll report back if I find anything interesting.
Level 102
@lolsokje ok cool. You can also trying being more explicit with the query
->where('status', '=', LapTimeStatus::APPROVED)
Level 17
@Sinnbeck The queries are identical, aside from the ID of the round the times are being fetched for;
Working:
select * from `lap_times` where `lap_times`.`round_id` = '3779454572691456' and `lap_times`.`round_id` is not null and `status` = 1 order by `lap_time` asc;
Not working:
select * from `lap_times` where `lap_times`.`round_id` = '102547902995365888' and `lap_times`.`round_id` is not null and `status` = 1 order by `lap_time` asc;
When I run the query in TablePlus, both return the expected results, so something must be going on within Eloquent.
Both
->where('status', '=', LapTimeStatus::APPROVED->value)
and
->whereRaw('`status` = ' . LapTimeStatus::APPROVED->value)
produce the exact same query as well, but the former doesn't fix the issue while the latter does.
Level 27
@lolsokje The only two things I can think of that raise tiny red flags (not really red flags, but potential ones) is having a column named status (a MySQL keyword, so using it without escaping could in theory cause issues in certain contexts) and the type of the round_id column (the values you’re using are larger than what an unsigned int column can hold, but smaller than what a bigint can hold, even a signed one).
It’s also weird that the queries you give specify the foreign key twice (where round_id = ? and round_id is not null) – a regular Eloquent relation shouldn’t do that.
Level 102
@kokoshneta I strangely just saw that happen here, where it was lazy loading a relationship https://laracasts.com/discuss/channels/eloquent/extra-query-when-lazy-loading-relationship
@lolsokje Could you somehow be doing that?
Level 17
@kokoshneta Columns names are escaped by Laravel by default I believe, so that shouldn't be an issue. All ID columns are unsigned bigints actually, not unsigned integers as I alluded to earlier.
It’s also weird that the queries you give specify the foreign key twice (where round_id = ? and round_id is not null) – a regular Eloquent relation shouldn’t do that.
I think I've seen that every time I've looked at Debugbar's "query" in any project, and I'm pretty sure I've defined my relationships properly. I don't think there's anything I can change to the relationships defined in the Round and LapTime models, is there?
Level 17
@Sinnbeck As far as I can see, only one query is being run on the lap_times column, if that's what you meant.
Level 27
@lolsokje The double reference to the foreign key shouldn’t really make a difference (I mean, if round_id has the value X, then it’s obviously not null), but it’s still odd. None of my queries in Debugbar have that, at least.
I think if there’s anything you were to do to cause that, it would have to be to override the relationship methods (hasOne() etc.) in your base model or the appropriate traits, which doesn’t seem like something you could do by accident.
Level 17
@kokoshneta Could it maybe be the DB driver resulting in different queries? I've always used MySQL so far.
Level 27
@lolsokje Could be. Mine is primarily SQL Server. But I don’t see any reason why the MySQL driver should result in that kind of thing.
Level 10
Myabe not related to your problem, but what data type is the status field on the lap_time table ? The Enum is an integer but you are casting it to string in your fix. Is a varchar on the database ?
EDIT: If the field on the table is a varchar, try changing the enum to string, like this:
enum LapTimeStatus: string
{
case SUBMITTED = '0';
case APPROVED = '1';
case DENIED = '2';
}
Try running the queries again.
Level 17
@psrz it's an unsigned integer in the database, which makes it even weirder the fix is casting the value to a string.
Level 10
My vm with mysql running is busted so I can't test it now
Kinda shooting in the dark , but can you use differnet values for the status ? Maybe somthing like this:
enum LapTimeStatus: int
{
case SUBMITTED = 1000;
case APPROVED = 2000;
case DENIED = 3000;
}
For the database, something simple like this:
update lap_times set status = 1000 where status = 0;
update lap_times set status = 2000 where status = 1;
update lap_times set status = 3000 where status = 2;
I'm not liking much the 0 and the 1 as values. A value of 0 is evalutated as empty for example in php. Maybe that's why casting to string works
I haven't dived that deep into the core of Eloquent so I don't know what could be happening.
Level 17
@psrz Just tried this, but didn't help sadly. As for what you described, 0 being evaluated as empty, shouldn't matter since it's the default status for new lap times anyway.
Level 10
Well, I don't know what else to try.
I made a small table and to try it out with your Enum and it works just fine. Both the generated query and the results are the expected ones
This is what I've used to test it:
// migration
public function up()
{
Schema::create('lap_times', function (Blueprint $table) {
$table->id();
$table->bigInteger('round_id')->nullable();
$table->unsignedBigInteger('status')->default(0);
$table->bigInteger('lap_time')->default(0);
$table->timestamps();
});
}
// factory
public function definition()
{
return [
'round_id' => $this->faker->optional()->randomNumber(8),
'status' => $this->faker->randomElement([
LapTimeStatus::SUBMITTED,
LapTimeStatus::SUBMITTED,
LapTimeStatus::SUBMITTED,
LapTimeStatus::APPROVED,
LapTimeStatus::APPROVED,
LapTimeStatus::DENIED,
]),
'lap_time' => rand(30, 120) * 1000,
];
}
// Eloquent queries
//LapTime::factory(300)->create();
$submitted = LapTime::query()
->where('status', LapTimeStatus::SUBMITTED)
->count();
$approved = LapTime::query()
->where('status', LapTimeStatus::APPROVED)
->count();
$denied = LapTime::query()
->where('status', LapTimeStatus::DENIED)
->count();
dd($submitted, $approved, $denied);
The generated sql queries are these ones:
select
count(*) as aggregate
from
`lap_times`
where
`status` = 0;
select
count(*) as aggregate
from
`lap_times`
where
`status` = 1;
select
count(*) as aggregate
from
`lap_times`
where
`status` = 2
The results of those counts are fine.
I don't know what else it could be. ->where('status', LapTimeStatus::APPROVED) works fine on a eloquent query.
I'm out of ideas.
Level 17
@psrz I'm stumped to be honest, the resulting queries for both working and non-working pages are exactly the same, and running them in TablePlus results in exactly the expected result in both cases. The issue only crops up when Eloquent is involved.
Please or to participate in this conversation.