Help optimizing 25mb query

Hi guys. I'm using a data table to list, search and filter my users. I'm getting a 25mb query.

First problem is I don't know how to reduce the records and still be able to filter them from the data table so I'm getting all the users.

The second problem is that I need the last login from Spatie Activity log, that runs more queries which leaves me with 1756 queries, 1464 models for 584 users.

    public function index(Request $request)
    {
        $users = User::latest()
            ->get([
                'id', 
                'first_name', 
                'last_name',
                'email', 
            ]);

        $users->map(function ($user) {
            $certifications = Result::where('user_id', $user->id)->get();
            $certifications = $certifications->pluck('total_marks', 'user_marks')->toArray();

            $user->total_certifications = count($certifications);
        });

        $users->map(function ($user) {
            $user->last_login = Activity::where('causer_id', $user->id)
                ->where('description', 'like', '%logged in%')
                ->latest()
                ->first();
        });

Please bare with me, rookie.

jlrdw

3 years ago

Level 75

Learn server side pagination. Yours is not the first question for someone who tries to load too many records at one time.

Sinnbeck

3 years ago

Best Answer

Level 102

You need to move both of those to the data. Then it should be possible to do it in 1 query

Example

$loginsQuery = Activity::whereColumn('causer_id', 'users.id')
                ->where('description', 'like', '%logged in%')
                ->latest()
               ->select('created_at')
                ->limit(1);
$users = User::latest()
   ->selectSub($loginsQuery, 'last_login')
    ->withCount('results as total_certifications')
   ->paginate(100);

And as @jlrdw said, you probably don't want to get every single user. Replace get with paginate

dysentry30

3 years ago

Level 1

I think, you can use this to make query more efficient.

User::with(["Result", "Activity"])->get();

In Laravel, this is called "Eager Loading" or you can search their official website and type this keyword “Eager Loading”. Make sure, you created "Result" and "Activity" model before you run this syntax.

Please or to participate in this conversation.