Level 39
Where is your N+1 issue?
Jul 14, 2022
28
Level 8
Excel Import, N+1 Issue
Hi, I need some help: I am using simple-excel package from spatie and I am able to import an excel document to the DB, the only caveat is that I am experiencing the N+1 problem when querying the import. Below is the store method of my controller:
public function store(Request $request)
{
$this->validate($request, [
'import_file' => 'required|mimes:xls,xlsx,csv'
]);
SimpleExcelReader::create($request->file('import_file'), 'xlsx')->getRows()
->each(function(array $rowProperties) {
$user= User::where('name', 'like', '%'. $rowProperties['username'].'%')->first();
Department::create([
'user_id' => $user->id,
'name' => $rowProperties['name'],
'code' => $rowProperties['code']
]);
});
return back()->with('success', 'All good!');
}
Kindly help me rewrite the code $user= User::where('name', 'like', '%'. $rowProperties['username'].'%')->first(); in a more effecient way.
Level 8
@sr57 When I import, the 175 users, it executes 405 queries.
Level 122
You might be able to read all users into memory to avoid doing a search on every insert, but it depends how big the users table is
Level 8
@Snapey could you kindly elaborate? I have 175 users
Level 29
$user= User::where('name', 'like', '%'. $rowProperties['username'].'%')->first();
The username is unique in the file?
Level 122
how often are you going to run this? - does it really matter?
1 like
Level 8
@Snapey Not frequently by the way. Thank you for this point of view.
Level 39
When I import, the 175 users, it executes 405 queries.
Where do you import the 175 users?
How many lines in your excel file?
Level 8
@sr57 176 rows, one for the header.
Level 39
So no N+1 pb, your code is correct regarding the number of queries.
1 like
Level 8
Level 39
Right , 175 is a small number.
Level 102
What is supposed to happen if this returns no results? Or a wrong match?
$user= User::where('name', 'like', '%'. $rowProperties['username'].'%')->first();
Let's say the file has the username "ra". Then it will just take the first user in the database that has "ra" anywhere in the username
Also, name and username is normally different things yet in your code they are the same?
Level 8
@Sinnbeck yes they are exactly the same.
Level 102
@Brammah but no user in the system has a name/username that is a part of another's? Do you understand what I'm pointing out? :)
Level 8
@Sinnbeck if it returns no result, on the $user->id, i can rewrite it to $user->id ?? null;
Level 102
@Brammah it will already be null. So if that's allowed, then that part is good
1 like
Level 8
@Sinnbeck Maybe I can rename the username in the excel file to read name and move the columns around.. :)
Level 102
@Brammah it's completely up to you. I don't know your database etc :)
I am mostly just pointing out that you code might find a wrong user
Level 8
@Sinnbeck True, true.
Level 102
@Brammah maybe matching on email would be safer? Or exact username match?
Level 8
{
Schema::create('users', function (Blueprint $table) {
$table->id();
$table->string('name');
$table->string('username')->unique();
$table->string('email')->unique();
$table->string('phone');
$table->string('code')->unique();
$table->string('gender');
$table->string('password');
$table->string('status')->default('active');
$table->rememberToken();
$table->timestamps();
});
}```
Level 102
@Brammah so you have both username and name. But you use the username column in the excel file to compare to the name column (not username) in the database?
Did you mean to do
$user= User::where('username', '=', $rowProperties['username'])->first();
Level 8
@Sinnbeck No, the code is okay
Level 102
@Brammah also the 'like' part? Did you get why that can be a problem?
Level 8
Level 102
@Brammah yeah those are the answers for the question and are therefor best. I was only pointing out a potential problem with your code, which was unrelated to the question :)
Level 8
@Sinnbeck Ooh, okay. I will look into it and refactor it.
Please or to participate in this conversation.