John Davidson

Message:

I have three models, User, Post and Comment. User and Post are in a One-To-Many relationship, and so are User and Comment. For the sake of this example, there isn't a Post-Comment relationship.

When I run the following query

User::withCount(['posts', 'comments'])->get()

I obtain the expected results:

[

...

  App\Models\User {#3459

    id: 18,

    username: "Foo",

    created_at: "2021-12-08 11:38:39",

    updated_at: "2021-12-08 11:38:39",

    posts_count: 5,

    comments_count: 15,

  }

...

]

I would like to remove the timestamps from the resulting models.
I've tried putting the array of fields I want as parameter for get (as in ->get(['username', 'posts_count', 'comments_count']), but the result didn't change at all.

I've also tried replacing get(...) with select(...)->get(), but that produces this error:

Illuminate\Database\QueryException with message 

'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'posts_count' in 'field list'  

(SQL: select `username`, `posts_count`, `comments_count` from `users`)'

which I assume it is due to the fact that the aggregation function is not yet being executed.

So I came up with this solution

$usersWithCounts = User::withCount(['posts', 'comments'])->get()

    ->map(function ($item, $key) {

         return $item->only(['username', 'posts_count', 'comments_count']); 

      });

but it doesn't feel right: the returned collection is no longer made of Eloquent models, just simple arrays.

[

...

  [

    username: "Foo",

    posts_count: 5,

    comments_count: 15,

  ]

...

]

What's the right way of proceeding?

---

My Answer:
it will return the count of posts and comments for each user. However, if you want to select a specific field after the aggregation, you can use the `select` method after the `withCount` method.

For example, if you want to select the `name` field of the user along with the count of posts and comments, you can do the following:

php
User::withCount(['posts', 'comments'])
->select('id', 'name')
->get();


This will return a collection of users with their `id`, `name`, and the count of posts and comments. You can replace `id` and `name` with any other fields you want to select from the `users` table.