Truncate table with foreign key

instead of truncate, you can use

Business::where('id', '>', 0)->delete();

This will delete all business AS LONG AS you don't have any photos with this business id

You can do something like this.

    public function updateAll(Request $request)
    {
        Photo::truncate();
    Business::where('id', '>', 0)->delete();

        foreach($request->businesses as $business){
            Business::create([
                'id' => $business['id'],
                'title' => $business['title'],
                'description' => $business['description'],
                'order' => $business['order'],
                'visiblity' => $business['visiblity'],
                'lang' => $business['lang'],
            ]);
        }
        return response('Update Successful.',200);
    }

BE CAREFUL. This will delete all photos

Hi @ziben69,

I would not use a foreign key (when I see this code) I should reset the business_id, do a update on Photo model before 'Business::truncate()'.

public function updateAll(Request $request)
    {
    Photo::update(['business_id' => 0]);
        Business::truncate();

@mushood you are also deleting all the photos (from the database) i don't know if this is the intention.

@MUSHOOD - Thanks, but I can't delete these photos. I need to change the business_id column in the Photo table. Maybe I can do something like that?

@MVD - "message": "Non-static method Illuminate\Database\Eloquent\Model::update() should not be called statically",

The main question here is @ziben69 do you want to keep your relationships between your photos and your buisnesses ?

if yes, you can't delete your buisness because there's no way you will be able to attach your photos back with the updated ( re-created in your case) buisnesses.

I would strongly advise to update when it's possible rather than delete everything and re-create if you have relationships.

If you don't care however,

you can simply add onDelete('set null'); on your foreign key. This will set your buisness_idon photos to null when you delete your buisness.

If you do that don't forget to add ->nullable()to your buisness_id in photos.

public function up()
    {
        Schema::table('photos', function(Blueprint $table){
            $table->integer('business_id')->unsigned()->change()->nullable();
           $table->foreign('business_id','photos_business_id_foreign')->references('id')->on('businesses')->onDelete('set null');
        });
    }

@ziben69 Agree with @stereoh

@STEREOH - I need to keep photos and change only their business_id

@ZIBEN69 - Based on what ? Is the buisness['id'] in your request the same Id you had before the deletion ?

If it is, so I guess it's okay to do so.

@STEREOH - The ID should remain unchanged. However, only the "order" column should be changed, but I don't know why it doesn't work.

Now I have something like that:

   public function updateAll(Request $request)
    {
        Photo::update(['business_id' => 0]);
        Business::truncate();

        foreach($request->businesses as $business){
            Business::create([
                'id' => $business['id'],
                'title' => $business['title'],
                'description' => $business['description'],
                'order' => $business['order'],
                'visiblity' => $business['visiblity'],
                'lang' => $business['lang'],

            ]);
        }
        return response('Update Successful.',200);
    }

and error:

"message": "Non-static method Illuminate\Database\Eloquent\Model::update() should not be called statically",

With the edit I made on my first answer, this should work properly. ( the ->onDelete('set null') ; part )

If it doesn't, describe me what is wrong.

And put back this code to where it was at the begining.

   public function updateAll(Request $request)
    {
        Business::truncate();

        foreach($request->businesses as $business){
            Business::create([
                'id' => $business['id'],
                'title' => $business['title'],
                'description' => $business['description'],
                'order' => $business['order'],
                'visiblity' => $business['visiblity'],
                'lang' => $business['lang'],
            ]);
        }
        return response('Update Successful.',200);
    }

@ziben69 if only the order column needs to be changed, just do a update instead of the truncate and create what @stereoh wrote.

@ZIBEN69 - Sorry didn't see you had an error earlier , just delete the Photo::update line it's not relevant if you have ->onDelete('set null')

By the way you need to php artisan migrate:fresh since you changed your migration

@MVD - I totally agree with you and I strongly advised that in my first post but if that's what @ziben69 wants.

@STEREOH -

I added in migration:

    public function up()
    {
        Schema::table('photos', function(Blueprint $table){
            $table->integer('business_id')->unsigned()->change()->nullable();
            $table->foreign('business_id','photos_business_id_foreign')->references('id')->on('businesses')->onDelete('set null');
        });
    }

and back with controller function updateAll to:

    public function updateAll(Request $request)
    {
        Business::truncate();

        foreach($request->businesses as $business){
            Business::create([
                'id' => $business['id'],
                'title' => $business['title'],
                'description' => $business['description'],
                'order' => $business['order'],
                'visiblity' => $business['visiblity'],
                'lang' => $business['lang'],
            ]);
        }
        return response('Update Successful.',200);
    }

php artisan migrate:fresh done

and again I have error:

"message": "SQLSTATE[42000]: Syntax error or access violation: 1701 Cannot truncate a table referenced in a foreign key constraint (`photography`.`photos`, CONSTRAINT `photos_business_id_foreign` FOREIGN KEY (`business_id`) REFERENCES `photography`.`businesses` (`id`)) (SQL: truncate `businesses`)",

Try something like this.

    DB::statement('SET FOREIGN_KEY_CHECKS=0;');
    DB::table('table1')->truncate();
    DB::table('table2')->truncate();
    DB::statement('SET FOREIGN_KEY_CHECKS=1;');

@GORAKH - It deleting my photos from DB

Indeed you can't truncate.

try

\App\Buisness::whereNotNull('id')->delete();

instead of

Business::truncate();

But tell me how we can update photo, now we are still deleting it

@stereoh - at the moment we are deleting photos after moving the row in the table. Now how do I load this picture from scratch?

update your migration like this and then refresh your migration

public function up()
    {
        Schema::create('photos', function(Blueprint $table) 
            {
    $table->increments('id');
    $table->string('filename');
    $table->integer('business_id')->unsigned();
    $table->foreign('business_id')->references('id')->on('business')->onDelete('cascade');
    $table->timestamps();
         });
     }

Well that's easy you just update them ( I also changed the way you delete buisnesses ):

public function updateAll(Request $request)
{
   foreach($request->businesses as $business){

   Buisness::find($buisness['id'])->delete();

   $newBuisness = Business::create([
      'id' => $business['id'],
      'title' => $business['title'],
      'description' => $business['description'],
      'order' => $business['order'],
      'visiblity' => $business['visiblity'],
      'lang' => $business['lang'],
   ]);

   $orphanPhotos = Photo::whereNull('buisness_id')->get();

   foreach($orphanPhotos  as $photo)
      {
         $photo->update(['buisness_id' => $newBuisness->id]);
      }
   }
   
   return response('Update Successful.',200);
}

You should try and search before asking for all made solutions though. It's not good for your learning if you get it served on a plate.

@GORAKH - He doesn't want is photos deleted , but updated.

@ZIBEN69 - you are not deleting your photos , just not reffering to your new buisness in it.

As a final note , I will show you how easy all of this would have been if you juste updated your models.

public function updateAll(Request $request)
{
   foreach($request->businesses as $business){

      $buisnessModel = Buisness::find($buisness['id']);
      $buisnessModel->title = $business['title'];
      $buisnessModel->description = $business['description'];
      $buisnessModel->order = $business['order'];
      $buisnessModel->visiblity = $business['visiblity'];
      $buisnessModel->lang = $business['lang'];

      $buisnessModel->save();
   }
   
   return response('Update Successful.',200);
}

and no changes in your migrations.

You should probably do this.

@STEREOH - That's work! Yeah time to analyze what you wrote.

<3 Thank you and everyone for help!

@stereoh yes thats a much better approach for only a update. And if only the order field needs a update

$buisnessModel = Buisness::find($buisness['id']);
$buisnessModel->order = $business['order'];
$buisnessModel->save();

@STEREOH - but why he is use Photo::truncate(); in code. ?

@GORAKH - he didn't , one of the reply did.

This is insane. Only an update was needed but i wonder why OP first thought of deleting the data for business and recreate it :/ Well i hope he got what he was looking for :)

thank for this !