One place for hosting & domains

      Eloquent

      How To Update Database Records 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.

      In a previous section of this series, you updated an existing Artisan command in order to support the new lists feature. Although there are commands to insert and delete links, the demo application currently doesn’t have a command to edit existing links. This can be useful to move links between lists, for instance, or update a link description.

      In this guide, you’ll create a new Artisan command to update existing links in the database.

      From your terminal, first make sure you’re in your project’s root directory, then run the following to bootstrap a new Artisan command:

      • docker-compose exec app php artisan make:command LinkUpdate

      This will create a new LinkUpdate.php file located at app/Console/Commands. Open the file in your code editor of choice:

      app/Console/Commands/LinkUpdate.php
      

      This file contains boilerplate code for a new Artisan command. You’ll update it to handle editing a link provided its unique id. This is what your handle() method needs to do:

      • Obtain an id provided by the user and check for the existence of a link with a matching id in the database.
      • If a valid link cannot be found, show an error message and exit.
      • If a valid link is found, prompt the user to provide updated values for the link description and link list.
      • Ask the user to confirm changes.
      • When confirmed, update the item in the database.

      Start by including a couple use definitions at the top of the file, to facilitate referencing to the Link and LinkList classes later on:

      app/Console/Commands/LinkUpdate.php

      <?php
      
      namespace AppConsoleCommands;
      
      use AppModelsLink;
      use AppModelsLinkList;
      use IlluminateConsoleCommand;
      
      ...
      

      To obtain the link id, you should set up a mandatory argument in the new link:update command, so that users are required to provide that parameter at run time. Locate the command signature definition at the top of the file and replace it with the highlighted line:

      app/Console/Commands/LinkUpdate.php

      ...
      
      class LinkUpdate extends Command
      {
          /**
           * The name and signature of the console command.
           *
           * @var string
           */
          protected $signature="link:update {link_id}";
      ...
      

      If you save the file and try to run the command now without an additional argument, you’ll get an error:

      • docker-compose exec app php artisan link:update

      Output

      Not enough arguments (missing: "link_id").

      In the handle() method, you need to obtain the link id provided by the user and locate it in the database. This can be done with the argument() method that is provided through the parent Command class. Then, you can use the find() Eloquent method to query the database for a link with that id. If the find() method returns null, it means no link with that id was found, so the program should exit in error.

      app/Console/Commands/LinkUpdate.php

      ...
         /**
           * Execute the console command.
           *
           * @return int
           */
          public function handle()
          {
              $link_id = $this->argument('link_id');
              $link = Link::find($link_id);
      
              if ($link === null) {
                  $this->error("Invalid or non-existent link ID.");
                  return 1;
              }
      
              // obtain updated information from user
          }
      ...
      

      When a valid link is found, you need to prompt the user for the updated link information.You can do so using the ask method, highlighted in the next example:

      app/Console/Commands/LinkUpdate.php: function handle()

      ...
              if ($link === null) {
                  $this->error("Invalid or non-existent link ID.");
                  return 1;
              }
      
              $link->description = $this->ask('Link Description (ENTER to keep current)') ?? $link->description;
              $list_name = $this->ask('Link List (ENTER to keep current)') ?? $link->link_list->title;
      ...
      

      This code will prompt the user for an updated description and list, while keeping the current values as default in case a user doesn’t provide new ones, pressing ENTER to skip the prompt.

      Once you have all this information, you can proceed to the update. It’s a good idea to use the confirm() method to have the user confirm the changes before you run the database update. This is how such code would look:

      app/Console/Commands/LinkUpdate.php: function handle()

      ...
              $link->description = $this->ask('Link Description (ENTER to keep current)') ?? $link->description;
              $list_name = $this->ask('Link List (ENTER to keep current)') ?? $link->link_list->title;
      
              $this->info("Description: $link->description");
              $this->info("Listed in: " . $list_name);
      
              if ($this->confirm('Is this information correct?')) {
                  //code that updates the link
              }
      ...
      

      Inside the if block, you have to start by checking if the requested list exists, otherwise create a new list with the provided name. Then, you’ll use the associate() method to update the relationship between this link and its “parent” list. The save() method, finally, will persist the changes to the database:

      app/Console/Commands/LinkUpdate.php: function handle()

      ...
              if ($this->confirm('Is this information correct?')) {
                  $list = LinkList::firstWhere('slug', $list_name);
                  if (!$list) {
                      $list = new LinkList();
                      $list->title = $list_name;
                      $list->slug = $list_name;
                      $list->save();
                  }
                  $link->link_list()->associate($list)->save();
                  $this->info("Updated.");
              }
      ...
      

      This is the complete LinkUpdate.php file for your reference:

      app/Console/Commands/LinkUpdate.php

      <?php
      
      namespace AppConsoleCommands;
      
      use AppModelsLink;
      use AppModelsLinkList;
      use IlluminateConsoleCommand;
      
      class LinkUpdate extends Command
      {
          /**
           * The name and signature of the console command.
           *
           * @var string
           */
          protected $signature="link:update {link_id}";
      
          /**
           * The console command description.
           *
           * @var string
           */
          protected $description = 'Update a link in the database';
      
          /**
           * Create a new command instance.
           *
           * @return void
           */
          public function __construct()
          {
              parent::__construct();
          }
      
          /**
           * Execute the console command.
           *
           * @return int
           */
          public function handle()
          {
              $link_id = $this->argument('link_id');
              $link = Link::find($link_id);
      
              if ($link === null) {
                  $this->error("Invalid or non-existent link ID.");
                  return 1;
              }
      
              $link->description = $this->ask('Link Description (ENTER to keep current)') ?? $link->description;
              $list_name = $this->ask('Link List (ENTER to keep current)') ?? $link->link_list->title;
      
              $this->info("Description: $link->description");
              $this->info("Listed in: " . $list_name);
      
              if ($this->confirm('Is this information correct?')) {
                  $list = LinkList::firstWhere('slug', $list_name);
                  if (!$list) {
                      $list = new LinkList();
                      $list->title = $list_name;
                      $list->slug = $list_name;
                      $list->save();
                  }
                  $link->link_list()->associate($list)->save();
                  $this->info("Updated.");
              }
      
              return 0;
          }
      }
      

      Note: For more detailed information on Artisan commands, check our guide on How To Create Artisan Commands to Manage Database Records in Laravel, which is part of our introductory Laravel series.

      Save the file when you’re finished. Then, use the link:show command to obtain all links and its respective IDs:

      • docker-compose exec app php artisan link:show

      Output

      +----+-------------------------------------------------+--------------+----------------------------------+ | id | url | list | description | +----+-------------------------------------------------+--------------+----------------------------------+ | 1 | https://digitalocean.com/community | default | DO Community | | 2 | https://digitalocean.com/community/tags/laravel | default | Laravel Tutorias at DigitalOcean | | 3 | https://digitalocean.com/community/tags/php | default | PHP Tutorials at DigitalOcean | | 4 | https://twitter.com/digitalocean | social | Twitter | | 5 | https://dev.to/digitalocean | social | DEV.to | | 6 | https://laravel.com/docs/8.x/eloquent | default | Laravel Eloquent Docs | +----+-------------------------------------------------+--------------+----------------------------------+

      Then, choose an item to edit. For instance, you may want to create a digitalocean list for the links that point to the DigitalOcean website (that would correspond to items with IDs 1, 2, and 3 in the previous example output).

      To update the link with ID 1, run:

      • docker-compose exec app php artisan link:update 1

      Output

      Link Description (ENTER to keep current): > DO Community Link List (ENTER to keep current): > digitalocean Description: DO Community Listed in: digitalocean Is this information correct? (yes/no) [no]: > y Updated.

      Then, run the link:show command again to see the updated information:

      Output

      +----+-------------------------------------------------+--------------+----------------------------------+ | id | url | list | description | +----+-------------------------------------------------+--------------+----------------------------------+ | 1 | https://digitalocean.com/community | digitalocean | DO Community | | 2 | https://digitalocean.com/community/tags/laravel | digitalocean | Laravel Tutorias at DigitalOcean | | 3 | https://digitalocean.com/community/tags/php | digitalocean | PHP Tutorials at DigitalOcean | | 4 | https://twitter.com/digitalocean | social | Twitter | | 5 | https://dev.to/digitalocean | social | DEV.to | | 6 | https://laravel.com/docs/8.x/eloquent | default | Laravel Eloquent Docs | +----+-------------------------------------------------+--------------+----------------------------------+

      In this guide, you learned how to update database records with Laravel Eloquent. You have upgraded the demo application to include a new command that allows users to edit existing links in the database.

      In the next and final part of this series, you’ll create a new command to delete a list of links.





      Source link

      How To Delete Database Records 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.

      In Eloquent, you can delete database records conveniently with the delete method from the parent Model class. The link:delete command, already implemented within the base version of the demo application, deletes links based on a valid link id. The application is still missing a command to delete lists.

      In the last part of this series, you’ll create a new command to delete lists. For simplicity, any links associated with the list to be deleted will be reassigned to the default link list.

      From your terminal, run the following to bootstrap a new Artisan command:

      • docker-compose exec app php artisan make:command ListDelete

      This will create a new ListDelete.php file located at app/Console/Commands. Open the file in your code editor of choice:

      app/Console/Commands/ListDelete.php
      

      You’ll update this code to handle deleting a link list provided its unique slug, which is a URL-friendly name used to identify each list.

      This is what your handle() method needs to do:

      • Obtain a slug provided by the user and check for the existence of a list with a matching slug in the database.
      • If a valid list cannot be found, show an error message and exit.
      • If a valid list is found, prompt the user to confirm.
      • Reassign to the default list any links associated with the list that will be deleted.
      • Delete the list from the database.

      If you’ve been following along with all parts of the series so far, you have implemented similar code before when creating the LinkUpdate command. The main difference now is that you won’t need to prompt the user for additional info, and before running the delete() method you’ll need to run a mass update to change associated links to a different list.

      Replace the boilerplate code in your ListDelete.php file with the following:

      app/Console/Commands/ListDelete.php

      <?php
      
      namespace AppConsoleCommands;
      
      use AppModelsLink;
      use AppModelsLinkList;
      use IlluminateConsoleCommand;
      
      class ListDelete extends Command
      {
          /**
           * The name and signature of the console command.
           *
           * @var string
           */
          protected $signature="list:delete {list_slug}";
      
          /**
           * The console command description.
           *
           * @var string
           */
          protected $description = 'Delete Lists';
      
          /**
           * Create a new command instance.
           *
           * @return void
           */
          public function __construct()
          {
              parent::__construct();
          }
      
          /**
           * Execute the console command.
           *
           * @return int
           */
          public function handle()
          {
              $list_slug = $this->argument('list_slug');
              $list = LinkList::firstWhere('slug', $list_slug);
      
              if ($list === null) {
                  $this->error("Invalid or non-existent List.");
                  return 1;
              }
      
              if ($this->confirm("Confirm deleting the list '$list->title'? Links will be reassigned to the default list.")) {
                  $default_list = LinkList::firstWhere('slug', 'default');
                  if (!$default_list) {
                      $default_list = new LinkList();
                      $default_list->title="default";
                      $default_list->slug = 'default';
                      $default_list->save();
                  }
      
                  $this->info("Reassigning links to default list...");
      
                  Link::where('link_list_id', $list->id)->update(['link_list_id' => $default_list->id]);
      
                  $list->delete();
                  $this->info("List Deleted.");
              }
      
              return 0;
          }
      }
      

      Save the file.

      In the previous code, the handle() method starts by trying to locate a link list based on the provided slug. If a valid list can’t be found, the application exits in error. When a valid list is found, the confirm() method is called to ask the user for confirmation.

      When confirmed, the application will locate the default list or create a new one if necessary, assigning it to the $default_list variable.

      Next, it will locate and update all links that are associated with the list that is about to be deleted. The chained call to update() will update the referenced list ID on all links that match the query, using the condition defined within the previous where() call. This line is highlighted for your reference.

      Finally, the list is deleted with the delete() method, also highlighted. This method is available to all Eloquent models through the parent Model class.

      To delete a list, first run link:show to obtain all links currently in the database:

      • docker-compose exec app php artisan link:show

      Output

      +----+-------------------------------------------------+--------------+----------------------------------+ | id | url | list | description | +----+-------------------------------------------------+--------------+----------------------------------+ | 1 | https://digitalocean.com/community | digitalocean | DO Community | | 2 | https://digitalocean.com/community/tags/laravel | digitalocean | Laravel Tutorias at DigitalOcean | | 3 | https://digitalocean.com/community/tags/php | digitalocean | PHP Tutorials at DigitalOcean | | 4 | https://twitter.com/digitalocean | social | Twitter | | 5 | https://dev.to/digitalocean | social | DEV.to | | 6 | https://laravel.com/docs/8.x/eloquent | default | Laravel Eloquent Docs | +----+-------------------------------------------------+--------------+----------------------------------+

      To delete the digitalocean list and revert those links back to the default list, run:

      • docker-compose exec app php artisan list:delete digitalocean

      Confirm the deletion by typing y and hitting ENTER.

      Output

      Confirm deleting the list 'digitalocean'? Links will be reassigned to the default list. (yes/no) [no]: > y Reassigning links to default list... List Deleted.

      If you run the link:show() command again, you’ll see the updated information:

      Output

      +----+-------------------------------------------------+---------+----------------------------------+ | id | url | list | description | +----+-------------------------------------------------+---------+----------------------------------+ | 1 | https://digitalocean.com/community | default | DO Community | | 2 | https://digitalocean.com/community/tags/laravel | default | Laravel Tutorias at DigitalOcean | | 3 | https://digitalocean.com/community/tags/php | default | PHP Tutorials at DigitalOcean | | 4 | https://twitter.com/erikaheidi | social | Twitter | | 5 | https://dev.to/erikaheidi | social | DEV.to | | 6 | https://laravel.com/docs/8.x/eloquent | default | Laravel Eloquent Docs | +----+-------------------------------------------------+---------+----------------------------------+

      The application now has a dedicated command to delete lists of links.





      Source link

      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