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

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              |

CodePudding user response：

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.

CodePudding user response：

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();

CodePudding user response：

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();