Tutorial

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

Published on August 12, 2021

Developer Advocate

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:

routes/web.php
<?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:

routes/web.php
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.

Although there are shortcuts to implement routes that reference Eloquent models, in this tutorial we focus on using the where() method for learning purposes.

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:

resources/views/index.blade.php
        <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:

resources/views/index.blade.php
        <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:

resources/views/index.blade.php
            <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:

resources/views/index.blade.php
<!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:

The screenshot shows the updated Landing Laravel application with working links to individual list pages

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

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.

Learn more about our products


Tutorial Series: A Practical Introduction to Laravel Eloquent ORM

Eloquent is an object relational mapper (ORM) that is included by default within the Laravel framework. In this project-based series, you’ll learn how to make database queries and how to work with relationships in Laravel Eloquent. To follow along with the examples demonstrated throughout the series, you’ll improve a demo application with new models and relationships. Visit the series introduction page for detailed instructions on how to download and set up the project.

About the authors
Default avatar

Developer Advocate

Dev/Ops passionate about open source, PHP, and Linux.

Still looking for an answer?

Ask a questionSearch for more help

Was this helpful?
 
1 Comments


This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

Great tutorial. Thank you for sharing…

A one liner:

$list = LinkList::where('slug', $slug)->first();
    if (!$list) {
        abort(404);
    }
$list = LinkList::where('slug', $slug)->firstOrFail();

Same as the code above.

Try DigitalOcean for free

Click below to sign up and get $200 of credit to try our products over 60 days!

Sign up

Join the Tech Talk
Success! Thank you! Please check your email for further details.

Please complete your information!

Featured on Community

Get our biweekly newsletter

Sign up for Infrastructure as a Newsletter.

Hollie's Hub for Good

Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

Become a contributor

Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

Welcome to the developer cloud

DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

Learn more