使用laravel eloquent优化查询

3xiyfsfu  于 2021-07-26  发布在  Java
关注(0)|答案(0)|浏览(201)

我需要优化以下查询,因为生成的ajax请求内存不足。

$types = $request->get('types', null);

        return [
            'results' => Client::with([
                'people' => function ($query) use ($request) {
                    $query
                        ->when(! $request->has('withDeceased'), function (Builder $query) {
                            $query->notDeceased();
                        })
                        ->select(['people.id', 'preferred_name', 'full_name', 'home_postcode']);
                },
                'type',
            ])
                ->when(! empty($request->term), function (Builder $query) use ($request) {
                    $query->whereHas('people', function (Builder $query) use ($request) {
                        $query
                            ->where('full_name', 'ilike', '%' . $request->term . '%')
                            ->orWhere('preferred_name', 'ilike', '%' . $request->term . '%');
                    })
                        ->orWhere(function (Builder $query) use ($request) {
                            $query->where('name', 'ilike', '%' . $request->term . '%')
                                ->orWhere('trading_name', 'ilike', '%' . $request->term . '%')
                                ->orWhere('company_number', 'ilike', '%' . $request->term . '%');
                        });
                })
                ->filterByType($types)
                ->orderBy('name')
                ->get()
                ->map(function ($client) use ($request) {
                    if ($client->type->slug == 'personal') {
                        $postcode = $client->people->map->home_postcode->filter()->first();
                        $text = $client->people->map->full_name->implode(' & ') . ($postcode ? ' - ' . $postcode : '');
                    } else {
                        $text = $client->name;
                    }

                    $clientArr = [
                        'id' => $client->id,
                        'text' => $text,
                    ];
                    $request->has('withPeople') ? $clientArr['people'] = $client->people : null;

                    return $clientArr;
                }),
        ];

我只需要通过查询返回这些结果所需的相关数据来进行优化。
我唯一能看到的优化方法就是改变 type, 收件人:

'type' => function (BelongsTo $query) {
   $query->select(['client_types.id', 'name', 'slug']);
},

我还能做些什么来优化它吗?

暂无答案!

目前还没有任何答案,快来回答吧!

相关问题