N+1 query throught multiple tables

Hi all,

I'm having a N+1 query issue between a relationship of 3 tables.

[2021-06-01 10:41:02] local.INFO: Detected N+1 Query  
[2021-06-01 10:41:02] local.INFO: Model: App\User
Relation: App\Dealer
Num-Called: 13
Call-Stack:

I've got a table called User and a Dealer table. The relationship between both is AuthorisedDealers with the following structure:

id
user_id
dealer_id
created_at
.... and other fields

I've done the following query

user::with(['authorisedDealers'])->where(...)->get();

And, in my loop:

foreach ($user->authorisedDealers as $dealer) {
    $authorisedDealers[] = $dealer->cod;
}

Here I'm having the N+1 problem and, honestly I don't know how to solve it, I've tried with

user::with(['authorisedDealers.dealer'])->where(...)->get();

and so on, but I've got an error in the query that says that it's not able to found the table.

I don't know if I've to change the table "authorisedDealers" to "userDealers"

In the User model I've got the following relationship:

    public function authorisedDealers(): BelongsToMany
    {
        return $this->belongsToMany(Dealer::class, 'authorised_dealers', 'user_id', 'dealer_id');
    }

Do you have any clue? I'm a bit stucked with this issue, I'm working right now doing performance tasks.

Many thanks in advance.

tykus

5 years ago

Level 104

I've got an error in the query that says that it's not able to found the table

Which table does it complain about?

I expect because you are incorrectly using the 'authorisedDealers.dealer' eager-loading syntax, Laravel is trying to eager-load a dealer relation on the Dealer model? You should be aware that the authorisedDealer relationship loads a Collection of Dealer instances, so a nested dealer relationship makes no sense.

Where does the callstack point; what is the source of the N+1 issue?

j.avi

5 years ago

Level 1

Hi, this is the output log for the N+1 issue:

[2021-06-01 16:39:29] local.INFO: Detected N+1 Query  
[2021-06-01 16:39:29] local.INFO: Model: App\User
Relation: App\Dealer
Num-Called: 13
Call-Stack:
#16 /app/Repositories/UserRepository.php:87
#17 /app/Services/UserService.php:102
#18 /app/Services/NotificationService.php:51
#19 /app/Services/Imports/StockImportService.php:259

The code in the row 87 of UserRepository is

public function findByShowNotifications(): ?Collection
{
	return $this->user::with(['authorisedDealers'])->where(....)->get();
}

The code executed calling the function is:

$users = $this->userService->findByShowNotifications();

foreach ($users as $user) {
  $auth = array();
  foreach ($user->authorisedDealers as $dealer) {
     $auth[] = $dealer->cod;
  }
...

The relationships defined in the models are:

class User extends Authenticatable
{
    public function authorisedDealers(): BelongsToMany
    {
        return $this->belongsToMany(Dealer::class, 'authorised_dealers', 'user_id', 'dealer_id');
    }
}

class Dealer extends Model
{
}

class AuthorisedDealers extends Model
{
    public function dealer(): BelongsTo
    {
        return $this->belongsTo(Dealer::class, 'dealer_id', 'id');
    }

    public function user(): BelongsTo
    {
        return $this->belongsTo(User::class, 'user_id', 'id');
    }
}

Hoping this helps you with more information.

Snapey

5 years ago

Level 122

You do this

user::with(['authorisedDealers'])->where(...)->get();

what does that get assigned to?

in the foreach loop

foreach ($user->authorisedDealers as $dealer) {

is $user in the above a child of the previous database query? Do you have an outer loop that does foreach ($users as $user) ?

Snapey

5 years ago

Level 122

Install Laravel Debugbar, it shows you each repeated query and the code line number.

Please or to participate in this conversation.