Eloquent Many-To-Many: Simple Query Example

hello guys today i want to demonstrate you how to write one sql query and there was a question on my youtube post when i asked any questions about eloquent performance when i was preparing the eloquent performance course and one of the questions was from mahmoud about specific situation where they have resources and partners and many-to-many relationships and how to query some data and i didn't include that answer as a video in the course because it's not specifically about eloquent performance it's just about how to write a query with many-to-many relationships but i asked my colleague nerius to simulate that and create a demo project and here we go so in the database we have project and resource management relationship resources table is this one so partners it may be partners it may be consultants so additional external resources people and the projects belong to users so there are three users seated three projects some many-to-many relationship and the goal is to get the projects by a certain user with the resources that they worked with and specifically filtering resources as title partner the result the visual thing looks like this so by projects with user id 1 we have project 1 and then for each of the partner we do the list comma separated and here's how the query looks so this is the controller invoked by user id invoke by the way is if you have a controller with one method you can just call it invoke and in the web routes web file you just can specify the controller without any method so that was by the way and here's the query so we get the projects where user id equals this parameter then we check where has resources resources is a many-to-many relationship project resources belongs to many and then we filter where has and then where condition by resources.title equals partner and then in the blade in home blade we have something like this so for each of the projects and then for each that's another column for each of the project resources and here's one more trick this one so there is a variable called loop which is used in for each loop of blade variables of blade structure of blade syntax and it has a lot of things that you can use if loop first if loop last loop iteration loop index stuff like that if you go to the documentation of blade templates there's loops section and also separately the loop variable a few examples and the full table of what properties can you use on the loop so this is how we get comma separator except for the last item one important note here is how to avoid the n plus one query too many queries here because it is easy to overlook this thing so you may get projects and your goal is to get projects and the queries actually i've introduced laravel debug bar installed on the project there are two queries so we get the projects and then we get the resources by those projects where project id is in one or two so this is done by with resources and if you comment that out and we refresh the page we have three queries instead of two so it gets the resources by project id one done by project id 2 and if there are more projects it would take project id 3 and so on so that would be n plus 1 query so to avoid that don't forget to always load the relationships that you actually will use so with resources we are using that in the blade with project resources that's why we need to eager load it's called eager loading from the controller of course there are other ways how to write that query so this is totally eloquent way but you can do it with query builder using joins and you can measure what is faster or more convenient and i talked about that with a few examples in my course so if you want to dive deeper in eloquent performance course i have a few lessons so eloquent versus query builder versus join and i have two examples where in one case where has is worse in other case where has is better in terms of performance so you can watch those if you purchase the course better eloquent performance the link will be in the description below if you want to dive deeper again but for this short video that's all i wanted to show to answer the question by mahmoud and from time to time i do pick your questions from youtube comments to simulate some situations that would be beneficial to other members of the audience so be active in the comments and see you guys in other videos

Channel: Laravel Daily

Views: 7,161

Keywords:

Id: jKRqFo3rk5k

Length: 4min 14sec (254 seconds)

Published: Mon Nov 08 2021