Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

angelina-ss's avatar

how to exclude soft deleted rows from Laravel Excel export?

Hello, i have a view that listed Klien records using withTrashed() method. I made a button in this view to export all the records from Klien model along with other tables that have relationship with Klien.

Schema::create('kliens', function (Blueprint $table) {
            $table->string('klien_uic', 15)->primary();
            $table->string('nama_lahir', 50);
            $table->string('nama_panggilan', 15);
            $table->date('tanggal_lahir');
            $table->string('kategori_klien', 10);
            $table->unsignedBigInteger('staf_pendamping_id');
            $table->timestamps();
            $table->softDeletes();

            $table->foreign('staf_pendamping_id')->references('staf_id')->on('stafs')->onUpdate('cascade')->onDelete('cascade');
        });
public function index()
    {
        return view('dashboard.klien.index', [
            "title" => "Klien",
            "kliens" => Klien::withTrashed()->latest('kliens.created_at')->filter(request(['search','kategoriklien']))->with('staf')->paginate(10)
        ]);
    }

I got an error every time i tried to export to XLSX if the Klien is soft deleted and one-to-many relationship table failed to find the foreign key klien_uic. This is my KliensExport.php:

public function map($klien): array
    {
        return [
            // Data Utama Klien
            Date::dateTimeToExcel(Carbon::parse($klien->created_at)),
            $klien->klien_uic,
            $klien->nama_lahir,
            $klien->nama_panggilan,
            $klien->tanggal_lahir,
            $klien->kategori_klien,
            $klien->staf->nama_staf,

            // Identitas Penduduk Klien
            $klien->identitaspendudukklien['kota_asal'],
            $klien->identitaspendudukklien['alamat_domisili'],
            $klien->identitaspendudukklien['kota_madya_domisili'],
            $klien->identitaspendudukklien['status_domisili'],
            $klien->identitaspendudukklien['jenis_domisili'],
            $klien->identitaspendudukklien['jenis_ktp'],
            $klien->identitaspendudukklien['klien_nik'],
            $klien->identitaspendudukklien['asuransi_kesehatan'],

            // Medsos Klien
            $klien->medsosklien['tidak_punya_medsos'] != 0 ? "Iya" : "Tidak",
            $klien->medsosklien['facebook'] != 0 ? "Punya" : "Tidak Punya",
            $klien->medsosklien['instagram'] != 0 ? "Punya" : "Tidak Punya",
            $klien->medsosklien['twitter'] != 0 ? "Punya" : "Tidak Punya",
            $klien->medsosklien['tiktok'] != 0 ? "Punya" : "Tidak Punya",
            $klien->medsosklien['medsos_lain'] != NULL ? $klien->medsosklien->medsos_lain : "Tidak Ada",

            // Pendikan Klien
            $klien->pendidikanklien['jenjang_pendidikan'],
            $klien->pendidikanklien['apakah_lanjut_pendidikan'],
        ];
    }
    
    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        return Klien::filter(request(['search','kategoriklien']))->get();
    }

    public function sheets(): array
    {
        $sheets = [];
            // parent table
            $sheets[] = new KliensExport;
            // hasMany child tables 
            $sheets[] = new KeterampilanKliensExport;
            $sheets[] = new PekerjaanKliensExport;
            $sheets[] = new KontakKliensExport;
            $sheets[] = new KontakLainKliensExport;
            $sheets[] = new RiwayatPerilakuSeksualKliensExport;
            $sheets[] = new AksesFaskesKliensExport;
            $sheets[] = new KesehatanHivAidsKliensExport;
            $sheets[] = new PerawatanPendampinganKliensExport;
            $sheets[] = new RiwayatKekerasanVerbalKliensExport;
            $sheets[] = new RiwayatKekerasanFisikKliensExport;

        return $sheets;
    }

thank you in advance!

0 likes
5 replies
sr57's avatar
sr57
Best Answer
Level 39

Hi @angelina-ss

if the Klien is soft deleted and one-to-many relationship table failed to find the foreign key klien_uic

These 2 assertions should not be linked, "failed to find the foreign key klien_uic" means a linked table has a record with a field linked to a "klien_uic" that does not exist in table "klients"

You did not provide linked table(s) with "klien_uic", but probably you missed on "delete on cascade" and you db is not consistent.

You have to :

  • remove this inconsistent records

  • put an on delete" as needed

as that should be ok.

1 like
angelina-ss's avatar

@sr57 thank you for your explanation. After reading it, my hasMany table actually doesn't have onDelete('cascade'), and this is the example:

        Schema::create('keterampilan_kliens', function (Blueprint $table) {
            $table->id('keterampilan_klien_id')->autoIncrement();
            $table->string('klien_uic', 15);
            $table->string('nama_keterampilan', 40);
            $table->string('status_keterampilan', 30);
            $table->timestamps();

            $table->foreign('klien_uic')->references('klien_uic')->on('kliens')->onUpdate('cascade');
        });

should i put use SoftDeletes, CascadeSoftDeletes; on the hasMany table? i expect the soft-deleted Klien and its child tables excluded from the excel export. I only put the use SoftDeletes, CascadeSoftDeletes; on the Klien model because i thought the child tables will be excluded automatically, thank you

my child tables destroy() method:

public function destroy(Klien $klien, KeterampilanKlien $keterampilan)
    {
        if($keterampilan->klien_uic != $klien->klien_uic) {
            abort(404);
        }

        $keterampilan->where('keterampilan_klien_id', $keterampilan->keterampilan_klien_id)->delete();

        return back()->with('success', 'Keterampilan dihapus.');
    }
sr57's avatar

should i put use SoftDeletes, CascadeSoftDeletes; on the hasMany table?

Yes (It's not done automatically, for delete cascade for isntance, to be sure it's what the user (dev) wants, because, this command deletes all records in linked tables)

1 like

Please or to participate in this conversation.