How to periodically update a database table's column for a million records in Laravel?-CodePudding

In my users' table, I will require to revert the 'daily' column back to the default value '1' on the dot at 12AM.

    Schema::create('users', function (Blueprint $table) {
        $table->id();
        $table->string('name', 50);
        $table->string('email')->unique();
        $table->unsignedTinyInteger('daily')->default('1');
        $table->timestamp('email_verified_at')->nullable();
        $table->string('password');
        $table->rememberToken();
        $table->timestamps();
    });

Is there a way where I can revert the value back to default value 1 or update the value to 1 for a million records with no time delay(meaning all the user's 'daily' column will be updated at the same time) at 12AM daily?

Note: The 'daily' column is to determine if user has 'use up' their daily click. User will have 1 chance to click on a button(sort of like a reward box) daily. Once they have clicked on the button, the daily will be updated to '0' and they will not be able to click the button again until the next day.

CodePudding user response：

You need to do the update within a transaction with serialization level of serializable to ensure it's done at once and no other transaction interferes with the update. As for the timing, I do not know how you can get the update done exactly at 00:00. It will either happen before or after midnight no matter what you do. The other solution is to have a time stamp for each row and use a trigger upon update to check if the timestamp is before midnight and if the time is after midnight set the column you want to one on that particular row. This should also be done in a transaction.

CodePudding user response：

Instead using loop for it.

You can use eloquent like this :

 Model::whereIn('id', $request->'name column')->update(['name column' => 1]);
 Model::whereNotIn('id', $request->'name column')->update(['name column' => 0]);