Laravel Excel: Import with Relationships

hello guys today i'm answering another comment on my youtube channel this time from fanny raj who asks to show how to import the relationship data using laravel excel package and i do have a video and even full course and i reply it in this comment i have a separate course on excel export import and one of the lessons is around that but it's a pretty old course and i wanted to refresh that topic also not everyone can afford my courses so in this video i will show you how to import this csv file username user email and then transaction amount and transaction description and then name an email come from a different table of users in the database we have such structure so users table and then there are transactions table and we want to import transactions but in the csv file we have name and email of a person but in the database we need to land on user id so find user id by name and email how to do that with the package laravel excel and also this video will be a general lesson for those of you who haven't tried laravel excel and how it works so we will start from the very beginning the initial example is this so simple form without any design so input field and then import button and in the code we have a create blade with the form with action and then the transaction controller has two methods create with just a form and then store we will fill from scratch so we go through the documentation of the package and first the installation composer require we paste here we install the package next we go to the import section for imports we need to create the classes so every import is laravel excel class and we need to generate that with the command php artisan make import for example transaction import and we need to add dash dash model transaction okay and now we have transaction import class transaction import and app imports folder here and all we need to do is define the fields from row of csv of the excel into transaction so transaction model has these fields user id amount and description so user id for now let's hard code it to user number one amount is row with an index of csv starting from zero so if we open that csv this is zero one two so amount is row two and description is row three like this we define the import and how do we call that in the controller we do excel with facade import then we specify the class so new transaction import like this and then which file and file we take immediately from the request which is request file with the name of that input which is import file this one like this and let's just return success message without any design because it's not the point of this video we just returned success refresh the page import the file transaction csv import okay i forgot one thing so all the fields should be fillable in the models transaction so in here in the model we need to add the fillable array fillable is user id amount and description okay and also one more thing we need to specify the header row we need to tell the excel package that our file has a header row and in our import file all we need to do is add comma with heading row that's it and then if we use the heading row we need to also change row 2 and row 3 to actually row amount and row description which is the name of the header fields row description so these ones become the indexes the keys of that row array and let's try it out again we import the file transaction csv import and we have success great so that's the first step how to import the excel in general now let's get to the relationships part so here instead of user id we would need to do something like user where name row name then where email equals row email we can do first and let's change that to a variable of user equals this user and then if there is a user we do this or no so it seems logical you're searching for user for every row and get the user id but the problem with this approach is that it fires sql query for every row of that excel sheet or csv sheet so whenever the user is needed okay one more query one more query and one more query to everything so if you have hundred rows in your csv or excel it will have hundred sql queries so a better way to do that is to implement a constructor so public function construct of that import file and then let's add this users equals actually private users and this users equals user all or in fact all we need to do is select id name and email with get and then in here we don't need to do sql query anymore we can do this users where so collection operation not eloquent operation anymore and let's write out if it works we choose the file again transactions import it's still a success but with lower amount of database queries so in general how to define the relationships in your excel import is just find the record by some parameters and a better way to do that to avoid too many sql queries is to have the collection already ready in that class and reuse that in the model function that's it if you want to find more about laravel excel import and export of course read the documentation of laravel excel package or you can go to my course excel export import as i said it's pretty old one but majority of those things should be working or you can subscribe to all courses on mylarvaldaily.teachable.com for yearly membership within that yearly membership you get access to all the 19 other courses and everything i will release for a week not for a week for a year ahead because it's a yearly membership the next course planned is about flutter and laravel api should be out in september so if you want to get those and other courses sign up for the yearly membership and also by doing that you will support this youtube channel because i will have more free time to shoot these free daily videos i hope it was helpful and see you guys in other videos

Channel: Laravel Daily

Views: 8,645

Keywords:

Id: n2WOag1G7Zg

Length: 7min 26sec (446 seconds)

Published: Fri Jul 16 2021