Get object value on an array iteration with foreach

Question

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

Did you declare the relation comments() in the Post model or you need help with that ? — N69S
– N69S, Commented Jan 7, 2020 at 13:24

TsaiKoga · Accepted Answer · 2020-01-07 14:07:27Z

1

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')

edited Jan 7, 2020 at 14:07

answered Jan 7, 2020 at 13:16

TsaiKoga

13.4k2 gold badges23 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

jose Over a year ago

Thanks TsaiKoga. wihCount is a great solution and perhaps best suited for this type of case. However, in my real (more complex) problem I necessarily have to use DB :: select

TsaiKoga Over a year ago

@josei man, really really suggest you rebuild the table structure.

jose Over a year ago

Thanks TsiKoga. It's just a simplified fictional case to illustrate the problem I want to solve. With eloquent and relationship, as you suggest, it would be very simple and effective. But it really has to be with DB: select this case. I am trying to find a simple solution with laravel that does not go through the model.

jose Over a year ago

Thanks TsaiKoga. Your suggestion is very interesting and a simple solution that works well (for my very specific case where there is no model). Thanks again.

Vicky Gill · Accepted Answer · 2020-01-07 13:11:09Z

1

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]);  
            }
        }

answered Jan 7, 2020 at 13:11

Vicky Gill

7341 gold badge7 silver badges22 bronze badges

1 Comment

jose Over a year ago

Thnaks Vicky. I tried your solution, but it didn't work in that case.

N69S · Accepted Answer · 2020-01-07 13:15:06Z

1

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.

answered Jan 7, 2020 at 13:15

N69S

17.3k4 gold badges25 silver badges41 bronze badges

Comments

VIKAS KATARIYA · Accepted Answer · 2020-01-07 13:20:18Z

1

Try this simple

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

answered Jan 7, 2020 at 13:20

VIKAS KATARIYA

6,0153 gold badges19 silver badges37 bronze badges

1 Comment

jose Over a year ago

Thnaks Vikas. In my real (more complex) problem I necessarily have to use DB :: select

FULL STACK DEV · Accepted Answer · 2020-01-07 13:27:22Z

1

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'];
     }

edited Jan 7, 2020 at 13:27

answered Jan 7, 2020 at 13:07

FULL STACK DEV

16k6 gold badges49 silver badges67 bronze badges

1 Comment

jose Over a year ago

Thanks Adnan. I tried this solution but with ->count() i get de error message "Call to a member function count() on array"

Collectives™ on Stack Overflow

Get object value on an array iteration with foreach

5 Answers 5

4 Comments

1 Comment

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

1 Comment

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related