Expect the following field inside a database:
$table->unsignedBigInteger('experience_points')->nullable(false)->default(0);
To increment / decrement the value the following functions are used:
$user->increment('experience_points', +50); //+50 can be $someExperienceAmountToBeIncremented
$user->increment('experience_points', -50); //-50 can be $someExperienceAmountToBeIncremented
Let's say a user triggers after creating a his user account the -50 increment function, this will throw an exception inside laravel:
Illuminate\Database\QueryException SQLSTATE[22003]: Numeric value out of range: 1690 BIGINT UNSIGNED value is out of range in '`database`.`users`.`experience_points`
is there any chance to still use increment() or am I required to perform the check every time before like:
if($user->experience_points - $someExperienceAmountToBeIncremented) < 0
$user->update(['experience_points', 0]);
Somehow using this "check" before feels bad to use, but is there any other way to handle this maybe a bit more elegant?
Because of observers "watching" on the experience_points it's not possible for me to switch to rawSQL.
The limit is at DB engine level.
I don't like very much increment() and decrement() for this limitations.
One solution might be to try setting a trigger on the "before update" event on the affected table and that converts negative numbers to zero, but I don't recommend that.
An acceptable alternative could be this:
$user->update(['experience_points', max ($user->experience_points + $someExperienceAmountToBeIncremented, 0)]);
The reason for a user not being able to do decrement when they create an account is that the limit is 0 (Cannot be a negative value) because the column type is unsignedBigInteger, to allow a user to have -50 points, you need to use a column type as follows:
$table->bigInteger('experience_points')->nullable(false)->default(0);
Or as @pippo has explained, you can check in your model before the query is run if the number is negative and return a 0 value, however to save on additional logic (processing time), if the changing of the column type will fit your problem I recommend in using that approach.
@pippo
Why not use both methods? Using the max() approach inside an observer watching the watching event?
I've already included increment() into several sections of the project so this would keep the max() approach in one central point.
@t0berius If i'm not wrong, updating() is fired by increment() only from Laravel 8.x
I've used the following observer code:
public function updating(User $user){
if($user->isDirty('experience_points')){
$user->exp = max($user->experience_points, 0); //already used dd() to verify it's 0 even if - values being used
//increased logic
if($user->experience_points > $user->getOriginal('experience_points')){
/...
}
}
//decreased
elseif($user->experience_points < $user->getOriginal('experience_points')){
/...
}
}
}
}
I still get the exception SQLSTATE[22003]: Numeric value out of range: 1690 BIGINT UNSIGNED value is out of range in 'database.users.experience_points + -50' ( even if the dd() statement used to debug shows 0 as value?
Any idea where things go wrong?
@t0berius I don't know what you logic is inside here:
//increased logic
if($user->experience_points > $user->getOriginal('experience_points')){
/...
}
}
//decreased
elseif($user->experience_points < $user->getOriginal('experience_points')){
/...
}
}
But I would think that your value is being changed to be less than zero based on your logic, as SQL responses are explicit in that you would not get that response unless you are returning a value lower than zero or null. Maybe something is returning a null value in your logic?
EDIT: Did you mean to use $user->exp in the increased & decreased logic instead of the $user->experiance_points?
@t0berius How about adding extra logic to check if the value is 0, this would stop you from them allow decrease logic, you could use something like this:
public function updating(User $user){
if($user->isDirty('experiance_points')){
$user->exp = max($user->expedience_points, 0);
if ($user->exp === 0) {
// only allow increase logic
} elseif ($user->exp > 0 && $user->exp < 51 ) {
// maybe use this logic to only allow smaller decreases?
// I put this in as your original example says you are trying to reduce experiance by -50
// so it will fail if you allow this
} else {
// must be above 51, so allow both increases of 50 & decreases of 50
}
}
}
Even this code would throw the exception, my logic won't manipulate the field:
public function updating(User $user){
if($user->isDirty('experiance_points')){
//no negative values please
$user->experiance_points = max($user->experiance_points, 0);
}
}
You can test it yourself, by tinker, experiance_points was set to 0 before:
> User::where('id', 1)->first()->increment('experiance_points', -10);
runs in:
Illuminate\Database\QueryException SQLSTATE[22003]: Numeric value out of range: 1690 BIGINT UNSIGNED value is out of range in '`database`.`users`.`experiance_points` + -10'
@t0berius So, just so we understand. You are trying to minus -10 points off 0 & expecting that the max() helper will pick up on it & prevent the negative value from attemping to be updated in the model?
@camkem Yes, I'm looking for a way to prevent it to be negative inside the observer.
I think the issue is you are not taking in account what increment() does
$user = User::find(5);
$user->increment('experience_points', -50);
This is the query that gets build and executed
update
user
set
experience_points = experience_points + -50
updated_at = '2023-03-11 10:14:44'
where
id = 5
Unless you tweak that second parameter when you call increment(), you will keep facing this problem
I suggest you write your own method to perform this increment
// User.php model
public function incrementExperience(int $value)
{
$this->update(['experience_points' => max($this->experience_points + $value, 0 )]);
}
A pain to track down all the calls to increment() on the User model but I don't think that method is a good fit for this situation.
The other option is to write a BEFORE UPDATE trigger on the database for the user table and make that adjustment there.
@psrz Thank you for reply.
So the problem is the value experience_points cannot be changed from inside the updating function of the observer?
I'm a bit confused why my new value (0) won't be used as defined inside the observer.
You can change that value during the updating event, but that won't do any good when using increment() function.
This is the key of the problem, the increment() function does not use the actual current value when performing the update.
set experience_points = experience_points + -50
Also, I just a bit of testing and it turns out you can directly override the increment() method on the model (I thought all of these being traits or magic calls to who knows what that wouldn't work)
Still, you should do your own tests with this (I'm only considering this use case for experience_points). Maybe if $column is not experience_points you might just return the default.
public function increment($column, $amount = 1, array $extra = [])
{
$currentValue = $this->$column;
$amount = ( ($amount + $currentValue) < 0 )
? $currentValue * -1
: $amount;
return parent::increment($column, $amount, $extra);
}
This returns very strange behaviour, I've excluded it into a trait:
<?php
namespace App\Traits;
trait Increment
{
public function increment($column, $amount = 1, array $extra = [])
{
$currentValue = $this->$column;
$amount = ( ($amount + $currentValue) < 0 )
? $currentValue * -1
: $amount;
return parent::increment($column, $amount, $extra);
}
}
Starting with 0 as experience_points
User::where('name', 'test')->first()->increment('experience_points', +10);
=> value set to 20
User::where('name', 'test')->first()->increment('experience_points', +10);
=> value set to 30
User::where('name', 'test')->first()->increment('experience_points', +10);
=> value set to 50
User::where('name', 'test')->first()->increment('experience_points', +10);
=> value set to 60
Trait was just included into the model correctly:
use App\Traits\Increment;
...
use HasFactory, Increment;
I tried this on one of my models and it works fine. info() shows the expected values every step
Artisan::command('custom-increment', function() {
$recepcion = Recepcion::query()->find(1);
$recepcion->update(['nota_escrito' => 0]);
$recepcion->refresh();
$this->info("start: " . $recepcion->nota_escrito);
$this->warn('incrementing...');
for($i = 0; $i < 5;$i++) {
$recepcion->increment('nota_escrito', 10);
$recepcion->refresh();
$this->info("new value " . $recepcion->nota_escrito);
}
$this->warn("decrementing...");
for($i = 0; $i < 6;$i++) {
$recepcion->increment('nota_escrito', -10);
$recepcion->refresh();
$this->info("new value " . $recepcion->nota_escrito);
}
});
start: 0
incrementing...
new value 10
new value 20
new value 30
new value 40
new value 50
decrementing...
new value 40
new value 30
new value 20
new value 10
new value 0
new value 0
Try the trait on another eloquent model without any observers or anything like it.
BTW, is name unique on your users table ?
Was my fault, was triggered by prometheus running on the same endpoint I used for testing. Shame on me.
Please or to participate in this conversation.