Inefficient query how to impove number of queries

@garethredfern, Have you tried to mount the query through the user?

make use of the eager load. https://laravel.com/docs/master/eloquent-relationships#eager-loading

// I'm guessing you have the following relationships in your App\User.php
// User hasMany results
function results() {
    return $this->hasMany(App\Results::class);
}
// User hasMany compliances
function compliances() {
    return $this->hasMany(App\Compilance::class);
}

// query

$usersWithMatrix = App\User::query()
    ->with([
        'results' => function($results) use ($courseIds, $entityIds) {
            $results
                ->select(
                    'course_id',
                    'expiry_warning',
                    'passed',
                    'score',
                    'type',
                    'user_id',
                    'expires',
                    'completed',
                    'attempt'
                )
                ->whereIn('course_id', $courseIds)
                ->whereIn('entity_id', $entityIds)
                ->where('passed', true);
            },
            'compliances' => function($compilances) use ($courseIds) {
                $compilances
                    ->select(
                        'users.id AS user_id',
                        'users.name AS user',
                        'roles.name AS role',
                        'roles.id AS role_id',
                        'courses.id AS course_id',
                        'courses.title AS course_title',
                        'courses.short_title AS short_title',
                        'compliance.is_compliant AS is_compliant',
                        'compliance.include AS include',
                        'compliance.exclude AS exclude',
                        'compliance.type AS type'
                    )
                    ->join('courses', 'courses.id', '=', 'compliance.course_id')
                    ->join('roles', 'roles.id', '=', 'compliance.role_id')
                    ->join('users', 'users.role_id', '=', 'roles.id')
                    ->when(!empty($courseIds), function ($query) use ($courseIds) {
                        $query->whereIn('courses.id', $courseIds);
                    })->distinct('course_id');
                },
            ])->get();

The mapping you do last seems like that really should be done in the database and not in php.

The database is always faster than php if the sql is sound.

I would also append ->toSql() on both queries to get the sql and the run explain on them to see if there are some indexes missing.

Functions inside queries is almost never a good option since the parser can't optimize them.

Distinct should not be used if it's possible to avoid since they will in many cases make the performance worse.

I also suggest that you paginate your result.

@tray2 , I agree with you about the root of the problem. It is the map function.

But these anonymous functions within Eloquent will not iterate over data.

They set the parameters of a subquery for QueryBuilder. In the end it will result in 3 queries, 1 for the User Model, 1 Compilance, 1 Result.

See the test below with my application.

Route::get('/u', function () {
    \DB::connection()->enableQueryLog();

    $u = \App\Usuario::with([
        'acordos' => function($a){
            $a->whereYear('created_at', 2019);
        },
        'promessas' => function($p){
            $p->whereYear('created_at', 2019);
        }])
    ->get();

    $queries = DB::getQueryLog();

    dump($queries);
});

The telescope shows the 3 queries.

The inclauses seems to contain every user id and there is a limit of how many you can specify like that,

I suggest you use a subquery there instead something like

SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE first_name = 'John');

And make sure you have indexes on the keys

@pciranda Thank you for taking the time to look through this and provide an example. The results are against a user exactly how you have it shown.

The compliance is a bit tricky because it is associated with a user through a "role" that they are given (a user only has one role). I can probably figure out how to fetch the compliance through their role and add it to the user model that way. Then try and work with your example and see how that works out.

@tray2 thank you for looking at this. I have heard that pushing the work onto the DB speeds things up but I am not that experienced with SQL. One of the great things with Laravel is that it can allow you to use it's helpers but I totally understand that this is not always the best approach. I'll have a look into experimenting with SQL and see how I get on. Do you have any resources worth looking at?

The new SQL course here is a good start. https://laracasts.com/series/mysql-database-design

I don't really have any other good resources.

Couldn't you simply move the get() to your initial variables and pass the resulting collection into the map so that you are simply filtering the collection on each pass instead of passing a query into the map function and running the query on each pass?

@tray2 , Yep.

The limit is 65536 (2¹⁶) placeholers for prepare the statement.

You can bypass this Mysql limit. https://www.php.net/manual/pt_BR/pdo.setattribute.php

DB::connection()->getPdo()->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

@Xsecrets, this is a god tip.

$compliances = Compliance::select(....)
    ... // all the stuff
    ->get()
    ->groupBy('user_id');

$results = Result::select =  Result::select('course_id',....)
    ... // all the stuff
    ->get()
    ->groupBy('user_id');

$reportUsers->transform(function($user) use ($compliances, $results){
    $user->results = $results[$user->is]];
    $user->compliances = $compliances[$user->is]];
    return $user;
});

@pciranda @xsecrets running a get() on my $compliance select causes it to just time out and crash, any ideas why that might happen?

@pciranda can I ask why you have added ->groupBy('user_id'); on the end of both results and compliance?

install the Telescope https://laravel.com/docs/5.8/telescope

Or Laravel debug bar https://github.com/barryvdh/laravel-debugbar

add a ->limit (10) to each query to prevent get much data.

See the generated queries and post here.

About the ->groupBy('user_id');

$results = collect([
    ['user_id'=> 1, 'course_id' => 1, 'passed'=> true, 'score' => 100],
    ['user_id'=> 1, 'course_id' => 2, 'passed'=> true, 'score' => 80],
    ['user_id'=> 1, 'course_id' => 3, 'passed'=> true, 'score' => 60],
    ['user_id'=> 2, 'course_id' => 1, 'passed'=> true, 'score' => 40],
    ['user_id'=> 2, 'course_id' => 2, 'passed'=> true, 'score' => 20],
    ['user_id'=> 2, 'course_id' => 3, 'passed'=> true, 'score' => 0]
]);

$resultGroupByUserID = $result->groupBy('user_id');
// will be this.
[
   1 => [
      ["user_id" => 1,"course_id" => 1,"passed" => true,"score" => 100],
      ["user_id" => 1,"course_id" => 2,"passed" => true,"score" => 80],
      ["user_id" => 1,"course_id" => 3,"passed" => true,"score" => 60],
    ],
    2 => [
      ["user_id" => 2,"course_id" => 1,"passed" => true,"score" => 40,],
      ["user_id" => 2,"course_id" => 2,"passed" => true,"score" => 20,],
      ["user_id" => 2,"course_id" => 3,"passed" => true,"score" => 0,],
    ],
 ]

// if you user $user->id = 1
$resultGroupByUserID[ $user->id ];
// will be this.
[
      ["user_id" => 1,"course_id" => 1,"passed" => true,"score" => 100],
      ["user_id" => 1,"course_id" => 2,"passed" => true,"score" => 80],
      ["user_id" => 1,"course_id" => 3,"passed" => true,"score" => 60],
]

i will back soon. after 21h gmt-3 https://time.is/GMT-3

@pciranda ok so I have now got my results and compliance working using your example, I think there may have been a couple of typos in your transform method? $results[$user->is]]; should this not be $results[$user->id]; and the same for compliance. Here is my code, but this now fails with a different error:

Undefined offset: 13120 which I believe is to do with the array indexes not matching?

$reportUsers->transform(function($user) use ($compliance, $results){
    $user->results = $results[$user->id];
    $user->compliance = $compliance[$user->id];
    return $user;
});

Tray2 is correct. DB is faster. Partly because it is easier to upload in batches. (Batch programming and chunking etc is usually faster than iterating through each record). SQL is an easy language to learn, and worth the time.

@garethredfern, It is because one of the users has no results or compliances. change to.

$user['results'] = $results[$user->id] ?? [];
$user['compilances'] = $compilances[$user->id] ?? [];

Thank you @pciranda that works now! I am now down to 56 queries but it's still not loading on large data sets which is nothing to do with your code and help - I think I will have to look how I can push it onto the DB - I can make the compliance and results queries. It's how I piece it all together to give out the final data structured in a way I can use it using json. My final structure needs to be like this:

        foreach ($roleIds as $roleId) {
            $data = $reportUsers->where('role_id', $roleId);
            $matrixData[] = [
                'role_id' => $roleId,
                'role' => Role::find($roleId)->name,
                'userCount' => $data->count(),
                'matrixData' => $data->values(),
            ];
        }
        return $matrixData;

How I do all the filters and loops in SQL is going to be fun part. Thank you for your help I should mark your answer as the correct one as it did reduce all the queries, also thank you to @xsecrets for your suggestion which gave the final answer.

@tagifind thank you, yes if you look at the other answers and @pciranda solution that's what I am now doing and its much quicker.

I am Agree with this comment

install the Telescope https://laravel.com/docs/5.8/telescope

Or Laravel debug bar https://github.com/barryvdh/laravel-debugbar

add a ->limit (10) to each query to prevent get much data.

See the generated queries and post here.

About the ->groupBy('user_id');

http://avgcleanerproapk.mystrikingly.com/

https://avgcleanerproapk.hatenablog.com/

https://avgcleanerproapk.puzl.com/