John Davidson

php - Best way of deleting multiple records linked in different tables in Laravel query builder

0 comments
Message:


I've got a function in my Laravel 8 API that deletes a user's account, it also deletes any of data in any other table where their user_id matches the one of the account that's being deleted. One thing has become apparant quite quickly with this, is how can this be scaled, and whether there's a better, potentially faster and more reliable way to delete linked tables data.


On the other hand, am I doing it the way that would be fine for medium sizes web applications, my current code is:


/**
* Delete account
*
* @return Response
*/
public function deleteAccount(Request $request)
{

// attempt to delete account
try {

$id = Auth::user();
$user = User::findOrFail($id['id']);
$user->delete();

$items1 = MyFirstModel::where('user_id', $id['id'])->get();
if (count($items1) > 0) {
foreach ($items1 as $key => $item) {
$item->delete();
}
}

$items2 = MySecondModel::where('user_id', $id['id'])->get();
if (count($items2) > 0) {
foreach ($items2 as $key => $item) {
$item->delete();
}
}

// ... other models here.

// everything went okay!
return response()->json(['success' => true, 'message' => 'Your account has been deleted'], 200);

} catch (Exception $e) {

// catch the error
return response()->json(['success' => false, 'message' => 'We was unable to delete your account at this time'], 422);

}

}


My Answer:
>delete();_

// delete linked data in other tables
DB::table('table1')->where('user_id', $id['id'])->delete();
DB::table('table2')->where('user_id', $id['id'])->delete();
// add more tables as needed

return response()->json(['message' => 'Account deleted successfully'], 200);
} catch (\Exception $e) {
return response()->json(['message' => 'Failed to delete account'], 500);
}
}

This approach is fine for small to medium-sized web applications. However, as you mentioned, it may not be the most scalable or efficient way to delete linked data in multiple tables.

One potential improvement could be to use database relationships and cascading deletes. By defining relationships between your User model and other models, you can set up cascading deletes so that when a user is deleted, all related records in other tables are automatically deleted.

Another approach could be to use transactions to ensure data consistency. You can wrap the deletion operations in a transaction so that either all deletions succeed or none of them do.

Here's an example using database relationships and cascading deletes:

In your User model, define relationships to other models:

php
class User extends Model
{
public function table1()
{
return $this->hasMany(Table1::class);
}

public function table2()
{
return $this->hasMany(Table2::class);
}
// add more relationships as needed
}


Then, in your deleteAccount method, you can simply delete the user and Laravel will automatically delete related records in other tables:

php
public function deleteAccount(Request $request)
{
try {
$id = Auth::user();
$user = User::findOrFail($id['id']);
$user->delete();

return response()->json(['message' => 'Account deleted successfully'], 200);
} catch (\Exception $e) {
return response()->json(['message' => 'Failed to delete account'], 500);
}
}


This approach leverages Laravel's built-in features for managing relationships and cascading deletes, which can make your code more maintainable and scalable.

Rate this post

3 of 5 based on 5486 votes

Comments




© 2024 Hayatsk.info - Personal Blogs Platform. All Rights Reserved.
Create blog  |  Privacy Policy  |  Terms & Conditions  |  Contact Us