How to get total number of rows, and latest row's value in MySql?

user_8309730 0
Viewed 67

I have two tables named Posts and Comments.

Posts table looks like.

|id|user_id|content|created_at|updated_at|
|1 | 24    |demotxt|demo_date |demo_date |
|2 | 21    |domotxt|demo_date2|demo_date2|
|3 | 24    |domotxt|demo_date2|demo_date3|
|4 | 28    |dimotxt|demo_date3|demo_date5|

Comments table looks like

|id|user_id|post_id|comment |created_at|updated_at|
|1 | 24    |  3    |comment1|demo_date |demo_date |
|2 | 21    |  3    |xyadbsss|demo_date2|demo_date2|
|3 | 24    |  1    |okayokay|demo_date2|demo_date3|
|4 | 28    |  4    |somehtin|demo_date3|demo_date5|

What I am trying to achieve is to get first latest comment and total number of comments for each post. i.e.

|post_id|latest_comment |total_comments |
| 3     |xyadbsss       |2              |
| 1     |okayokay       |1              |
| 4     |somehtin       |1              |

This is the sql query I have tried

SELECT post_id, count(post_id) total_comments, comment latest_comment
FROM `comments` 
  LEFT JOIN posts on comments.post_id = posts.id
GROUP BY post_id;

Which is giving me

|post_id|latest_comment |total_comments |
| 3     |comment1[not latest]|2         |
| 1     |okayokay       |1              |
| 4     |somehtin       |1              |
laravel mysql sql
3 Answers

Can you try something like this, not sure if is best solution.

SELECT 
c1.post_id,
count(c1.`post_id`) total_comments,
(
    SELECT 
        c2.comment
    FROM
        comments as c2
    WHERE
        c2.post_id = c1.`post_id`
    ORDER BY 
        c2.id DESC LIMIT 1
) as latest_comment
FROM 
`comments` as c1
GROUP BY 
c1.`post_id`;

I would rather loop the first data and do another query based on post id to get latest comment.

user_173131050

To get the latest record in database you can use this

Model::latest()->first();

So in your case to get the latest comment:

Comment::latest()->first()['comment'];

You can also use this method depending of your laravel version (if you use 5.7 or higher):

DB::table('comments')->latest('comment')->first();

Now to count all comments on a post you can looking for all comments with the post id and use the count property

$count = Comment::where('post_id',$the_id_of_the_post)->count();
user_174457400

One way to achieve this would be to use the latestOfMany method. Add the following to your Post model:

public function latestComment()
{
    return $this->hasOne(Comment::class)->latestOfMany();
}

Then you can eager load the results:

$posts = Post::with('latestComment')->withCount('comments as total_comments')->get();
user_10336540
Related