One place for hosting & domains

      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