One place for hosting & domains

      Query

      How To Limit and Paginate Query Results in Laravel Eloquent



      Part of the 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.

      Throughout this series, you have been adding new links to your demo application to test out several features from Laravel Eloquent. You may have noticed that the main index page is getting longer each time you add a new link, since there is no limit to the number of links shown in the application. Although that won’t be an issue when you have a small number of database entries, in the long term that might result in longer loading times for your page, and a layout that is more difficult to read due to the amount of content spread in a single page.

      In this part of the series, you’ll learn how to limit the number of results in a Laravel Eloquent query with the limit() method, and how to paginate results with the simplePaginate() method.

      Limiting Query Results

      To get started, you’ll update your main application route (/) to limit the number of links that are listed on your index page.

      Start by opening your web routes file in your code editor:

      routes/web.php
      

      Then, locate the main route definition:

      routes/web.php

      Route::get('/', function () {
          $links = Link::all()->sortDesc();
          return view('index', [
              'links' => $links,
              'lists' => LinkList::all()
          ]);
      });
      

      The highlighted line shows the query that obtains all links currently in the database, through the Link model all() method. As explained in a previous part of this series, this method is inherited from the parent Model class, and returns a collection with all database records associated with that model. The sortDesc() method is used to sort the resulting collection in descending order.

      You’ll now change the highlighted line to use the database query sorting method orderBy(), which orders query results at the database level, instead of simply reordering the full set of rows that is returned as an Eloquent Collection via the all() method. You’ll also include a chained call to the limit() method in order to limit the query results. Finally, you’ll use the get() method to obtain the filtered resultset as an Eloquent Collection.

      Replace your main route with the following code. The change is highlighted for your convenience:

      routes/web.php

      Route::get('/', function () {
          $links = Link::orderBy('created_at', 'desc')->limit(4)->get();
      
          return view('index', [
              'links' => $links,
              'lists' => LinkList::all()
          ]);
      });
      

      The updated code will now pull the latest 4 links added to the database, no matter at which list. Because all links are added to lists, visitors can still go to specific lists to see the full list of links.

      Next, you’ll learn how to paginate results to make sure all links are still accessible, even though they don’t load all at once on a single page.

      Paginating Query Results

      Your index page now limits the number of links that are listed, so that your page isn’t overloaded with content, and gets rendered in a shorter amount of time. While this solution works well in many cases, you’ll need to make sure visitors can still access older links that aren’t visible by default. The most effective way to do so is by implementing a pagination where users can navigate between multiple pages of results.

      Laravel Eloquent has native methods to facilitate implementing pagination on database query results. The paginate() and simplePaginate() methods take care of generating pagination links, handling HTTP parameters to identify which page is currently being requested, and querying the database with the correct limit and offset in order to obtain the expected set of results, depending on the number of records per page you want to list.

      You’ll now update the Eloquent queries in routes/web.php to use the simplePaginate() method, which generates a basic navigation with previous and next links. Unlike the paginate() method, simplePaginate() doesn’t show information about the total number of pages in a query result.

      Open the routes/web.php file in your code editor. Start by updating the / route, replacing the limit(4)->get() method call with the simplePaginate() method:

      routes/web.php

      ...
      Route::get('/', function () {
          $links = Link::orderBy('created_at', 'desc')->simplePaginate(4);
      
          return view('index', [
              'links' => $links,
              'lists' => LinkList::all()
          ]);
      });
      ...
      

      Next, locate the /{slug} route definition in the same file, and replace the get() method with the simplePaginate() method. This is how the code should look once you’re done:

      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()->orderBy('created_at', 'desc')->simplePaginate(4),
              'lists' => LinkList::all()
          ]);
      })->name('link-list');
      ...
      

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

      routes/web.php

      <?php
      
      use IlluminateSupportFacadesRoute;
      use AppModelsLink;
      use AppModelsLinkList;
      
      /*
      |--------------------------------------------------------------------------
      | 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::orderBy('created_at', 'desc')->simplePaginate(4);
      
          return view('index', [
              'links' => $links,
              'lists' => LinkList::all()
          ]);
      });
      
      Route::get('/{slug}', function ($slug) {
          $list = LinkList::where('slug', $slug)->first();
          if (!$list) {
              abort(404);
          }
      
          return view('index', [
              'list' => $list,
              'links' => $list->links()->orderBy('created_at', 'desc')->simplePaginate(4),
              'lists' => LinkList::all()
          ]);
      })->name('link-list');
      
      

      Save the file when you’re done.

      The database queries are now updated, but you still need to update your front end view to include code that will render the navigation bar. The resulting Eloquent collection obtained with simplePaginate() contains a method called links(), which can be called from the front end view to output the necessary HTML code that will render a navigation section based on a paginated Eloquent query.

      You can also use the links() method in a paginated Eloquent collection to access the inherent paginator object, which provides several helpful methods to obtain information about the content such as the current page and whether there are multiple pages of content or not.

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

      resources/views/index.blade.php
      

      Locate the end of the section tagged with the links class, which contains a foreach loop where links are rendered. Place the following piece of code after that section and before the final </div> tag on that page:

      resources/views/index.blade.php

              @if ($links->links()->paginator->hasPages())
                  <div class="mt-4 p-4 box has-text-centered">
                      {{ $links->links() }}
                  </div>
              @endif
      

      This code checks for the existence of multiple pages of results by accessing the paginator object and calling the hasPages() method. When that method returns true, the page renders a new div element and calls the links() method to print the navigation links for the related Eloquent query.

      This is how the updated index.blade.php page will look like once you’re finished:

      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 }} ({{ $list->links()->count() }})</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>
      
              @if ($links->links()->paginator->hasPages())
                  <div class="mt-4 p-4 box has-text-centered">
                      {{ $links->links() }}
                  </div>
              @endif
          </div>
      </section>
      </body>
      </html>
      

      Save the file when you’re done updating it. If you go back to your browser window and reload the application page now, you’ll notice a new navigation bar whenever you have more than 4 links in the general listing or in any individual link list page.

      Updated application Landing Laravel with content pagination

      With a functional pagination in place, you can grow your content while making sure that older items are still accessible to users and search engines. In cases where you need only a fixed amount of results based on a certain criteria, where pagination is not necessary, you can use the limit() method to simplify your query and guarantee a limited result set.

      This tutorial is part of an ongoing weekly series about Laravel Eloquent. You can subscribe to the Laravel tag if you want to be notified when new tutorials are published.



      Source link

      How To Order Query Results in Laravel Eloquent



      Part of the 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 practice the examples explained throughout the series, you’ll improve a demo application with new models and relationships.

      In a previous part of this series, you learned how to obtain database records using the all() method from within an Eloquent model. You may recall using a method called sortDesc(), which was used to sort the records in descending order.

      The sortDesc() method is part of the Collection class, a powerful Laravel utility class that works as an improved version of native PHP arrays. Instead of ordering results within the database query itself, this method will invert the order of a collection, so that the last item appears first in the collection. While that works well for smaller result sets, it doesn’t offer the same flexibility as sorting the results in the database query itself.

      To sort results in the database query, you’ll need to use the orderBy() method, and provide the table field you want to use as criteria for ordering. This will give you more flexibility to build a query that will obtain only the results you need from the database.

      You’ll now change the code in your routes/web.php file to show results ordered from newest to oldest, based on the created_at table field.

      Both the created_at and the updated_at fields are managed by Eloquent when you include a timestamps() definition in your table migration. You should not update these fields manually, but you can use them to sort and filter your queries.

      Open this file in your code editor:

      routes/web.php
      

      This is how the code looks like now:

      routes/web.php

      <?php
      
      use IlluminateSupportFacadesRoute;
      use AppModelsLink;
      use AppModelsLinkList;
      
      /*
      |--------------------------------------------------------------------------
      | 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()
          ]);
      });
      
      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');
      
      

      Notice that the /{slug} route, which is responsible for listing the links by slug, currently doesn’t use any sorting method. Links are obtained through the list variable, highlighted in the code, using the relationship defined in the LinkList model.

      If you add multiple links to a list now, the query will return results ordered from oldest to newest by default. Although you could use the sortDesc() method to reorder the collection within the $list->links call, using the orderBy() method provides more flexibility and allows you to include additional filtering conditions later. You can chain this method with a where() call for even more fine-grained results.

      Replace the highlighted line in the previous code sample with the following line:

      routes/web.php

      'links' => $list->links()->orderBy('created_at', 'desc')->get(),
      

      Notice that this time we’re invoking the built-in query builder by calling the $list->links() method, which refers to the relationship method defined in the LinkList class. This is different from calling $list->links as a class property (without the parenthesis), which will invoke a magic method in the model to fetch all links related to that list.

      This is how the full routes/web.php file should look like once you’re finished:

      routes/web.php

      <?php
      
      use IlluminateSupportFacadesRoute;
      use AppModelsLink;
      use AppModelsLinkList;
      
      /*
      |--------------------------------------------------------------------------
      | 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()
          ]);
      });
      
      Route::get('/{slug}', function ($slug) {
          $list = LinkList::where('slug', $slug)->first();
          if (!$list) {
              abort(404);
          }
      
          return view('index', [
              'list' => $list,
              'links' => $list->links()->orderBy('created_at', 'desc')->get(),
              'lists' => LinkList::all()
          ]);
      })->name('link-list');
      
      

      Save and close the file. Now, add a couple new links using the link:new Artisan command. You can use the default list:

      • docker-compose exec app php artisan link:new

      Output

      Link URL: > https://laravel.com/docs/8.x/eloquent Link Description: > Laravel Eloquent Docs Link List (leave blank to use default): > New Link: https://laravel.com/docs/8.x/eloquent - Laravel Eloquent Docs Listed in: default Is this information correct? (yes/no) [no]: > yes Saved.

      If you reload the default link list page, you should now obtain links from newest to oldest:

      http://localhost:8000/default
      

      Default Link List showing links ordered by creation date, in descending order - from newest to oldest

      Likewise, if you would prefer to order links alphabetically by the link description, you would have to change the line to use that table field in the method call like this:

      'links' => $list->links()->orderBy('description', 'asc')->get(),
      

      This is how the links would be ordered after such change:

      Default Link List showing links ordered alphabetically by link description

      In the next part of this series, you’ll learn how to obtain the total result count from a Laravel Eloquent query.

      This tutorial is part of an ongoing weekly series about Laravel Eloquent. You can subscribe to the Laravel tag if you want to be notified when new tutorials are published.



      Source link

      How To Query the Database in Laravel with Eloquent (Select)



      Part of the 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 practice the examples explained throughout the series, you’ll improve a demo application with new models and relationships.

      If you’ve been following along with all parts of this series so far, you should have the application’s database and commands upgraded to include lists of links.

      The application main route currently shows all links in the database, with no information about lists. In this section, you’ll update the main front end view in order to reflect the new architecture.

      One of the biggest advantages of using an ORM system is the ability to manipulate rows in a database table as objects within your codebase. Eloquent provides several methods that can be accessed directly from models to query the database and filter results without having to write SQL statements. A typical SELECT query to obtain all rows from a table, which in pure SQL looks something like SELECT * FROM links, can be accomplished in Eloquent with code like this:

      $links = Link::all();
      

      The result set is returned as an Eloquent Collection, an iterable object that behaves similarly to an array, but provides extended functionality such as map / reduce methods and the ability to “hydrate” (pull in fresh data) referenced objects only when needed, which helps with overall performance while interacting with the database.

      Updating the Index Route

      If you examine the main application route file, where the index route is defined, you’ll notice that the current application code queries all links using a Link::all() call. Open the routes/web.php file in your code editor:

      routes/web.php
      

      This is how the / route is currently defined in this file:

      routes/web.php

      …
      Route::get('/', function () {
          $links = Link::all()->sortDesc();
          return view('index', [
              'links' => $links
          ]);
      });
      …
      

      As the name suggests, the sortDesc() method is used for ordering results in descending order, from last to first. This is not the same as an ORDER BY clause in an SQL query, since the sortDesc method works at code level, reordering the collection. You can ignore this method for now, as we’ll talk more about ordering results in an upcoming section of this series.

      You’ll now edit this code to obtain a collection of all lists currently registered within the database, so that you can use it later on in the front end view to show all list names.

      First, include a use declaration at the beginning of the file, referencing the LinkList model. This declaration is used to make sure that you don’t need to type the full class name each time you reference that class.

      routes/web.php

      <?php
      
      use IlluminateSupportFacadesRoute;
      use AppModelsLink;
      use AppModelsLinkList;
      ...
      

      Then, change the return of the main route definition to include a lists variable containing all registered lists:

      routes/web.php

      …
          return view('index', [
              'links' => $links,
              'lists' => LinkList::all()
          ]);
      …
      

      This is how the finished file should look. Note the highlighted changes:

      routes/web.php

      <?php
      
      use IlluminateSupportFacadesRoute;
      use AppModelsLink;
      use AppModelsLinkList;
      
      /*
      |--------------------------------------------------------------------------
      | 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()
          ]);
      });
      
      

      Don’t forget to save the file when you’re done updating it.

      Updating the Index View

      Once you get the main route updated to also provide information about lists, you can edit the referenced view file. Open the resources/views/index.blade.php file in your code editor:

      resources/views/index.blade.php
      

      This file contains the single front end view file that the application uses in its main index page. Within this file, locate the @foreach blade block that loops over the $links variable. It will look like this:

      resources/views/index.blade.php

      ...
                  @foreach ($links as $link)
                      <div class="box link">
                          <h3><a href="https://www.digitalocean.com/community/tutorials/{{ $link->url }}" target="_blank" title="Visit Link: {{ $link->url }}">{{ $link->description }}</a></h3>
                          <p>{{$link->url}}</p>
                      </div>
                  @endforeach
      ...
      

      The code in the loop does not include any information about lists yet. You’ll now include a new line right after the link URL that contains the title of the list where that link is saved. You can use the tag CSS class from Bulma to style this information as a tag:

      resources/views/index.blade.php

      …
      <p>{{$link->url}}</p>
      <p class="mt-2"><a href="#" title="{{ $list->title }}" class="tag is-info">{{ $link->link_list->title }}</a></p>

      Add the highlighted line to your file. The links will be adjusted later on, when you set up individual list pages in an upcoming part of this series.

      Next, locate the paragraph that has the class subtitle, which comes right after the <h1> tag and before the section containing your links. You’ll replace the generic text used in that area with a menu based on your link lists, which you made available previously in a lists variable when you edited the default route in routes/web.php.

      Replace the text in the subtitle section with the highlighted content:

      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>
      

      The list tags are styled just slightly differently than before, with an is-light additional CSS class to invert the colors of each tag.

      This is how your index.blade.php file should look like after you’re done editing. The highlighted lines indicate the changes:

      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">
                  Check out my awesome links
              </h1>
              <p class="subtitle">
                  @foreach ($lists as $list)<a href="#" 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="https://www.digitalocean.com/community/tutorials/{{ $link->url }}" target="_blank" title="Visit Link: {{ $link->url }}">{{ $link->description }}</a></h3>
                          <p>{{$link->url}}</p>
                          <p class="mt-2"><a href="#" title="{{ $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. The main front end view is now ready to show updated information about link lists.

      If you’d like, you can use the link:new Artisan command now to include new links and test your updated application:

      • docker-compose exec app php artisan link:new
      [secondary_label Output
      Link URL:
      > https://laravel.com/docs/8.x/
      
      Link Description:
      > Laravel Docs
      
      Link List (leave blank to use default):
      > laravel
      
      New Link:
      https://laravel.com/docs/8.x/ - Laravel Docs
      Listed in: laravel
      
      Is this information correct? (yes/no) [no]:
      > yes
      
      Saved.
      

      Then, reload the application page on your browser. If you’re using the included Docker Compose setup, the application should be available on the following local address:

      http://localhost:8000
      

      You’ll obtain a page similar to this:

      Screenshot of Landing Laravel app, updated to support link lists

      In the next part of this series, you’ll set up individual pages for link lists and learn how to make queries to the database using the where() method, for more fine-grained results.

      This tutorial is part of an ongoing weekly series about Laravel Eloquent. You can subscribe to the Laravel tag if you want to be notified when new tutorials are published.



      Source link