Laravel中PPA自引用实体的模型与控制器实现咨询

一、数据库迁移设计 #

首先创建两个核心表：rc_indicators（指标表）和ppas（自引用的PPA表），支持灵活的层级关联场景。

1. Rc_Indicator 表迁移 #

Schema::create('rc_indicators', function (Blueprint $table) {
    $table->id();
    $table->string('name')->comment('指标名称');
    $table->text('description')->nullable()->comment('指标描述');
    $table->timestamps();
});

2. PPA 自引用表迁移 #

Schema::create('ppas', function (Blueprint $table) {
    $table->id();
    $table->foreignId('rc_indicator_id')->nullable()->constrained()->onDelete('cascade')->comment('关联指标ID');
    $table->foreignId('parent_id')->nullable()->constrained('ppas')->onDelete('cascade')->comment('父PPA ID');
    $table->string('name')->comment('PPA名称');
    $table->enum('type', ['program', 'project', 'activity'])->comment('PPA类型：项目/子项目/活动');
    $table->text('description')->nullable()->comment('PPA描述');
    $table->timestamps();
});

• rc_indicator_id设为 nullable：支持Activity直接关联Indicator，无需通过Program/Project中转
• parent_id设为 nullable：支持顶层Program直接关联Indicator
• 级联删除配置：删除父节点时自动删除子节点，避免数据冗余

二、模型定义 #

1. RcIndicator 模型 #

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasMany;

class RcIndicator extends Model
{
    protected $fillable = ['name', 'description'];

    // 关联所有直接关联的PPA（含Program、直接关联的Activity等）
    public function ppas(): HasMany
    {
        return $this->hasMany(Ppa::class);
    }

    // 快捷筛选该指标下的所有Program
    public function programs(): HasMany
    {
        return $this->ppas()->where('type', 'program');
    }

    // 快捷筛选该指标直接关联的Activity
    public function directActivities(): HasMany
    {
        return $this->ppas()->where('type', 'activity');
    }
}

2. PPA 自引用模型 #

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Relations\HasMany;

class Ppa extends Model
{
    protected $fillable = ['rc_indicator_id', 'parent_id', 'name', 'type', 'description'];

    // 关联所属指标
    public function indicator(): BelongsTo
    {
        return $this->belongsTo(RcIndicator::class, 'rc_indicator_id');
    }

    // 关联父PPA节点
    public function parent(): BelongsTo
    {
        return $this->belongsTo(self::class, 'parent_id');
    }

    // 关联子PPA节点（递归加载子节点，支持无限层级）
    public function children(): HasMany
    {
        return $this->hasMany(self::class, 'parent_id')->with('children');
    }

    // 本地作用域：筛选Program类型
    public function scopePrograms($query)
    {
        return $query->where('type', 'program');
    }

    // 本地作用域：筛选Project类型
    public function scopeProjects($query)
    {
        return $query->where('type', 'project');
    }

    // 本地作用域：筛选Activity类型
    public function scopeActivities($query)
    {
        return $query->where('type', 'activity');
    }

    // 自定义属性：获取完整层级路径（如 "指标A > 项目1 > 子项目1 > 活动1"）
    public function getHierarchyPathAttribute()
    {
        $path = [$this->name];
        $parent = $this->parent;
        while ($parent) {
            array_unshift($path, $parent->name);
            $parent = $parent->parent;
        }
        if ($this->indicator) {
            array_unshift($path, $this->indicator->name);
        }
        return implode(' > ', $path);
    }
}

三、控制器实现 #

以下是核心业务逻辑的控制器示例，包含CRUD和层级树查询：

namespace App\Http\Controllers;

use App\Models\Ppa;
use App\Models\RcIndicator;
use Illuminate\Http\Request;

class PpaController extends Controller
{
    // 获取指定指标下的完整PPA层级树
    public function getIndicatorPpaTree($indicatorId)
    {
        $indicator = RcIndicator::findOrFail($indicatorId);
        // 获取指标下的顶层PPA（含Program、直接关联的Activity）
        $topLevelPpas = $indicator->ppas()
            ->whereNull('parent_id')
            ->with('children')
            ->get();
        return response()->json($topLevelPpas);
    }

    // 创建PPA节点
    public function store(Request $request)
    {
        $validated = $request->validate([
            'rc_indicator_id' => 'nullable|exists:rc_indicators,id',
            'parent_id' => 'nullable|exists:ppas,id',
            'name' => 'required|string|max:255',
            'type' => 'required|in:program,project,activity',
            'description' => 'nullable|text'
        ]);

        // 层级合法性校验
        if ($validated['parent_id']) {
            $parent = Ppa::find($validated['parent_id']);
            match ($validated['type']) {
                'project' => throw_if($parent->type !== 'program', new \Exception('Project的父节点必须是Program')),
                'activity' => throw_if(!in_array($parent->type, ['program', 'project']), new \Exception('Activity的父节点只能是Program或Project')),
                default => null
            };
        }

        $ppa = Ppa::create($validated);
        return response()->json($ppa, 201);
    }

    // 更新PPA节点
    public function update(Request $request, Ppa $ppa)
    {
        $validated = $request->validate([
            'rc_indicator_id' => 'nullable|exists:rc_indicators,id',
            'parent_id' => 'nullable|exists:ppas,id|not_in:'.$ppa->id, // 禁止自关联
            'name' => 'required|string|max:255',
            'type' => 'required|in:program,project,activity',
            'description' => 'nullable|text'
        ]);

        // 重复层级合法性校验
        if (isset($validated['parent_id'])) {
            $parent = Ppa::find($validated['parent_id']);
            match ($validated['type']) {
                'project' => throw_if($parent->type !== 'program', new \Exception('Project的父节点必须是Program')),
                'activity' => throw_if(!in_array($parent->type, ['program', 'project']), new \Exception('Activity的父节点只能是Program或Project')),
                default => null
            };
        }

        $ppa->update($validated);
        return response()->json($ppa);
    }

    // 删除PPA节点（自动删除子节点）
    public function destroy(Ppa $ppa)
    {
        $ppa->delete();
        return response()->json(['message' => 'PPA节点已删除']);
    }

    // 获取指定PPA节点的完整子树
    public function getChildrenTree(Ppa $ppa)
    {
        $tree = $ppa->load('children');
        return response()->json($tree);
    }
}

四、常用查询示例 #

// 获取指标1下的所有Program
$indicator = RcIndicator::find(1);
$programs = $indicator->programs()->get();

// 获取Program1下的所有子节点（Project+Activity）
$program = Ppa::programs()->find(1);
$children = $program->children;

// 获取指标1直接关联的所有Activity
$directActivities = $indicator->directActivities()->get();

// 获取Activity1的完整层级路径
$activity = Ppa::activities()->find(1);
echo $activity->hierarchyPath;

内容的提问来源于stack exchange，提问作者Ruthyn Ann POTAYRE