Laravel Advanced - Many To Many Relationship

hey what's up guys welcome to live tips in today's video we'll be talking about many to many relationship in laravel in my previous video i have discussed about has one and has many relationship so if you haven't watched that i would highly recommend you to watch those videos so this many-to-many relationship is a little bit more complicated than that of the has one and has many but not that much so when do we need this many-to-many relationship let's say there is a users and rules table and a user can have many roles and the same role can be applied to the many users basically a user is can be related to the multiple rows and a role can be related to the multiple users so in that scenario we need this many-to-many relationship so to define the many-to-many relationship uh there is a rule in the level how to define the table name it is not a must that you must follow that convention but if you follow the convention it will be easier to write the relationship okay let's say we have a users and a rules table and for that we need many to many relationship for that we need to define a thought table which is known as a pivot table and for the users and rows the pivot table should be role underscore user so the naming of this role user would be combination of these users and rules table in an alphabetical manner and in a singular words so let's say this is users and rules table then the r comes first and u comes after the r in the alphabet in the alphabets yeah so the table names would be role underscore user and the role underscore user must have the user underscore id and role underscore id as a foreign key okay now if i go into the code then i have already created those tables so i had created the role model so you can see the role migration here so i have only added a name field and here the role underscore users table so here we have the user underscore id and role underscore id i have also added this expires at extra column so that i would be able to show you how to store data when we have this extra field using the relationship now if i go in the database there is this there are five users and there are four roles but if i go inside this role underscore user so there is no data so we'll be adding the data using the relationship okay now if we go in the documentation here so let's see how to define the mini too many relationship in the models here okay now let's go to the users user model and here a user can have many roles so let us define a public function as roles and we have to return here a relationship method so that would be this belongs to many role class like this so we have returned or belongs to many relationship from here so for a mini to many relationship there is a method known as belongs to many relationship in laravel so we have to return this so since we have followed the convention while creating the table name so we should not pass anything here in this other parameters so if i show you here in the database so what we can do is we can also pass multiple parameters here since but we have followed the convention so we don't need so if we didn't follow the convention then we can pass the parameters over here and let's say so we have this rule user table by convention let's say we want this table to be role underscore users so laravel will not know about this so we can pass the second parameter as the table name so we can pass it like this and laravel will know about it okay and also the third parameter would be the user underscore id and last parameter would be role underscore id so whatever we have here in the relationship that foreign key would be in the last so let's see in the rule users tables let's say we have this here user id as a author id then we have to pass here author id and if we had any other thing in here except the role id then we have to pass here but let's follow the convention now let me all do all the changes we don't need any of those extra parameters here but conventional arable will automatically assume those fields for us okay now we have seen here a user can have many many roles but a role can also have multiple users so if we go in the roles then what we can do is here public form we can define the relationship like this pu public function and users return so the relationship name is same here belongs to many now here we have to pass user instead of rows yeah because our role belongs to many users and a user can belongs to moon more belongs to many rows so here also we don't need to pass any parameters since we are following the convention so you have seen this now how can we access the relationship so it is similar to that i have shown in my previous relationship videos you can just access it as a normal property in this model so before that let us see how to save data using relationship so if we go here in the documentation and see here inserting and updating data in many too many relationship let's go here now let me open the tinker wheel here and let's see how to assign a role to a user now let us find a user with the id of one so if i run this we will see the user with the id of one and now if we go in the documentation so we have a relationship name as rules yeah and we can attach a role like this so if we see here in the database we have roles with the ids of one two three and four we can attach a relationship or roll to a user like this user roles attach and we have to give the id of the role that we need to attach to the user so if i do it like this and if i run here it is returning a null yeah but if we go here and see in the database here in the role underscore user and refresh here now you can see here it is attaching roll 1 roll with the id of one to the user with the id of one but all these fields are null yeah why is this null because we haven't while creating a relationship we haven't passed here another extra data so laravel by default uh convention it knows that in the keyboard table there is a user underscore id and a role underscore id but it doesn't know anything other than that so for that we have to define it here like this so what we can say is with pivot and we have to give the names of the extra columns other than that of the user id and role id so we have here expires at expires at and also we have this created at an updated ad for that laravel has a method for that what we can do is with time stamps so it will add created ad and updated at here it will match this and we can also do similar thing here in the rose model now if i go in the tinker well and if i attach the second row then if i run this code and go in the database and here let me refresh now you can see here created add and updated address field since it will automatically be filled by the laravel but the expires at field is still known because we haven't passed anything while saving it from here in the tinker world now how to do that so for that what we can do is pass data as an array so let us say we want to attach row 3 to the user with expires at 2v expires add to b now so this is a carbon helper which will give us the current time and when saving the label will automatically convert it into the timestamp so now if i run this command here and if i go to the database here and if i refresh now you can see here it is adding the current time step so all of these are having the same timestamp let's say we want it to be anywhere let's say after a year now what we can do is now add here like this so to know more about this you can learn more about the carbon for which i'll be leaving a link in the description below that like button okay now if i do it like this and give it a roll for let us assign a user the role for and that will expire after a year now if i execute this code and if i go here in the database now you can see here it is adding a one year after so before it was 20 20 and now here is 2021. so in this way we can save many too many relationship so this is one way of saving so whenever we add let's say now here is a four roll four yeah and again if i run this code then it will again add another row you can see here it is again adding the another row so and the same user has the same role twice so this is occurring twice so for that we have another methods to store the data in the database now let's see one method first which is sync which will accept array now let's say we want to attach a rule with the one id one and the expires add app will be now add months let's say six now if i do it like this then it will save it will attach a role to the user with the id one but it will remove all the other rules so there will be only one role that will be attached to the user and we have attached these multiple rows here they will all be removed and there will be only this one so if i run this code here so you can see here it is it has said no new has been attached because the role with the id1 was already attached before so it is not attaching a new one but it will update this expires add field similarly it is showing that it has detached all those rows with the id other than one and you can see here updated is one which is this one so now if i go here all should be removed and we should only have this rule id1 and this expires at must have been updated so if i reload here now you can see here the expires add field is filled and this is null because previously when creating this field we didn't apply that with timestamps so this is not here so let me just copy it and paste it here okay now this is this is how the sync works so now let's say if we want to attach this user the second rule and if i say refresh here now you see i'll just refresh here now you can see here the one rule is again removed and we have only two yeah now we let's say we don't want this behavior we want to keep the previous roles and we want to add this along with them now what we can do is sync without detaching now we want to add a rule three if i run this code and if i go here and refresh both this should be here now you can see here both of these rows are available here so the difference between attach and sync without detaching is that attach will always add a new row to the database table but sync without detaching will look that if there is a previous row with the same rule id then update that row otherwise create a new one so that's the main difference between attach and sync without detaching now let's see how to detach data from the pivots table or detach the rules of the user that is pretty simple so let's say we need we want to detach the role three so we can basically say dash three this is the id role id and if i go here in the data first let me execute this and now if i go ahead and refresh so this row should be gone now now you can see here that row has been detached now if you want to remove multiple rows then you can pass here array and currently we have only this one row so let's pass only that and if i execute this code and if i go here so this row is also now removed so if we passed here let's say three four like this all those row light is then all those would also be detached now let us add some rules to the users let's say we want to give the id of two and three to the user one and let's say we want to give for the user to roll to only so if you see here in the database so you can see here a user 1 has the id rows of 2 and 3 and user 2 has only the rule with the two okay now let's see the existence of the relationship so this also works similarly that how we have seen in our previous relationship tutorial so what we'll see is do is here user has rows get what this does it it will return all the users that have at least one role now if we see here in the database here we have five users and among them only two of them have some roles yeah so it will only return these two users with id one and two now if i refresh here now you can see here it is only returning the user add with the id 1 and 2. also we have doesn't have now it will return all those remaining users which who don't have any rules so if i do like this so you can see here it is returning all the users with the id three four and five so they don't have any rules okay now let's say we want to get all the users that have the rule only two so if we go in the roles table the two is admin and three is developer and let's only find the developer users okay so they have the id of three here and if we go here the developer is only this user one yeah so now what we can do is where has rows and we can do function query where role dot name is rose dot name is so we can also do this only name here because we know that it is looking into the rules table so let's see what happens when we do this so get all the users that only have the role developer now if i do this now you can see here it is only getting this user with the id of one since the user with the id one is only developer so this so i id two is for the admin now if i say admin here and if i refresh so it will get both of these users one and two so you can say ids one and two so you can see it like this also there is where it doesn't have where it doesn't have the role admin then we'll be getting all those users that doesn't have any role as well these users these three three and three four and five as well and if we see here in the role user so whoever doesn't have roles as admin means the both of them have the role of admin so one and two will not be visible so if i refresh here you can see here three four and five is shown here now if i say well it doesn't have developer then it will return all the users that doesn't have any role as developer then it must return the four users so now you can see here all these users don't have any role so it is showing here and also user with the id 2 doesn't have the role of the developer so it is also showing here okay now let's get some users and let's see how to display them in the browser now if i go to the project here let me run the project serve and if you see here in the home controller here i have got all the users here users get and in i have passed those users in the welcome view and i have looped through all the users and i am displaying the username and also i am getting the roles of all each user and also displaying the name of the role now if i open this link in the browser now you can see here i am getting all these users and the roles of the user so first user this has the roles admin and developer and this user has only the role admin and all the other three users do not have any roles yeah so in this way you can pass the users and get the role as a property from the users and you can display them so now similar to that how what we have seen our previous video if we access the data like this then we will run into the n plus one query problem so you can see here it is getting all the users from our that users colon colon get that command and it is getting all the roles from each users and it is running one query per user so it is causing the n plus one problem so it is this code will be executed this mysql command will be executed when we access the user arrow roles so it's so easy to use using the eloquent the relationship that we will not realize that we are running into this such kind of n plus one problems here so to minimize this entrance one problem we can do is just eager loading or lazy loading as we have seen before so for that what we can do is with then give the relationship name rules like this now here you can see it is running six queries now if i refresh here now you will see here that it is only running two queries so it will get all the users and get that user's id and get the rules for each user so it will only run two ways how many users you get there will not be any problem it will always run two queries so our application will be optimized so this is the eager loading and the lazy loading would look like this like this now if i go here and again refresh the page then you'll see here that we'll only running the two queries so we have seen how to show the users and its roles now also let's see how to show that expires at field and the created ad fields that are in the pivot table so let's uh see how to show these fields now let us go here in the blade and for that what we can do is here in the role name we can give it a dash and roll p vote and we can give now the column name in the keywords table which is expires set so whenever we call this belongs to many relationship like this rules and there will be this pivot property available in the this related model yeah and it will have all those fields like this expires at created at updated ad this user id role id here everything will be present within this keyboard object so now if we do like this we want to show expires at and we also want to show role pivot created add now if i if i go in the browser and refresh the page now here now you can see here we can see this expires at and create a created ad for all those fields so we have seen how to show these keyboards also now let's see how to show only the not expired rolls in this blade now if i go here in this so all of these are in the futures table so they will not be expired so let me add let's say here in 2020 so now this role is already expired since we are currently in the october and this is in the april and this is already expired so we don't want to show this role to this user id one and all those rule they will be displayed because they are not expired so let's see how to do that so what we can do here is in the home controller so we have currently loaded the roles here so we only want to load the rules that haven't been expired for that particular user so we can do here function query so if we run any query here like where something like this this will run in the rows model yeah but now we want to run that in this pivot table then how to do that so we can just say where pivot like this and it will run this code in this pivot table so what we want here is where expires at must be greater than current date time is time so this now will be converted into the current timestamp now if i go here in the browser i should only be able to see this one role for this ill-way camera that has not been expired so if i refresh here now you can see here so the developer role is not expired for this camera and it is showing here and the admin role is already expired so if we go here and see the role 2 here and here in the roles table so you can see here this admin has already been expired and these two are not expired so we are only showing the rules that are not expired for this user okay now just before wrapping up let me show you one thing so before i showed you how to attach role to a user so we can also attach or sync multiple roles for a user so if i go here in the documentation and you can see here we can attach multiple roles for a user like this so just pass them as an array so and we can also do this using the sync and sync without detaching so we can pass the parameters in the same way like this to the sync and sync without detaching and we can save or sync multiple roles to a user so that's all about main to menu relationship and in our next video we'll be talking about this has one through relationship so that's it for this video guys thank you for watching if you want to see more videos like this then please don't forget to hit that thumbs up button and also he hit that subscribe button so thank you for watching have a great day bye

Channel: Laratips

Views: 1,844

Keywords: laravel for beginner, laravel beginner tutorial, laravel tutorial for beginners, laravel 8 tutorial, laravel 8, many to many relationship, laravel many to many relationship, laravel advanced, laravel 8 advanced, laravel advanced tutorial, laravel 8 advanced tutorial, laravel tutorial, laravel tutorial for beginners step by step, laratips

Id: _fWjvACPk4w

Length: 26min 22sec (1582 seconds)

Published: Sun Oct 18 2020