Laravel Eloquent：如何在单查询中获取主副事件的名称与颜色

首先得在EventRelationship模型里定义两个关联，分别对应主事件和副事件：

// app/Models/EventRelationship.php
namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class EventRelationship extends Model
{
    protected $table = 'event_relationships';

    // 关联主事件
    public function primaryEvent()
    {
        return $this->belongsTo(Event::class, 'primary_event_id', 'id');
    }

    // 关联副事件
    public function secondaryEvent()
    {
        return $this->belongsTo(Event::class, 'secondary_event_id', 'id');
    }
}

之后查询时用with()预加载这两个关联，就能一次拿到所有数据（底层会执行1次主表查询+2次关联表查询，这是Eloquent解决N+1问题的标准做法，属于一次Eloquent查询操作）：

// 获取当前用户的所有事件关联及对应事件的名称和颜色
$eventRelationships = auth()->user()->eventRelationships()
    ->with(['primaryEvent:id,name,color', 'secondaryEvent:id,name,color'])
    ->get();

如果追求单条SQL语句的实现，也可以用联表查询：

$eventRelationships = auth()->user()->eventRelationships()
    ->select(
        'event_relationships.*',
        'pe.name as primary_event_name',
        'pe.color as primary_event_color',
        'se.name as secondary_event_name',
        'se.color as secondary_event_color'
    )
    ->join('events as pe', 'pe.id', '=', 'event_relationships.primary_event_id')
    ->join('events as se', 'se.id', '=', 'event_relationships.secondary_event_id')
    ->where('pe.user_id', auth()->id()) // 确保事件归属当前用户
    ->where('se.user_id', auth()->id())
    ->get();

两种方式各有优势：

• 预加载写法更贴合Eloquent的设计理念，代码好读，后续改需求也方便
• 联表查询会生成单条SQL，适合对性能要求特别高的场景

内容的提问来源于stack exchange，提问作者Erdss4