Stuck with eloquent query and subquery

user_16658298 0
Viewed 75

I would like to display in a table the name of the user as well as the number of photos posted during the current week. Example : User1 has posted 10 photos, User2 has posted 20 photos...

So I need a count, but I'm not sure how.

Picture model :

public function user()
{
   return $this->belongsTo('App\Models\User');
}

User model :

public function pictures()
{
   return $this->hasMany('App\Models\Picture');
}

Query in the controller :

$users = User::whereHas('pictures', 
      fn($q) => $q->where('created_at', '>=', Carbon::now()->subDays(7)->toDateTimeString()))
    ->limit(10)
    ->get();

In the view:

{{ $user->photos->count() }}

Except that it counts all the photos of the user and not just the photos for the past week. How can I only count the recent photos?

eloquent laravel php
3 Answers

I do this :

In the User model :

public function current_week_pictures()
{
   return $this->hasMany('App\Models\Picture')
            ->whereBetween('created_at', [Carbon::now()->startOfWeek(), Carbon::now()->endOfWeek()]);
}

Query in the controller :

$users = User::whereHas('current_week_pictures')
            ->with('current_week_pictures')
            ->limit(10)
            ->get();

In the view, to count :

{{ $user->pictures->count() }}
user_166582980

Creating a new relationship is a valid way to solve your problem, but here's another option.

You can use the withCount() method. By passing an array, you can use a closure to limit the results of the count to match the constraint on the relation:

$users = User::whereHas(
        'pictures',
        fn($q) => $q->where('created_at', '>=', now()->subWeek())
    )
    ->withCount([
        'pictures' => fn ($q) => $q->where('created_at', '>=', now()->subWeek()
    ])
    ->limit(10)
    ->get();

Now in your view, you can use $user->pictures_count.

user_12552890

Here you get users which post a photo since last 7 days.

$users = User::whereHas('pictures', 
          fn($q) => $q->where('created_at', '>=', Carbon::now()->subDays(7)->toDateTimeString()))
         ->limit(10)
         ->get();

Then here you try to count the number of photo a user have

$user->photos->count()

But you count only photos of users which post last 7 days, not the photos posted last 7 days.

Try do continue this Eloquent to count only photos which are posted last 7 days

user_166779000
Related