TLDR: In Laravel 5.4, where can I do the logic if I don't want to do 50k requests to the DB from the model and want to limit logic in the view?
Basically, my tables looks like this:
-----
|users|
|-id |
-----
-------------
|subscriptions|
|-id |
|-user_id |
-------------
----------------
|participations |
|-subscription_id|
|-activity_id |
----------------
---------
|activities|
|-id |
---------
Now the thing is, I have a view that looks like a weekly calendar where we can display the users with all the activities they have access and based on a date and indicate if they participate or not.
@foreach(datesOfCurrentWeek() as $date)
<div class="title">
<i class="dropdown icon"></i>
{{$date->format("l Y-m-d")}}
</div>
<div class="content">
@foreach($users as $user)
<div class="accordion">
<div class="title">
<i class="dropdown icon"></i>
{{ $user->name }}
</div>
<div class="content">
@foreach($user->activitiesAt($date) as $activity)
@include('calendar.partials.card', ['activity' => $activity, 'participation' => $activity->participation($user)])
@endforeach
</div>
</div>
@endforeach
</div>
@endforeach
$user->activitiesAt($date) as $activity : I need the activities where the user has access to and during the given date.
In the model:
return Activities::where(['group_id' => $this->group->id, 'date_start_at' => $date->format('Y-m-d')])->get();
Then the $activity->participation($user) which return the participation of the user for this activity.
In the model:
return $this->participations()->where(['subscription_id' => $user->activeSubscription()->id])->first();
And the active subscription of the user model
public function activeSubscription()
{
// todo: get the active one
return $this->subscriptions()->first();
}
And the calendar.partial.date (nothing special at this point)
<div class="ui padded raised segment">
<p>{{ $activity->start_at->format('H:i') }} - {{ $activity->end_at->format('H:i') }} </p>
<p>{{ $activity->title }} </p>
@if($participation)
<button class="circular ui icon basic green button">
<i class="check green icon"></i>
</button>
@else
<button class="circular ui icon basic blue button">
<i class="add blue icon"></i>
</button>
@endif
</div>
The final result of the view give something like that:
> Monday 2017.07.03
> user1
- activity1 (participate)
- activity3 (participate)
> user2
- activity1 (participate)
- activity3
> user3
- activity2
- activity4
> Tuesday 2017.07.04
> user1
- activity10
- activity30 (participate)
> user2
- activity10
- activity30
> user3
- activity20 (participate)
- activity40
...
But since the queries have a "dynamic" where clause, I can't preload with eagerloading and the requests can become quickly over bloated. (4 users, 3 activities each and I'm already at 100+ queries)
I think the most optimized way would be to load everything with the eagerloading, and then do the logic into the view, but that's what I'm trying to avoid. So, any idea how can I do the logic while keeping a clean view?
In addition to that: I'm using repositories and I feel like that's wrong to add a where clause outside of the repository.
Thank you!
You would be able to use eager loading and you would have to change that much with your code.
I am going to be making a few assumptions with your code e.g. model names and relationship types but you should get the gist. I also understand that the following won't be taking in to consideration the fact that you're using repositories but you should be able to easily refactor them to fit.
Firstly, in your User model (unless you've already done so) add the following methods:
/**
* Activities Relationship
*
* @return \Illuminate\Database\Eloquent\Relations\HasMany
*/
public function activities()
{
return $this->hasMany(Activities::class, 'group_id', 'group_id');
}
/**
* Active Subscription Relationship
*
* @return mixed
*/
public function activeSubscription()
{
return $this->hasOne(Subscription::class)
->where('active', true); // <-- You would replace this will the actual logic to get the active subscription
}
/**
* Helper method to get the users activities for the supplied date
*
* @param $date
* @return mixed
*/
public function activitiesForDate($date)
{
return $this->activities->toBase()
->filter(function ($activity) use($date) {
return $activity->date_start_at->isSameDay($date);
});
}
Then in your Activities model:
/**
* Get the activities that start between the supplied dates
*
* @param $query
* @param $start
* @param $end
*/
public function scopeStartsBetween($query, $start, $end)
{
$query->whereBetween('date_start_at', [$start->format('Y-m-d'), $end->format('Y-m-d')]);
}
/**
* Helper function to check if the supplied User is a participant
*
* @param $user
* @return bool
*/
public function hasParticipant($user)
{
return ! $this->participations->toBase()
->filter(function ($participation) use($user) {
return $user->activeSubscription->id == $participation->subscription_id;
})
->isEmpty();
}
Your query would then look something like:
$dates = collect(datesOfCurrentWeek());
$users = User::with(['activeSubscription', 'activities' => function ($query) use($dates) {
$query->with('participations')->startsBetween($dates->first(), $dates->last());
}])->get();
Finally, you blade file will then look something like:
@foreach(datesOfCurrentWeek() as $date)
<div class="title">
<i class="dropdown icon"></i>
{{ $date->format("l Y-m-d") }}
</div>
<div class="content">
@foreach($users as $user)
<div class="accordion">
<div class="title">
<i class="dropdown icon"></i>
{{ $user->name }}
</div>
<div class="content">
@foreach($user->activitiesForDate($date) as $activity)
@include('calendar.partials.card', ['activity' => $activity, 'participation' => $activity->hasParticipant($user)])
@endforeach
</div>
</div>
@endforeach
</div>
@endforeach
The above should allow for everything you need to be eager loaded.
Hope this helps!