One place for hosting & domains

      Database

      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 Refine Database Queries in Laravel with Eloquent Where()



      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 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 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()
          ]);
      });
      
      

      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="https://www.digitalocean.com/community/tutorials/#" 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="https://www.digitalocean.com/community/tutorials/{{ 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="https://www.digitalocean.com/community/tutorials/{{ 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="https://www.digitalocean.com/community/tutorials/{{ 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="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="https://www.digitalocean.com/community/tutorials/{{ 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.

      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