Source code for my example app used in my Laracon Online 2018 talk
Switch branches/tags
Nothing to show
Clone or download
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
app Limit customer results to user's access Feb 7, 2018
bootstrap Install Laravel Feb 5, 2018
config Install Laravel Feb 5, 2018
database Setup Feb 5, 2018
public Install Laravel Feb 5, 2018
resources Add "birthday this week" filter Feb 7, 2018
routes Setup Feb 5, 2018
storage Setup Feb 5, 2018
tests Install Laravel Feb 5, 2018
.env.example Install Laravel Feb 5, 2018
.gitattributes Install Laravel Feb 5, 2018
.gitignore Install Laravel Feb 5, 2018
artisan Install Laravel Feb 5, 2018
composer.json Setup Feb 5, 2018
composer.lock Setup Feb 5, 2018
package.json Install Laravel Feb 5, 2018
phpunit.xml Install Laravel Feb 5, 2018
readme.md Add list of requirements to readme Feb 8, 2018
server.php Install Laravel Feb 5, 2018
webpack.mix.js Install Laravel Feb 5, 2018

readme.md

Laracon Online 2018

Requirement 1: Sort customers by name (last name, first name)

$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');
}

Requirement 2: Add company name

<th>Company</th>
    <td>{{ $customer->company->name }}</td>

Eager load companies:

$customers = Customer::with('company')->orderByName()->paginate();

Requirement 3: Add last interaction date

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);
});

Requirement 4: Add last interaction type

<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',
];

Requirement 5: Make all columns sortable

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

Requirement 6: Add text based search

<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.'%');
               });
        });
    }
}

Requirement 7: Add filter for customers with birthday's this week

<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);
}

Requirement 8: Limit results to the current user's access

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