I don't remember exactly, but i was working on my laravel 5+ application 2 months ago. At that moment i require to write subquery in select statement. I simply write in my mysql query and check it's work perfect. But i though how can it possible to write using laravel query builder.
So first i am going to explain what was that. I have two tables "posts"
posts
id, name, detail, created_at, updated_at
post_viewer
id, post_id, user_id
So, i written following mysql query you can simply read.
Mysql Query:
SELECT
posts.*,
(SELECT count(*) FROM post_viewer
WHERE post_viewer.post_id = posts.id
) as total_viewer
FROM `posts`
I think now you can understand, what was the issue. So finally i found solution using DB::raw(). So let's see bellow laravel convert query.
Laravel Query Builder:
$data = \DB::table("posts")
->select("posts.*",
\DB::raw("(SELECT count(*) FROM post_viewer
WHERE post_viewer.post_id = posts.id
) as total_viewer"))
->get();
I hope you found your best solution....
Do you like below Tutorials ?
- PHP Laravel 5.5 - import export data into excel and csv using maatwebsite
- Laravel 7.x and 6.x create custom facade example from scratch
- Node JS - Get Real time online users counter using socket io example
- PHP Laravel 7.x and 6.x - Bootstrap Admin Theme Integration example from scratch
- MySQL Query - How to select records of today to next Sunday ?
- Laravel 7.x and 6.x - username custom validation example
- Laravel 5.5 - Import Extract zip file using chumper/zipper package
- PHP Laravel Set Custom Validation Error Messages Example