gistfile1.txt

<?php

namespace AppNew\Console\Commands\OneOff;

use App\Type;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use FileInvite\Invite\Models\Attachment;
use Illuminate\Database\Eloquent\Builder;

class DeleteDuplicateAttachmentLogs extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'one-off:clean-duplicate-attachments {--inviterequest=} {--all}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Command description';

    const CHUNK_LIMIT = 100;

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        if (! $this->option('inviterequest') && ! $this->option('all')) {
            $this->info('You must choose if you want to remove the duplicate via individual invite request or remove all the duplicate attachment at the same time.');

            return false;
        }

        $pdfType = Type::where(['name' => 'pdf', 'description' => 'file'])->first();

        $this->getDuplicateFilesSql($pdfType)->chunk(20, function ($records) use ($pdfType) {
            $attachments = [];

            foreach ($records as $record) {
                if (! in_array($record->attachment_id, $attachments)) {
                    $attachments[] = $record->attachment_id;
                }

                foreach ($attachments as $id) {
                    $attachment = Attachment::find($id);
                    $this->deletePivot($attachment, $pdfType);
                }
            }
        });

        return true;
    }

    /**
     * Query: Get all duplicated pdf type files
     *
     * @return \FileInvite\API\Models\ApiSettings|Builder
     */
    public function getDuplicateFilesSql(Type $pdfType)
    {
        return Attachment::query()
            ->with('files')
            ->join('attachment_file', 'attachments.id', '=', 'attachment_file.attachment_id')
            ->join('files', 'files.id', '=', 'attachment_file.file_id')
            ->where('files.type_id', $pdfType->id)
            ->groupBy(['attachment_id'])

            ->when($this->option('inviterequest'), function (Builder $query) {
                return $query->where('attachments.inviterequest_id', $this->option('inviterequest'));
            })
            ->select(['attachments.id as attachment_id', 'attachments.*'])
            ->latest('files.id');
    }

    /**
     * Delete duplicated entries from pivot table
     *
     * @param Attachment $attachment
     */
    private function deletePivot(Attachment $attachment, Type $pdfType)
    {
        $attachment->files()->where('type_id', $pdfType->id)->orderBy('files.id', 'asc')->select(['files.*'])->chunkById(self::CHUNK_LIMIT, function ($files) {
            $count = 1;
            foreach ($files as $attachmentCount => $file) {

                // We assume that the first pdf file of that attachment is the file converted to pdf
                if ($attachmentCount > 0) {
                    DB::table('attachment_file')->where('file_id', $file->id)->delete();
                    $file->delete();
                }

                $count += 1;
            }

            $this->info('Deleted '.$count.' entries');
        }, 'attachment_file.id', 'id');
    }
}