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.

Leave a Reply

Your email address will not be published. Required fields are marked *