High performance code with laravel eloquent subquery

all right so let's talk about sub queries so in this video we'll talk about what soft queries are and why we want to use them so i'm here in homepage and i want to display all the users with their last logged in time so the use case is many for example you want to so the last logged in device and according to that you want to display some ads or whatever proper action you want to do according to their login time so in this case i want to just know which user is currently active and yeah so in our users table now we have simple user table just ignore these two fields for now and we have another table called logins so in logins table what i'm just logging in is your user id and then what ip address they are logging in from and device name so created at will be our the main uh field in this case to display last login time that's it so the relationship between users and logins is has many so one user can have many logins right so whenever they log in we are just making new entry on this table and that's it so so how do we display that we have to define so the basic uh thing that comes in mind is just define relationship logins relationship as many relationship with login class all right so that is okay now let's go to main.blade we are displaying name and last logged in at time so how do we do that so in a normal way we do it like this so user and now we have defined logins logins and we want to get the latest one by ordering them by latest and then we get first only one entry and then we show created at that's it all right now let's go to our browser and reload there we go so we can display last login date all right so in order to do that uh we have executed this many queries 37 queries so uh we have now problem of n plus one queries one query to face users and each query to face this one right so that's not good so we can reduce queries by going on our controller or whatever we are loading and just do with logins so by eager loading also we haven't solved that problem because here we are executing like this so we can do like this user logins and then get them all right so here you have to order by created ad but it is not a quite good option because if you see here so it is initializing this 946 models all right so this is a memory uh all computational it will be hard on cpu because it is doing this computational stuff or initializing this many models so 910 login models and then 36 user models all right so this normal way is uh not that performance why it's not good all right so in order to achieve same functionality with minimum resources we have sub queries so sub queries to rescue so how do we use them like this we have to call this add select method which will just give us the field name and then we run the query and then we will get that particular field which we can use that so before doing that i want to show you our users item users 36 users and if i see the individual thing we get only the fields from users table so wouldn't it be cool that uh if we get uh another entry here like last logged in at so that we can simply display that all right so in order to do that we have to use sub query so now we don't have to do this thing this will be just hard on memory so add select i'll get rid of everything for now so add select add select and then we define like this array and the key of array will be so you can give whatever name you want to give here so last login last login time you can give whatever you want just like this last login i'll give last login and then value of this key will be the query so how do we write query we'll access the login class by the way we have login class as well because we have login table right so we have this login class sorry let me inside app yeah this login class let's go back and here we we need to run query like this so we need to select whatever we want to fetch from that table table and this i will just copy it or i will just type that don't be lazy okay so we want to fetch on the created ad because we want we have this last login time inside created ad in login table and then where column so we have another uh this method where column so this will be adjust the linking column between this user and this foreign key all right so foreign key is user id and here user id we need to define like users table so this foreign key is pointing to id field on users table so sorry id so this should be the name of table and then id so we are saying add select just give us the last login time so this login time should be the value of this should be got from this login model and in login model we are just selecting created at column and that column so this column should be for this particular user right so particular user so how do we get users by seeing the user id field in users so behind this thing it will just match both the user id and only get last login field for each user so it will load through all user it will run the query to get all users and then compare them and just uh integrate or put that last login field inside each row so we'll see that in a moment where column and then we want last login right so we need to order by order by created at so order by descending right you can do order by and then descending or just order by descending same thing order by created at descending and another important thing here order by descending and then limit one limit one so one only one entry we want to get let me just call up states sidebar yeah all right so um by running this query where user id because this is has many relationships so there will be many logins for particular user but we have ordered them by descending order and then first one will be the first one will be that which we require right so that's why limit one and then finally we can call get so to make it readable you can move get on separate line and similarly you can put them on separate line like this okay there we go so now if i died on this die dump users see what happens so before there was this no user last login now if i expand that attributes we'll get last login right so this will be very useful now let's remove the item and pass that users pass all users so before we are getting 9446 models and this much memory and 37 queries now let's go to main.blade and we can get rid of this all of these and we can just say last login because we have this field right there nice login so now if i refresh see there is only one query and model before it was 900 something 996 now it is just 36. so we are just getting 36 users no login model initialization and all of the computational stuff so even we get um less memory uses yeah very useful all right so this is how we add uh sub query so you can add many sub queries here like if you want a last login device for example last login device you'll have to select the device and user id will be same and created that same thing just this device thing you need to select because here you can see device so now save it and let's display last login device last login underscore device now let's go and see there we go last login device with same number of queries same number of model same memory usages this is how you do multiple sub queries let's remove that device for now and i will show one more thing so if it is it feels too much bulky for you like in term of line shop code you can just cut this out from here and we will define with uh last login all right so with last login and then we'll do get so if we can do like this it will be quite helpful right so quite readable so in order to do that we will use query scopes so i've explained query scopes in last video and you can watch that if you want and now let's go to user model and define this let's go to user and define like this scope and then paste whatever we have copied from there query like this return it and we will have same thing see now we have make this quite readable right okay so same thing if you want to get device you will get you have to define another query scope for device if you want to do in one same thing you have to do like previously device and here also device and with last login and device you can name it whatever you want so as i have told you in last video you have to break you can break that on as many single pieces instead of just bulking up both in one yeah you can define two query scopes and then you can just change them here to get all of the fields all right so here we can get the last login time all right so that is for if you want to display last login time suppose we want to order them by last login currently it is no guarantee that it will be ordered by last login so out of these logins i want to see on top the one which is logged in latest we have latest login time so in order to do that here in route we can do like this order by order by descending all right and then here we can just pass in last login that's it there we go so someone that logged in on 350 is in fast 351st and then 342 341 333 there we go so how can we do that because we are using this query scope so by by using that select add select we have uh constructed a virtual column on our query so last login will be available as a virtual column and then now we can do order by descending so if we wouldn't have this thing here this wouldn't be working all right so column not found unknown column last login so by doing this we have partial column and it is working perfect so suppose you don't want to add select but you just want this ordering functionality order by descending functionality so same thing you need to just put that code inside here let me show you how you can do that let me comment this out so that you can see that as a reference so we don't want to add select but we just want to order them by descending all right so i will comment that out order them by descending and inside here you need to just put this sub query to make this faster i will just copy this out so now we don't need to do add select we just put the query that will order them by created created at descending there so login select this and then where this column matches and then order them by descending order that's it now if we refresh we should see the same order but we will not see this because we have removed the add select right so we should just see will uh open this on new tab so that we can compare it all right so it ordered exactly same so it is working so we should see this this web dev on last because so this it is should be on last let's verify that yeah this is in last so since uh these are just uh created on same time i have used model factory so these can be on different order no matter because these are created on exact same time so this should be on last and this second last let's verify that yeah it is web dev second last so it is ordering them by descending order whenever you need to add select just add select like this use add select if you just want to order them by just inside this order them by descending so if you want to order them by ascending order just to order by ascending and do ascending order all right so i've told you how to use sub queries how to use add select how to make them shorter by moving them onto query scopes i've also told you how to do order them by sub queries by this and we are displaying that here with add select all right so one last thing i want to share with you is suppose you want to display for example there are many column on logins table right now we are just displaying this created right so if we want to display device as well ip address and other if there are many fields so if we we want to display device as i have already told you how we can do it by doing like this device and here also device and similarly if there are many field you have to make like this so it will be cumbersome if there are many field so there is another way you can do it uh quite easy way so what we can do is by the way this is last login all right so what you can do is um you can define virtual relationship so what is the virtual relationship so normal relationship is like this we have defined logins so this is looking for user id field on sorry user id column on login login table so what we can do is uh let's define uh this and instead of last login which will just give us last login column we do like this last login id and we just select id and we get this id from there all right so after getting id we can define this thing we we define this last login relationship and this belongs to login class so now this relationship will search for id called last login last underscore login underscore id which we have partially created that column all right so as i have already told you we can create this virtual column using add select and now we can define this relationship and whenever we call this scope we are doing with last login okay so with last login and we are using with last login this is scope all right so after doing that we have access to this last login relationship so let me die and dump this user users so we have this last login relationship with all of the attributes right now you cannot see that so now if let's remove this title now if we go to this main dot blade and we can display last login time we can display device we can also display ip address ip by doing this so user and then access this last login and then create an ad similarly we can access this device let's duplicate them device ip underscore address all right so with the help of this virtual relationship we can access all the columns on login class login table let's go and refresh we should see everything and now if you notice here we just get 72 model so before it was 992 when we just do with without add select because it is only initializing the login class for 36 user not all the logins yeah so this is about virtual relationships just go through this code and you'll understand so this last part is um i know it will be hard for or to understand for some if you are quite new to laravel so basically we just added this column and defined the relationship so this video is inspired by this article if you want to read read this article you can read this on the everything is given on detail so i'll link this article as well all right so that's all about sub queries uh hope you learned something out of this video and i will see you on next video bye

Channel: WebDevMatics

Views: 6,678

Keywords:

Id: GfdqgF0OkWk

Length: 21min 40sec (1300 seconds)

Published: Wed Jul 29 2020