Laravel query to get sum of multiple relationship counts using e.g. union in relationship or selectRaw

Question

I have a Laravel 11 setup with Timeline and Place models.
Timeline has a timestamp and a place_id. A place can have child places, using parent_id to point to the parent place.

Question: how do I (eager) query the places to include the sum of the timeline visits to the main place and child places?

I have two Place relationships for the (direct) visits and visits to the child places, so: no problem to get them separately.

E.g.

Place::query()
  ->withCount('visits') // visits_count, e.g. 118
  ->withCount('visitsChildren') // visits_children_count, e.g. 481

Following this answer and this answer, I tried:

// app/Models/Place

public function visitsTotal()
{
    // only getting specific fields to avoid the SQL error: 
    // The used SELECT statements have a different number of columns

    return $this->visits()->select('timelines.id')
        ->union($this->visitsChildren()->select('timelines.id'));
}

This works for a specific place:

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

But it does not work for an eager query:

Place::query()
  ->where('id', $placeId)
  // ->withCount('visits') // 118
  // ->withCount('visitsChildren') // 481
  ->withCount('visitsTotal') // expected: 599, but I get: 118
  ->get()

So, then I get for visits_total_count the same number as for the main visits (e.g. 118).

I also tried to add a select:

Place::query()
  ->where('id', $placeId)
  ->withCount('visits') // visits_count, e.g. 118
  ->withCount('visitsChildren') // visits_children_count, e.g. 481
  ->selectRaw('visits_count + visits_children_count as visits_total_count') // expected: 599
  ->get()

But then I get an error: Unknown column 'visits_count' in 'field list'.

The relationships:

// app/Models/Place

public function visits()
{
    return $this->hasMany(Timeline::class);
}

public function visitsChildren()
{
    return $this->hasManyThrough(
        Timeline::class, // the end result
        Place::class, // going through this one
        'parent_id', // foreign key on going through Place (child)
        'place_id', // foreign key on Timeline
        'id', // local key on Place (parent)
        'id' // local key on going through Place (child)
    );
}

The toRawSql() query that's created when trying selectRaw:

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`

Answer 1

Given the relationships (HasMany ans HasManyThrough) both point to the same model, there is perhaps a hack you could use. Try defining another relationship based on visitsChildren and adding an extra condition to it.

public function visitsTotal(): HasManyThrough
{
    return $this->visitsChildren()->orWhereColumn('timelines.place_id', 'places.id');
}

Alternatively, I'd suggest just using the two available counts and if sorting is really important, it can still be done through Collection methods