How To Refine Database Queries in Laravel with Eloquent Where()

In a previous part of this series, you updated the demo application to organize links into lists in your database. The main application view now shows a menu with all lists that are currently registered within the database, but the menu has no active links yet.

In this section, you’ll create a new route within the application to show links by list. You’ll also learn how to use the where() method in Eloquent to better filter results in a database query.

To get started, open the routes/web.php file in your code editor:

routes/web.php

The file currently has the following content:

<?php
 
use Illuminate\Support\Facades\Route;
use App\Models\Link;
use App\Models\LinkList;
 
/*
|--------------------------------------------------------------------------
| Web Routes
|--------------------------------------------------------------------------
|
| Here is where you can register web routes for your application. These
| routes are loaded by the RouteServiceProvider within a group which
| contains the "web" middleware group. Now create something great!
|
*/
 
Route::get('/', function () {
    $links = Link::all()->sortDesc();
    return view('index', [
        'links' => $links,
        'lists' => LinkList::all()
    ]);
});
 

The Route::get call defines an HTTP GET route for the application entry page. When a request to / is made to the application, this callback function will be triggered to return the index view.

You’ll now create a second route to show lists of links based on a list slug. A slug is a short string that is typically used to build user-friendly URLs. The new route must query the database’s link_lists table for a list that has the provided URL parameter as its slug field. If a list with that slug cannot be found, the application should inform the user with an HTTP 404 or not found error.

The following code creates a GET route using a dynamic parameter (defined by {slug}), named link-list. This will:

• Use the LinkList Eloquent model to query the database with the where() method, using slug as search criteria. The first() method will make sure only one object is returned.
• If a list with the specified slug can’t be found, a 404 error is thrown with the abort method.
• If a valid list is found, the index view is then rendered, and the list is provided as a template parameter.

The $lists parameter is provided to build the list menu, and the $links parameter is provided for compatibility with the current version of the index view, since it loops through a variable with that name.

Include the following code at the bottom of your routes/web.php file:

Route::get('/{slug}', function ($slug) {
    $list = LinkList::where('slug', $slug)->first();
    if (!$list) {
        abort(404);
    }
 
    return view('index', [
        'list' => $list,
        'links' => $list->links,
        'lists' => LinkList::all()
    ]);
})->name('link-list');
 

Save the file when you’re done.

To test that your new route works as expected, you can go to your browser and access the link for the default list page. If you’ve followed all steps in this series so far and your database is not empty, the default list page should be available at the following local address:

http://localhost:8000/default

You’ll see the same page as before, but links are now limited to those from the default list. If you have additional lists, you can access their page by replacing the highlighted default slug in the URL with the slug of your list.

With your new route configured, you can now use the route Blade method to dynamically generate URLs for your link lists, from your template view. You can also customize the page header to show information about a list in case one is available.

Open the resources/views/index.blade.php file in your code editor:

resources/views/index.blade.php

This file has 2 lines that need updating. First, locate the subtitle paragraph containing the menu you created in another part of this series. This is how it looks like now:

        <p class="subtitle">
            @foreach ($lists as $list)<a href="#" title="{{ $list->title }}" class="tag is-info is-light">{{ $list->title }}</a> @endforeach
        </p>

You’ll update the href hyperlink to include the current URL for the list page, using the route blade method. This method expects the name of the route as the first argument, with URL parameters provided as additional arguments to the method call. Replace the # character with the following highlighted content:

        <p class="subtitle">
            @foreach ($lists as $list)<a href="{{ route('link-list', $list->slug) }}" title="{{ $list->title }}" class="tag is-info is-light">{{ $list->title }}</a> @endforeach
        </p>

Next, locate the links section and the foreach loop within. You’ll need to include another call to the route() method where the list name is printed for each link. This will be similar to the previous example, however, the list object is accessed differently, through the $link variable:

            <p>{{$link->url}}</p>
            <p class="mt-2"><a href="{{ route('link-list', $link->link_list->slug) }}" title="{{ $link->link_list->title }}" class="tag is-info">{{ $link->link_list->title }}</a></p>

Next, you may want to include information about a list, when additional information is provided. You can check for the existence of a $list variable, and print the list title only when that variable is available.

Replace your title section with the following highlighted code:

        <h1 class="title">
            @if (isset($list))
                {{ $list->title }}
            @else
                Check out my awesome links
            @endif
        </h1>

This is how the index.blade.php file will look once you’re finished. The changes are highlighted for your convenience:

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>My Awesome Links</title>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bulma@0.9.1/css/bulma.min.css">
 
    <style>
        html {
            background: url("https://i.imgur.com/BWIdYTM.jpeg") no-repeat center center fixed;
            -webkit-background-size: cover;
            -moz-background-size: cover;
            -o-background-size: cover;
            background-size: cover;
        }
 
        div.link h3 {
            font-size: large;
        }
 
        div.link p {
            font-size: small;
            color: #718096;
        }
    </style>
</head>
<body>
<section class="section">
    <div class="container">
        <h1 class="title">
            @if (isset($list))
                {{ $list->title }}
            @else
                Check out my awesome links
            @endif
        </h1>
        <p class="subtitle">
            @foreach ($lists as $list)<a href="{{ route('link-list', $list->slug) }}" title="{{ $list->title }}" class="tag is-info is-light">{{ $list->title }}</a> @endforeach
        </p>
 
        <section class="links">
            @foreach ($links as $link)
                <div class="box link">
                    <h3><a href="{{ $link->url }}" target="_blank" title="Visit Link: {{ $link->url }}">{{ $link->description }}</a></h3>
                    <p>{{$link->url}}</p>
                    <p class="mt-2"><a href="{{ route('link-list', $link->link_list->slug) }}" title="{{ $link->link_list->title }}" class="tag is-info">{{ $link->link_list->title }}</a></p>
                </div>
            @endforeach
        </section>
    </div>
</section>
</body>
</html>

Save the file when you’re done updating its contents.

You can now access the application main page through your browser. If you are using the included Docker Compose setup, the application should be available through the following local address:

http://localhost:8000

You’ll get a page similar to the following screenshot:

In the next part of this series, you’ll learn how to order query results in Laravel Eloquent.