Level 12
@marrsgreen Have you tried upgrading your query builder with Eloquent? Eloquent provides you with the default functions for the requirement you're currently having.
Oct 23, 2023
6
Level 1
Query Builder count
Hi guys help me a little! I use laravel Query Builder. I have posts and replies table. I need total replies in the post(not use get(), use first() or find()). Is it possible or not.
$post= DB::table('posts')
->leftJoin('replies', 'posts.id', '=', 'replies.post_id')
->select(
'posts.id',
'posts.slug',
'posts.title',
'posts.body',
'posts.created_at',
DB::raw('COUNT(replies.id) AS total_replies'),
)
->where('posts.id', $post->id)->first();
Level 1
@DhPandya But I use only Query Builder
Level 12
@MarrsGreen In that case consider using groupBy with the table which you're joining with.
groupBy('replies.post_id').
Level 1
@DhPandya but not working
Level 12
@MarrsGreen Add your builder code here.
Level 6
yes, it is possible.
your code only have a small issue, just remove the comma after `total_replies'
use this as reference
$post= DB::table('posts')
->leftJoin('replies', 'posts.id', '=', 'replies.post_id')
->select(
'posts.id',
'posts.slug',
'posts.title',
'posts.body',
'posts.created_at',
DB::raw('COUNT(replies.id) AS total_replies')
)
->where('posts.id', $post->id)->first();
the result may like this
-------------------------------------------------------------------------------------
| ID | Slug | Title | Body | Created At | Total Replies |
-------------------------------------------------------------------------------------
| 1 | example-post | Example Post | post 1. | 2023-10-23 14:30:00 | 5 |
| 2 | sample-slug | Sample Title | post 2. | 2023-10-24 15:30:00 | 7 |
| 3 | test-slug | Test Title | post 3. | 2023-10-25 16:30:00 | 2 |
| 4 | demo-slug | Demo Title | post 4. | 2023-10-26 17:30:00 | 9 |
| 5 | another-slug | Another Title | post 5. | 2023-10-27 18:30:00 | 3 |
| 6 | post-slug | Post Title | post 6. | 2023-10-28 19:30:00 | 8 |
| 7 | new-slug | New Title | post 7. | 2023-10-29 20:30:00 | 4 |
| 8 | fresh-slug | Fresh Title | post 8. | 2023-10-30 21:30:00 | 6 |
| 9 | latest-slug | Latest Title | post 9. | 2023-10-31 22:30:00 | 1 |
| 10 | final-slug | Final Title | post 10.| 2023-11-01 23:30:00 | 10 |
-------------------------------------------------------------------------------------
Please or to participate in this conversation.