SQLSTATE[42S02]: Base table or view not found: 1051 Unknown table 'topics' on withCount

[sts-ryan-holton]

Hi, I'm using Laravel 10 and the latest version of this package. I'm also running PHP 8.3 and MySQL 8. I have a few models: Topic, TopicEntry and Post. A Topic can have many posts through a TopicEntry.

I need to get a count of all of the posts within a topic, I have a relationship defined called posts on my Topic model. This works without the tree method, but as soon as I add this I get the following error:

SQLSTATE[42S02]: Base table or view not found: 1051 Unknown table 'topics'

In more detail...

with recursive `laravel_cte` as ((select *, 0 as `depth`, cast(`id` as char(65535)) as `path` from `topics` where `parent_id` is null) union all (select `topics`.*, `depth` + 1 as `depth`, concat(`path`, ., `topics`.`id`) from `topics` inner join `laravel_cte` on `laravel_cte`.`id` = `topics`.`parent_id`)) select `topics`.*, (select count(*) from `posts` inner join `topic_entries` on `topic_entries`.`topic_id` = `posts`.`id` where `topics`.`id` = `topic_entries`.`post_id`) as `posts_count` from `laravel_cte` where `parent_id` is null limit 10 offset 0

Here's my Topic model

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use Staudenmeir\LaravelAdjacencyList\Eloquent\HasRecursiveRelationships;
use Illuminate\Database\Eloquent\Relations\HasMany;
use Illuminate\Database\Eloquent\Relations\HasManyThrough;

class Topic extends Model
{
    use HasFactory, HasRecursiveRelationships;

    /**
     * The attributes that are mass assignable.
     *
     * @var array<int, string>
     */
    protected $fillable = [
        'parent_id',
        'topic',
        'slug',
    ];

    /**
     * Get the breadcrumb name
     */
    public function breadcrumbName(): string
    {
        return $this->topic;
    }

    /**
     * Get the children that the model has
     */
    public function children(): HasMany
    {
        return $this->hasMany(Topic::class, 'parent_id', 'id');
    }

    /**
     * Get all of the posts for the project.
     */
    public function posts(): HasManyThrough
    {
        return $this->hasManyThrough(
            Post::class,
            TopicEntry::class,
            'post_id',
            'id',
            'id',
            'topic_id'
        );
    }

    /**
     * Scope a query to only include popular users.
     */
    public function scopeRoot(Builder $query): void
    {
        $query->whereNull('parent_id');
    }

    /**
     * Bootstrap any application services.
     */
    public static function boot()
    {
        parent::boot();

        static::deleted(function ($model) {
            $model->children()->delete();
        });
    }
}

And then the following usages in my controller:

Topic::withCount('posts')->with('children')->root()->tree()->paginate(10) // BREAKS
Topic::withCount('posts')->with('children'))->paginate(10) // WORKS

What am I missing? I tried adding `with('children.posts') with little success

[staudenmeir]

Hi @sts-ryan-holton,
For technical reasons, the tree() scope needs to come before withCount():

Topic::tree()->withCount('posts')->with('children')->root()->paginate(10)

What is your goal with this query? If you build the whole tree with tree() but then add root() to limit the results to only root topics, the tree() scope doesn't do anything (and even slows down the query).

[sts-ryan-holton]

My goal is to get a list of my root level Topic models with a count of the posts for each topic along with the children of the children etc. root gets all of my root level topics where parent_id is null, unless the tree does this by default?

[staudenmeir]

If you only want to get root topics, you don't need the tree() scope. You can also use the package's included isRoot() scope:

Topic::withCount('posts')->with('children')->isRoot()->paginate(10)

along with the children of the children etc.

->with('children') only gets direct children. Your description sounds more like ->with('descendants'):
https://github.com/staudenmeir/laravel-adjacency-list?tab=readme-ov-file#included-relationships

[sts-ryan-holton]

@staudenmeir What am I missing here...

I need to get the posts count for each nested level, initially with the with('children') I've got to specify withCount() to get them, if i add a third nested item like my screenshot, I'd have to nest again on this query. Also, with('descendants') doesn't get me the posts count for each level other than the root?

/**
 * Display a listing of the resource.
 */
public function index()
{
    return view('admin.topics.index', [
        'topics' => Topic::tree()->withCount('posts')->with([
            'children' => function ($query) {
                $query->withCount('posts');
            }
        ])->root()->paginate(10)
    ]);
}

[staudenmeir]

You want to paginate only root topics, right? Eager load the descendants relationship together with withCount('posts').

        'topics' => Topic::withCount('posts')->with([
            'descendants' => function ($query) {
                $query->withCount('posts');
            }
        ])->root()->paginate(10)

You can convert the decsendants relationship results into a nested tree if that makes it easier to generate your view:

$topics->each(fn($topic) => $topic->setRelation('tree', $topic->descendants->toTree()));

foreach ($topics as $topic) {
    foreach ($topic->tree as $child) {
        foreach ($child->children as $child2) {
            // ...
        }
    }
}

[sts-ryan-holton]

i want to paginate root level items, and see an indefinite number of nested children. For each child I want my statistics for each one. Changing children to descendants only ever gave me the statistics for my root level and nothing below it.

The only way I can see to do it is like this:

return view('admin.topics.index', [
    'topics' => Topic::tree()->withCount('posts')->with([
        'children' => function ($query) {
            $query->withCount('posts')->with([
                'children' => function () {
                    $query->withCount('posts');
                    // and so on...
                }
            ]);
        }
    ])->root()->paginate(10)
]);

Which is messy to keep doing that. I thought this package might be able to automatically figure out that I'm trying to load another relationship on each child and whenever children are called, each call the stats for it no?

[staudenmeir]

The package can't recursively eager load nested children relationships, but that's also not desirable (for performance reasons). The descendants relationships loads all direct and indirect children into a flat list and toTree() can then convert this list into nested children relationships.

Replace tree with children in the example from my previous comment and you get the structure you described:

$topics = Topic::withCount('posts')->with([
    'descendants' => fn($query) => $query->withCount('posts'),
])->root()->paginate(10);

$topics->each(
    fn(Topic $topic) => $topic->setRelation('children', $topic->descendants->toTree())
);

foreach ($topics as $topic) {
    dump($topic->posts_count);

    foreach ($topic->children as $child) {
        dump($child->posts_count);     
   
        foreach ($child->children as $child2) {
            dump($child2->posts_count);

            // ...
        }
    }
}

return view('admin.topics.index', [
    'topics' => $topics,
]);