Eloquent: Query 3-Level Relations with hasManyThrough and withCount

hello guys today's video will be another case of helping someone on laracast forum to group the data and work with eloquent because i'm a fan of practical learning so let's learn on that practical example from a real project in short the task is to query the relationship three levels deep and i have already answered on laracast and i will link that in the description of this video the link to the post itself but now i will summarize so for that nilo leon for that person they have model customers then customer has many claims it's probably some kind of insurance project then claims model every claim may have many refunds so i imagine person claiming something with a lot of refunds potentially and then every refund may belong to service with belongs to many relationship so for example the refund is hundred dollars for service one is twenty dollars for service to it's a full refund or something like that so pretty realistic scenario now the task is to write a query of all customers that have successful refunds for a certain service so it's querying the customer so level one querying that by condition with level four relationship and there are two tables between those how to query that and as usual there are multiple ways to do that so i will present my own way what i came up with after 30 minutes of playing around with eloquent queries and i have recreated that database in a simplified way missing a lot of columns but keeping the relationship columns the main ones with customers having clean tech liente is probably customer number or something then there are claims which belongs to a customer then there are refunds belong to claim and then there are services with the pivot table of refund service probably it would be better to understand visually so i've created mysql workbench chart or database schema visually so this is how it looks probably you will understand it a bit better so customers have many claims claim has many refunds and then refunds may have multiple services with a pivot table and our task is to query the client of customers filtering by service id by this id or by this service id so if i had to write plain sql query for that it will be customers joy enjoying join wear and quite a lot of complexity and let's see what i came up with this is the controller code and it looks like a pretty short way to execute that long sql query right but there are quite a lot of things happening under the hood so let me explain we are querying customers with only one field cli and 10 with count of the refunds and we are filtering only those customers that have refunds service with id1 so two things here filter out wrong customers and return the amount of refunds for each of them but there was one additional change to eloquent model that i have done to achieve that the relationship goes customers claims refunds services and in this query there are no claims mentioned right so where have they gone so in addition to the relationship customer claims i did another one has many threw off refunds in eloquent there's a function has many through so you can create a relationship two levels deep so customers have many refunds through the model claims i will add the article to the official documentation in laravel about has many through in the description of this video the main reason to create that function was that it was impossible to use with count with a relationship two levels deep so if you do claims refunds it wouldn't work it would show this error undefined method but if you have this with has many through it works and i will also link the with count documentation in the description as well and the result of that as you can see for each statement with count refunds turns into refunds underscore count automatically returned and if we launch that in a browser we see something like this so client with number 56789 have two refunds with service one and this one has one refund i'm not sure if you noticed customers has three records so there are three customers and one of them is filtered out so all three of them have claims with customer id one two or three and then all of those claims have refunds but only three of those refunds have service id one which is exactly what we're filtering by and if we trace back those three two customers this is exactly what we get two here and one here and just out of curiosity i've installed laravel debug bar to see what queries executed under the hood so if we take a look here let's see select client from customers joining the claims and refunds and selecting the count as refunds count and also checking where exists and that where exists has a sub query and exists because it's two or three levels deep so it's pretty complex and maybe it can be optimized even more because some parts i see they are repeating so maybe it's better let's actually experiment let's remove that refresh our page we should have more simple query but now that one customer isn't filtered out so let's try to load that with count because with count can accept a parameter so if we do refunds and then have a callback function i thought i prepared this video beforehand but now as i'm talking i will try to refactor that even better in live mode kind of so we can put where has here on query where has comment that like this and probably this one isn't needed and let's see what we have now as you can see now it has zero so what we're left to do here is to filter that out but we need to do that after the get because refunds count is a calculatable field not directly existing in the database so instead of doing where here we need to do that after that so where refunds count bigger than zero and now if we refresh we're back to the same result but with more simple query so we change the last filtering of 0 or not 0 from eloquent query to the collection filtering it would be pretty similar as doing that if so if customer refunds count is bigger than zero then do that and then we can comment that get and do the same result but i'm just doing that directly on the collection with where statement let's do undo undo undo and this is our final version what do you think about my filtering did you like the first version or the second version better and maybe have more tips to optimize that even more share in the comments below because as i said there are multiple ways to query the database in eloquent and collections and all of that so let's all learn laravel and eloquent together by sharing tips to each other and i will continue doing that on this youtube channel so you should subscribe and tell your friends to subscribe and i will keep shooting the videos almost daily now see you guys in other videos

Channel: Laravel Daily

Views: 16,237

Keywords:

Id: kPFG5Dw63qI

Length: 7min 36sec (456 seconds)

Published: Thu Aug 20 2020