PHP Laravel subquery in select statement example

---

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"

and "post_viewer". I have to simple count viewer post vise. So i have structure was like as bellow:

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....

---

July 9, 2017 | Category : PHPLaravel

---

Do you like bellow Tutorials ?