Refactoring / Optimising Query

Hi, I have the following query & I have just noticed a problem it is selecting every single user from the database, where I want it to only select the owners (users) from the database who have the last reply (if there is a reply) from the users database.

I'm not certain why it's pulling every user from the database in the section where it says ->with('owner:id,username,email') because I figured that is a relationship on the replies table, so it would only pull the users related to the reply rows it finds? I think because in the entire threads table each user has created at least 1 reply, but I only want it to get the users who have the last reply for each of the threads of the current page of paginated results.

Otherwise it's going to always pull all the users in the query, because certainly overtime every user will have a reply to a post & this is going to become more & more bloated query.

Please help!

public function getThreads(ThreadFilters $filters): LengthAwarePaginator
    {
        return Thread::query()
            ->with('creator:id,username,email')
            ->with('topics:id,title,slug,color,description,icon')
            ->with(['replies' => function ($query) {
                $query->select('replies.id', 'replies.thread_id', 'replies.created_at', 'replies.user_id')
                    ->with('owner:id,username,email');
            }])
            ->filter($filters)
            ->paginate(ForumHelpers::PAGINATION_LIMIT)
            ->withQueryString()
            ->through(function ($thread) {
                return [
                    'title' => $thread->title,
                    'body' => $thread->body,
                    'slug' => $thread->slug,
                    'views' => $thread->views,
                    'solved' => $thread->solved,
                    'created_at' => $thread->created_at->diffForHumans(),
                    'updated_at' => $thread->updated_at->diffForHumans(),
                    'creator' => [
                        'username' => $thread->creator->username,
                        'email' => $thread->creator->email,
                    ],
                    'topic' => [
                        'title' => $thread->topics->first()->title,
                        'slug' => $thread->topics->first()->slug,
                        'color' => $thread->topics->first()->color,
                        'description' => $thread->topics->first()->description,
                        'icon' => $thread->topics->first()->icon,
                    ],
                    'replies' => $thread->replies->count() ? [
                        'count' => $thread->replies->count(),
                        'first' => [
                            'created_at' => $thread->replies->last()->created_at->diffForHumans(),
                            'owner' => [
                                'username' => $thread->replies->last()->owner->username,
                            ]
                        ],
                    ] : null,

                ];
            });
    }

Here is the clockwork query results (I can tell something is not right by the format of that last users query, but I am stumped for how to fix it)

597553F7-5402-4760-AD31-70A334680E39

vincent15000

3 years ago

Level 63

That's not directly related to your problem, but what I would do is to extract all the array in an API resource.

https://laravel.com/docs/10.x/eloquent-resources#concept-overview

Then about your problem, I think that it would be a better idea to retrieve the datas from the paginated query without the owners and map over this paginated collection to manually load the missing users for each thread (->loadMissing('owner')). In this way, you will only load the owners needed on the paginated part of datas.

CamKem

3 years ago

Level 10

@vincent15000 Can you give me an example of how to use loadMissing() in this instance?

1 like

vincent15000

3 years ago

Best Answer

Level 63

@CamKem

I haven't tested, but this is the idea, it should work.

$threads = Thread::
	with(['creator', 'topics'])
	->paginate()
	->through(function ($thread) {
		foreach ($thread->replies as $reply) {
			$reply->loadMissing('owner'); // or perhaps just load('owner')
		}
		return $thread;
	});

return ThreadResource::collection($threads);

And you define the array to return in a resource.

public function toArray($request)
{
    return [
        'title' => $this->title,
        'body' => $this->body,
		...
		'creator' => new CreatorResource($this->creator),
		'topic' => new TopicResource($this->topics->first(),
		...
    ];
}

CamKem

3 years ago

Level 10

@vincent15000 Thanks! I'll give it shot!

1 like

vincent15000

3 years ago

Level 63

@CamKem Happy to help ;).

Please or to participate in this conversation.