Get the latest record on the group in the most Eloquent way 😎
Ok, so we need the latest record of each group from the database. Or we can say we want the latest messages of each user from the messages table.
Lets say we have a messages table like:
id user_id content is_read created_at
1 1 ... 0 2018-05-23 23:00:00
2 1 ... 1 2018-05-22 23:00:003 2 ... 1 2018-05-23 23:00:00
4 2 ... 0 2018-05-22 23:00:005 3 ... 0 2018-05-23 23:00:00
6 3 ... 1 2018-05-22 23:00:00
And our end result should contain following:
id user_id content is_read created_at
1 1 ... 0 2018-05-23 23:00:003 2 ... 1 2018-05-23 23:00:005 3 ... 0 2018-05-23 23:00:00
In MySQL, most probably we will write the query as:
And in Laravel it will be written as:
But the problems with the raw queries are:
- It’s a raw query 😐.
- Hard to add conditional
where(). - Not able to use super helpful
whereHas(). - Global Scopes will not be added to the query 😭.
So to solve all these problems we need to come up with a query which uses Eloquent query builder and then convert it to raw SQL statement. For this, we will use the toSql() method as:
And what if we have bindings of sub-query which we want to add in parent query. For this, we can use addBinding() method.
Our updated query will be like this:
This will add the bindings of sub-query to the parent’s join query.
Now we are able to use all the power of eloquent for writing the complex queries 😎.
The Same practice of creating the query in eloquent first and then converting it into raw SQL can be followed in other situations also.
Let me know if you find this post helpful in the comment section or you have any doubts related to this.
Thanks for reading 😇.
