Check usage before deleting an user(Model)

I have a database, with more than 20 tables and each table has the created_by, modified_by, and deleted_by columns. I have to prevent users from deleting the entries from the Users table when it is used in any of the tables in any of the above columns. Currently, I'm doing in a very long way:

public function operators()
{
    return ($this->hasMany(Operator::class, 'created_by', 'id')->count() +
        $this->hasMany(Operator::class, 'modified_by', 'id')->count() +
        $this->hasMany(Operator::class, 'deleted_by', 'id')->count());
}
public function sources()
{
    return ($this->hasMany(Source::class, 'created_by', 'id')->count() +
        $this->hasMany(Source::class, 'modified_by', 'id')->count() +
        $this->hasMany(Source::class, 'deleted_by', 'id')->count());
}

public function unit_types()
{
    return ($this->hasMany(UnitType::class, 'created_by', 'id')->count() +
        $this->hasMany(UnitType::class, 'modified_by', 'id')->count() +
        $this->hasMany(UnitType::class, 'deleted_by', 'id')->count());
}
public function in_use()
{
    return  $this->operators() + $this->sources() + $this->unit_types() + $this->users();

}

and checking the in_use() value greater than 0 and informing users, that the action is prevented. I hope there will be a simple way to do that, rather than repeating the same for more than 20 tables.

LaryAI

2 years ago

Best Answer

Level 58

One way to simplify the code and avoid repeating the same logic for each table is by using a dynamic approach. You can create a method that accepts the table name as a parameter and checks if the user is referenced in any of the specified columns. Here's an example implementation:

public function isUserReferenced($tableName)
{
    $columns = ['created_by', 'modified_by', 'deleted_by'];

    foreach ($columns as $column) {
        $count = $this->hasMany($tableName, $column, 'id')->count();
        if ($count > 0) {
            return true;
        }
    }

    return false;
}

public function in_use()
{
    $tables = ['Operator', 'Source', 'UnitType'];

    foreach ($tables as $table) {
        if ($this->isUserReferenced($table)) {
            return true;
        }
    }

    return false;
}

In this solution, the isUserReferenced method accepts the table name as a parameter and iterates over the specified columns to check if the user is referenced in any of them. The in_use method then iterates over the tables array and calls isUserReferenced for each table. If any of the tables reference the user, it returns true; otherwise, it returns false.

This way, you can easily add or remove tables from the $tables array without having to modify the code for each table individually.

1 like

tykus

2 years ago

Level 104

Your database already comes with referential integrity built-in. You can prevent deleting records in the users table if you have established foreign key constraints on the other tables.

Then you can try deleting the User record, and catch a Illuminate\Database\QueryException which is thrown whenever the integrity constraint is violated.

2 likes

Snapey

2 years ago

Level 122

Instead of deleting the user, obfuscate the personal details. EG, change their names to 'Former User' and email to some random value (eg GDdtwwsk2 @domain.com).

Then the integrity is preserved but it is no longer an account that someone can login with. You can also set a flag on the model so that the 'deleted' user is not visible.

Note, I'm not talking about soft deletes, which would hide the user from all places where you try and show the owner, etc. You would then end up fixing a load of views to stop it failing when no user is present.

1 like

Please or to participate in this conversation.