Eloquent Practice: Group By Raw and HasMany

hello guys today i will answer an email this one that you can see on the screen with a question about eloquent and i'm still surprised that eloquent is almost number one topic of questions that i get over social media and my email but okay if you have questions let's answer them so the situation there are posts and there are post likes with has many and what is needed here is to show the posts by date order by date descending let's assume it's post created at it could be like published at or something but it doesn't really say in this email anyway so date but not the time stamp it's date so that's one challenge and then for each date order the post by the amount of likes which is has many relationship so i've tried to recreate that scenario in my local demo project so here is the situation in the database 20 fake posts with random created ad and here's what we will group and order by and then there are post likes with just post id it doesn't really matter what's the username or whatever so post has many likes and the result of that on the screen would be something and the result of that on the screen would be something like this the first result we will transform it as well but order by date descending then the amount of flags in this case it's the same amount but then 22nd of november 4 and 1 likes then these 854 so ordered exactly as the email so how to achieve that in the controller for now i just have a placeholder and we will fill the actual code now and here's the task described we need to select title and likes count order by created add descending date and grouped by that so first likes count how do we define likes count if we go to post model lives has many and for that eloquent has with count with count of likes the relationship name get and then we can order by that with count let's do order by likes underscore count descending so that would order by likes not the dates yet but that is a step one next we need to order by date descending by created add and date for that we would do before the likes count we do another order by but not the created ad but date created at descending and here we'll have order by raw we can do raw query on ordering as well next what we need to select for that query select and here we'll also use raw select raw we need id title date created add and it seems like that's it and also since we see date created ad here repeating we can define that as a variable as created date let's put with count on a new line so create a date and then we can use order by without this and refer to created date as an eloquent property almost so this sounds like what we need and then in the blade we do for each of the posts show title show create a date and show likes count let's refresh the page we refresh and this is the result and i've installed laravel debug bar to see what actual elect query is running under the hood so this is almost like a row query so we select exactly what we specified in the controller that with count is a sub query so select count from post likes and we can use that likes count later in the ordering order by descending and there's one more thing i would like to improve here in this view this is one list so it's not really clear what is the difference of the dates so wouldn't it be better to have date and then post of that date then another date and then posts of that date with likes so we can group by created date and we can group it in a collection so there's group by before the get which is query builder but there's also group by after the get so group by created date gives us the result let's dd or actually did you know that there's dd on the collection as well dd die and dump and if we refresh the page we have that array and here's the key of that array which is create a date and for every key we have a collection and that collection is for example two items of posts so now in the blade if we remove the dd in the blade we can add another four each so for each dates for example as date posts then we add for example date here the key then the list and then we end for each author format with phpstorm and then we need to pass the dates so it's not the post anymore it's the dates so we will name the variable as well and if we refresh now we have as you can see date and then post and then it doesn't really matter what is here so we can remove this variable which will make it look even better so like this i forgot this one so six likes something like this this is the final result i hope it helps that person who sent that email and also if you want to find out about eloquent more more tricks and tips like this one i have quite a lot of lessons about eloquent and separate courses about eloquent on my teachable platform so there's better eloquent performance course there's a course about structuring databases and also i touch eloquent in almost any course where i get some queries from the database so the best way to practice eloquent is to subscribe to yearly membership of my courses which will get you 23 current courses and everything i will release for year ahead i have a lot of plans for new courses with new ideas some of them from you guys so see you guys inside the courses or on free videos on youtube

Channel: Laravel Daily

Views: 3,914

Keywords:

Id: Hm55hFTKvtQ

Length: 5min 49sec (349 seconds)

Published: Thu Nov 25 2021