php - Stuck with eloquent query and subquery

Solution:

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

You can use thewithCount() 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();
Copy code

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

Solution:

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

Query in the controller :

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

In the view, to count :

{{ $user->pictures->count() }}
Copy code

Solution:

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();
Copy code

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

$user->photos->count()
Copy code

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