$customers = Customer::orderBy('last_name')->orderBy('first_name')->paginate();Using scope instead:
$customers = Customer::orderByName()->paginate();public function scopeOrderByName($query)
{
$query->orderBy('last_name')->orderBy('first_name');
}<th>Company</th>
<td>{{ $customer->company->name }}</td>Eager load companies:
$customers = Customer::with('company')->orderByName()->paginate();Via relationship:
<th>Last Interaction</th>
<td>{{ $customer->interactions->sortByDesc('created_at')->first()->created_at->diffForHumans() }}</td>$customers = Customer::with('company', 'interactions')->orderByName()->paginate();Via database query:
$customers = Customer::with('company')->orderByName()->paginate();<td>{{ $customer->interactions()->latest()->first()->created_at->diffForHumans() }}</td>Via sub query:
public function scopeWithLastInteractionDate($query)
{
$subQuery = \DB::table('interactions')
->select('created_at')
->whereRaw('customer_id = customers.id')
->latest()
->limit(1);
return $query->select('customers.*')->selectSub($subQuery, 'last_interaction_date');
}$customers = Customer::with('company')
->withLastInteractionDate()
->orderByName()
->paginate();<td>{{ $customer->last_interaction_date->diffForHumans() }}</td>protected $casts = [
'birth_date' => 'date',
'last_interaction_date' => 'datetime',
];Via sub query (improved):
public function scopeWithLastInteractionDate($query)
{
$query->addSubSelect('last_interaction_date', Interaction::select('created_at')
->whereRaw('customer_id = customers.id')
->latest()
);
}use Illuminate\Database\Eloquent\Builder;
Builder::macro('addSubSelect', function ($column, $query) {
if (is_null($this->getQuery()->columns)) {
$this->select($this->getQuery()->from.'.*');
}
return $this->selectSub($query->limit(1)->getQuery(), $column);
});<td>
{{ $customer->last_interaction_date->diffForHumans() }}
<span class="text-secondary">({{ $customer->last_interaction_type }})</span>
</td>public function scopeWithLastInteractionType($query)
{
$query->addSubSelect('last_interaction_type', Interaction::select('type')
->whereRaw('customer_id = customers.id')
->latest()
);
}$customers = Customer::with('company')
->withLastInteractionDate()
->withLastInteractionType()
->orderByName()
->paginate();Sub query relationship approach:
$customers = Customer::with('company')
->withLastInteraction()
->orderByName()
->paginate();public function lastInteraction()
{
return $this->hasOne(Interaction::class, 'id', 'last_interaction_id');
}
public function scopeWithLastInteraction($query)
{
$query->addSubSelect('last_interaction_id', Interaction::select('id')
->whereRaw('customer_id = customers.id')
->latest()
)->with('lastInteraction');
}<td>
{{ $customer->lastInteraction->created_at->diffForHumans() }}
<span class="text-secondary">({{ $customer->lastInteraction->type }})</span>
</td>Remove last_interaction_date cast:
protected $casts = [
'birth_date' => 'date',
];<th><a class="{{ request('order', 'name') === 'name' ? 'text-dark' : '' }}" href="{{ route('customers', ['order' => 'name'] + request()->except('page')) }}">Name</a></th>
<th><a class="{{ request('order') === 'company' ? 'text-dark' : '' }}" href="{{ route('customers', ['order' => 'company'] + request()->except('page')) }}">Company</a></th>
<th><a class="{{ request('order') === 'birthday' ? 'text-dark' : '' }}" href="{{ route('customers', ['order' => 'birthday'] + request()->except('page')) }}">Birthday</a></th>
<th><a class="{{ request('order') === 'last_interaction' ? 'text-dark' : '' }}" href="{{ route('customers', ['order' => 'last_interaction'] + request()->except('page')) }}">Last Interaction</a></th>$customers = Customer::with('company')
->withLastInteraction()
->orderByField($request->get('order', 'name'))
->paginate();public function scopeOrderByField($query, $field)
{
if ($field === 'name') {
$query->orderByName();
} elseif ($field === 'company') {
$query->orderByCompany();
} elseif ($field === 'birthday') {
$query->orderByBirthday();
} elseif ($field === 'last_interaction') {
$query->orderByLastInteractionDate();
}
}Company, one approach:
public function scopeOrderByCompany($query)
{
$query->join('companies', 'companies.id', '=', 'customers.company_id')->orderBy('companies.name');
}Company, another approach:
public function scopeOrderByCompany($query)
{
$query->orderBySub(Company::select('name')->whereRaw('customers.company_id = companies.id'));
}Builder::macro('orderBySub', function ($query, $direction = 'asc') {
return $this->orderByRaw("({$query->limit(1)->toSql()}) {$direction}");
});
Builder::macro('orderBySubDesc', function ($query) {
return $this->orderBySub($query, 'desc');
});Birthday:
public function scopeOrderByBirthday($query)
{
$query->orderbyRaw("to_char(birth_date, 'MMDD')");
}Last interaction date:
public function scopeOrderByLastInteractionDate($query)
{
$query->orderBySubDesc(Interaction::select('created_at')->whereRaw('customers.id = interactions.customer_id')->latest());
}<form class="input-group my-4" action="{{ route('customers') }}" method="get">
<input type="hidden" name="order" value="{{ request('order') }}">
<input type="text" class="w-50 form-control" placeholder="Search..." name="search" value="{{ request('search') }}">
<div class="input-group-append">
<button class="btn btn-primary" type="submit">Search</button>
</div>
</form>$customers = Customer::with('company')
->withLastInteraction()
->whereSearch($request->get('search'))
->orderByField($request->get('order', 'name'))
->paginate();public function scopeWhereSearch($query, $search)
{
foreach (explode(' ', $search) as $term) {
$query->where(function ($query) use ($term) {
$query->where('first_name', 'ilike', '%'.$term.'%')
->orWhere('last_name', 'ilike', '%'.$term.'%')
->orWhereHas('company', function ($query) use ($term) {
$query->where('name', 'ilike', '%'.$term.'%');
});
});
}
}<select name="filter" class="custom-select">
<option value="" selected>Filters...</option>
<option value="birthday_this_week" {{ request('filter') === 'birthday_this_week' ? 'selected' : '' }}>Birthday this week</option>
</select>$customers = Customer::with('company')
->withLastInteraction()
->whereFilters($request->only(['search', 'filter']))
->orderByField($request->get('order', 'name'))
->paginate();public function scopeWhereFilters($query, array $filters)
{
$filters = collect($filters);
$query->when($filters->get('search'), function ($query, $search) {
$query->whereSearch($search);
})->when($filters->get('filter') === 'birthday_this_week', function ($query, $filter) {
$query->whereBirthdayThisWeek();
});
}use Illuminate\Support\Carbon;
public function scopeWhereBirthdayThisWeek($query)
{
$start = Carbon::now()->startOfWeek();
$end = Carbon::now()->endOfWeek();
$dates = collect(new \DatePeriod($start, new \DateInterval('P1D'), $end))->map(function ($date) {
return $date->format('md');
});
return $query->whereNotNull('birth_date')->whereIn(\DB::raw("to_char(birth_date, 'MMDD')"), $dates);
}public function scopeVisibleTo($query, User $user)
{
if ($user->is_admin) {
return $query;
}
return $query->where('sales_rep_id', $user->id);
}use App\User;
$customers = Customer::with('company')
->withLastInteraction()
->whereFilters($request->only(['search', 'filter']))
->orderByField($request->get('order', 'name'))
->visibleTo(
User::where('name', 'Jonathan Reinink')->first()
// User::where('name', 'Taylor Otwell')->first()
// User::where('name', 'Ian Landsman')->first()
)
->paginate();
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent