CODEWITHSUNDEEP
laravel
jose
jose

Reputation: 1562

Get object value on an array iteration with foreach

In the following very simplified example and study case, we get the list of posts and the count of comments for each post.

However, I intended to send to client side only the value, i.e. 2, and not the array object [{total: 2}]

Is there a simplified way to do this with the laravel?

Note: DB::select must be used

public function readPosts(Request $request) {     
    $posts = DB::select("SELECT * FROM posts");   

     foreach ($posts as $key => $post) {         
         $idComment = $post->id_comment;             
         $post->nComments = DB::select('SELECT COUNT(id_comment) As total FROM post_comments WHERE id_comment = ? ', [$idComment]); 
     }    

    if($posts){
      return response()->json(['success'=>true, "output"=>$posts);
    }else{
      return response()->json(['success'=>false]);  
    }
}

response

data:
    0:
      id_post: 1
      post: "post text"
      nComments: [{total:2}]
   1:
      id_post: 2
      post: "post text 2"
      nComments: [{total:1}]

Expected

data:
    0:
      id_post: 1
      post: "post text"
      nComments: 2
    1:
      id_post: 2
      post: "post text 2"
      nComments: 1

Upvotes: 0

Views: 76

Answers (5)

TsaiKoga
TsaiKoga

Reputation: 13394

I suggest that you can use Eloquent with eager loading to get the count. However you need to reconstruct your tables, and build relationship with your models.

If you really want to use DB::select(),

You can just use subquery

$posts = DB::select('
SELECT posts.*, (
    SELECT COUNT(id_comment)  AS total
    FROM post_comments 
    WHERE id_comment = posts.id_comment) AS total
FROM posts')

Upvotes: 1

FULL STACK DEV
FULL STACK DEV

Reputation: 15971

You can use count() for the collections count. 

 foreach ($posts as $key => $post) {         
         $idComment = $post->id_comment;             
         $comments = DB::select('SELECT COUNT(id_comment) As total FROM post_comments WHERE id_comment = ? ', [$idComment]); 
         $post->nComments = $comments[0]['total'];
     }

Upvotes: 1

VIKAS KATARIYA
VIKAS KATARIYA

Reputation: 6005

Try this simple

 $posts = Post::withCount('comments');  
 dd($post);

Upvotes: 1

N69S
N69S

Reputation: 17216

Cleanest way would be to use the relations comments of the post

public function readPosts(Request $request) {     
    $posts = DB::withCount('comments')->get();   

    if($posts){
      return response()->json(['success'=>true, "output"=>$posts);
    }else{
      return response()->json(['success'=>false]);  
    }
}

response

data:
    0:
      id_post: 1
      post: "post text"
      comments_count: 2
   1:
      id_post: 2
      post: "post text 2"
      comments_count: 1

Plus: this will take advantage of the eager loading in Eloquent and the results will be way faster and performant.

Upvotes: 1

Vicky Gill
Vicky Gill

Reputation: 734

you can try this

 public function readPosts(Request $request) {     
            $posts = DB::select("SELECT * FROM posts");   

             foreach ($posts as $key => $post) {         
                 $idComment = $post->id_comment; 
                 $total = DB::select('SELECT COUNT(id_comment) As total FROM post_comments WHERE id_comment = ? ', [$idComment]);        
                 $post->nComments = $total[0]['total'];
             }    

            if($posts){
              return response()->json(['success'=>true, "output"=>$posts);
            }else{
              return response()->json(['success'=>false]);  
            }
        }

Upvotes: 1

Related Questions