Laravel 11中如何查询地点及子地点的Timeline访问总量？

我使用Laravel 11框架，包含Timeline与Place模型。Timeline包含时间戳与place_id字段，Place通过parent_id字段关联子地点。需求是通过预加载查询，获取主地点及其子地点的Timeline访问总次数。

已为Place定义两个关联关系，可分别获取主地点直接访问量与子地点访问量：

Place::query()
  ->withCount('visits') // 返回visits_count，示例值118
  ->withCount('visitsChildren') // 返回visits_children_count，示例值481

现有尝试的问题 #

1. Union关联预加载无效
   尝试通过union定义visitsTotal关联：

// app/Models/Place
public function visitsTotal()
{
    // 仅查询指定字段避免SQL列数不一致错误
    return $this->visits()->select('timelines.id')
        ->union($this->visitsChildren()->select('timelines.id'));
}

单个地点查询有效：

Place::find($placeId)->visits->count(); // 118
Place::find($placeId)->visitsChildren->count(); // 481
Place::find($placeId)->visitsTotal->count(); // 599（118+481）

但预加载用withCount时不符合预期：

Place::query()
  ->where('id', $placeId)
  ->withCount('visitsTotal') // 预期599，实际得到118
  ->get()

2. selectRaw计算总和报错
   尝试用selectRaw计算总和，报错Unknown column 'visits_count' in 'field list'：

Place::query()
  ->where('id', $placeId)
  ->withCount('visits')
  ->withCount('visitsChildren')
  ->selectRaw('visits_count + visits_children_count as visits_total_count')
  ->get()

Place模型关联定义 #

// app/Models/Place
public function visits()
{
    return $this->hasMany(Timeline::class);
}
public function visitsChildren()
{
    return $this->hasManyThrough(
        Timeline::class, // 最终关联模型
        Place::class, // 中间模型
        'parent_id', // 中间模型外键
        'place_id', // 最终模型外键
        'id', // 当前模型本地键
        'id' // 中间模型本地键
    );
}

生成的原生SQL：

SELECT
    `places`.*,
    (
        SELECT
            count(*)
        FROM
            `timelines`
        WHERE
            `places`.`id` = `timelines`.`place_id`) AS `visits_count`, 
    (
        SELECT
            count(*)
        FROM
            `timelines`
            INNER JOIN `places` AS `laravel_reserved_2` ON `laravel_reserved_2`.`id` = `timelines`.`place_id`
        WHERE
            `laravel_reserved_2`.`deleted_at` IS NULL
            AND `places`.`id` = `laravel_reserved_2`.`parent_id`) AS `visits_children_count`, 
    visits_count + visits_children_count AS visits_total_count
    FROM
        `places`

方法一：优化visitsTotal关联，支持withCount #

修改visitsTotal关联，直接通过条件查询主地点和子地点的所有Timeline：

// app/Models/Place
public function visitsTotal()
{
    return $this->hasMany(Timeline::class)
        ->orWhereExists(function ($query) {
            $query->select(DB::raw(1))
                ->from('places as child_places')
                ->whereColumn('child_places.parent_id', 'places.id')
                ->whereColumn('child_places.id', 'timelines.place_id')
                ->whereNull('child_places.deleted_at');
        });
}

之后直接用withCount即可获取正确总次数：

Place::query()
    ->where('id', $placeId)
    ->withCount('visitsTotal') // 正确返回599
    ->get();

方法二：直接在查询中计算总和 #

利用MySQL子查询直接相加，绕过别名无法直接引用的问题：

Place::query()
    ->where('id', $placeId)
    ->withCount(['visits', 'visitsChildren'])
    ->selectRaw('
        places.*,
        (SELECT count(*) FROM timelines WHERE places.id = timelines.place_id) + 
        (SELECT count(*) FROM timelines INNER JOIN places AS child_places ON child_places.id = timelines.place_id WHERE child_places.deleted_at IS NULL AND places.id = child_places.parent_id) 
        AS visits_total_count
    ')
    ->get();

方法三：使用模型访问器 #

如果不需要将总次数作为数据库查询字段，可定义访问器在模型实例上计算：

// app/Models/Place
protected $appends = ['visits_total_count'];

public function getVisitsTotalCountAttribute()
{
    return $this->visits_count + $this->visits_children_count;
}

查询时只需加载两个基础count：

Place::query()
    ->where('id', $placeId)
    ->withCount(['visits', 'visitsChildren'])
    ->get();

此时模型实例会自动拥有visits_total_count属性，值为两者之和。

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