如何使用<select>标签中嵌套的项目列表减少数据库查询的数量？

Question

Laravel 5.8: I'm getting too much queries when I use nested list of items.

This is for menu in the View. I've done nested menu of items but it seems to me it takes too much queries(10 extra queries for my list of 30 items) Although I used "eager loads".

In the model:

public function children()
{
    return $this->hasMany(Item::class, 'parent_id', 'id');
}

Its Repository: (used eager loads [with])

public function getListOfItemTitles()
{
    $columns = ['id', 'parent_id', 'title'];

    $result = $this->startConditions()
        ->select($columns)
        ->with(['children:id,title,parent_id'])
        ->get();
    return $result;
}

And the view:

<select name="item_id" id="item_id" class="form-control" required>
    @foreach($itemList as $itemOption)
        @if($itemOption->parent_id == 1)
            <optgroup label="{{ $itemOption->title }}">
                @foreach($itemOption->children as $children)
                    <option value="{{ $children->id }}">
                        {{ $children->title }}
                    </option>
                    @foreach($children->children as $subchildren)
                        <option value="{{ $subchildren->id }}">
                            —{{ $subchildren->title }}
                        </option>
                    @endforeach
                @endforeach
            </optgroup>
        @endif
    @endforeach
</select>

Here are my queries:

select `id`, `parent_id`, `title` from `items` where `items`.`deleted_at` is null
select `id`, `title`, `parent_id` from `items` where `items`.`parent_id` in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30) and `items`.`deleted_at` is null
select * from `items` where `items`.`parent_id` = 4 and `items`.`parent_id` is not null and `items`.`deleted_at` is null
select * from `items` where `items`.`parent_id` = 8 and `items`.`parent_id` is not null and `items`.`deleted_at` is null
select * from `items` where `items`.`parent_id` = 12 and `items`.`parent_id` is not null and `items`.`deleted_at` is null
select * from `items` where `items`.`parent_id` = 16 and `items`.`parent_id` is not null and `items`.`deleted_at` is null
select * from `items` where `items`.`parent_id` = 20 and `items`.`parent_id` is not null and `items`.`deleted_at` is null
select * from `items` where `items`.`parent_id` = 26 and `items`.`parent_id` is not null and `items`.`deleted_at` is null
select * from `items` where `items`.`parent_id` = 27 and `items`.`parent_id` is not null and `items`.`deleted_at` is null
select * from `items` where `items`.`parent_id` = 28 and `items`.`parent_id` is not null and `items`.`deleted_at` is null
select * from `items` where `items`.`parent_id` = 29 and `items`.`parent_id` is not null and `items`.`deleted_at` is null
select * from `items` where `items`.`parent_id` = 30 and `items`.`parent_id` is not null and `items`.`deleted_at` is null

I'd be happy if you help me to decrease amount of queries. Any additional advice in this case would be very apreciated since I'm new in PhP & Laravel.

Answer 1

You must eager load the subschildren, too:

$columns = ['id', 'parent_id', 'title'];

$result = $this->startConditions()
    ->select($columns)
    ->with(['children:id,title,parent_id', 'children.children:id,title'])
    ->get();
return $result;

Consider the following example from this article: https://laravel-news.com/eloquent-eager-loading

$posts = App\Post::with('author.profile')->get();
$posts->map(function ($post) {
    return $post->author->profile;
});

Here you can see they use dot notation to fetch deep relationships using with()