Compare two tables to get all unlinked entries

I have the problem on RolandRadio, that a lot of people try to modify the charts and register to vote. All users not verified by Mail are deleted after 24 hours to avoid fake users but some of them already mass-voted on titles.

So i needed to find out if there are ratings by users already non existent in the users table. I came up with this mySQL-Solution:

select * 
from ratings A
Where Not exists (select 1 from users B Where A.model_id = B.id)

Or without the aliasing:

select * 
from ratings
WHERE NOT exists (select 1 from users Where ratings.model_id = users.id)

So i select all from the table ratings and get all those entries where the model_id-column of the ratings-table is not in the id of the users-table.

This brought up 1600 ratings of already deleted users. i hate it that people are so manipulative on the charts. Now i need to do a cron-tab-command to delete ratings of deleted users every day. Also i included

     if (Auth::user()->verified != 1) {
            session()->flash('message', 'You must verify your email first!');
        } 

into the rating function to avoid users not verified to rate on titles.

1071 Specified key was too long;

If you use an older Version of mySQL or MariaDB Laravel throws an error on the first migration on your database. Thats cause the defaultStringLentth needs to be set to a maximum length of 191 characters.

    public function boot()
{
Schema::defaultStringLength(191);
}
}

Add this to your AppServiceProvider.php File and rerun your migration

Dont forget to add

use Illuminate\Support\Facades\Schema;

Adding a command to laravel deleting all unwanted users :-)

As spammer start flooding my users table with fake-accounts i startet to develop a command for laravel to run in a cron job for deleting all users which didnt verified their email within the last 24 hours.

A nice how-to do a laravel console command for cron can be found at: https://tutsforweb.com/how-to-set-up-task-scheduling-cron-job-in-laravel/

This is my routine to select all users which haven’t confirmed their email and their register date is older than 24 hours.

    public function handle()
    {
         $results = User::whereNull('email_verified_at')->get();
        $results = $results
            ->where('created_at', '<', Carbon::now()->subDays(1)->toDateTimeString());

        foreach ($results as $result)
        {
            $result->delete();
        }

    }

Added the following command to the crontab by crontab -e on the webserver to execute the command:

cd /var/www/vhosts/rolandradio.net/rolandradio2020 && /opt/plesk/php/7.4/bin/php artisan schedule:run >> /dev/null 2>&1

Set Laravel-Project to “down” during updates

Everytime i start an update of the whole project it takes hours to upload all the new updated files to my web server.

To avoid a massive downtime for the user without informing them about the status and displaying any errors i just touch a file within the project:

project->storage->framework

there i add a file called “down” and the site goes into “down-mode” – atfer i finished my work i rename the file to “down-” to bring up the site agein. I configured the Error 503 page to display a nicer version of the error.

Error Class ‘Illuminate\Database\Grammar’ not found

Today i updated RolandRadio.net because i had some new packages i wanted to use. On my local Laravel instalation everything worked fine but after uploading the new changed files i got an error:

Error Class ‘Illuminate\Database\Grammar’ not found

I didn’t figured out how to fix this and suggested that it has soething to do with updates of laravel that occured during my development and so the local laravel version and the version on my webserver where different.

After uploading all the files again completely instead of only the different ones the website worked again. Maybe my FTP-Client has messed up some files. As the Upload of those files takes about 2,5 hours i should think about using GIT to avoid those long downtime and commit my changes more often to the GIT repository then.

How to disable the Cache in Laravel

Sometimes its necessary to disable the caching in Laravel. To do so a new cache-driver “none” must be added to the cache-configuration-file in config/cache.php

'stores' => [
    //...
    'none' => [
        'driver' => 'null',
    ],
  ],

After that you have to set this cache-driver in your .env-File and you Cache is disabled

Edit .env and change the line
CACHE_DRIVER=file
to
CACHE_DRIVER=none

Clearing some Caches in Laravel

Clear Application Cache:

php artisan cache:clear

Clear Route Cache

Throws an error if CACHE_DRIVER=none

php artisan route:clear

Clear Configuration Cache

Throws an error if CACHE_DRIVER=none

php artisan config:clear 

 Clear Compiled Views Cache

Throws an error if CACHE_DRIVER=none

php artisan view:clear