One place for hosting & domains

      Records

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

      In a previous section of this series, you set up two models for a one-to-many relationship between the LinkList and Link models. In this section, you’ll learn how to insert links and lists in the database using Eloquent models. To limit the scope of this work, you’ll use custom Artisan commands to manage links and lists from the command line, which won’t require a web form.

      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. With Eloquent, as with other ORMs, the object itself provides methods that can be used to persist it to the database, saving you the work of writing SQL statements and manually managing data within tables.

      When working with one-to-many relationships in Laravel Eloquent, you have a few different options to save related models. In most cases, you’ll need to first set up the model representing the one side of the relationship, which in this demo is the LinkList model, and save that to the database. After doing that, you’ll be able to reference this model (which, once saved, represents a database record) when setting up the many side of the relationship (the Link model). That also means you’ll need to first have one or more lists in order to be able to create links.

      Before creating a new command to insert lists, however, you should update the existing link:new command to support the list feature.

      Open the following file in your code editor:

      app/Console/Commands/LinkNew.php
      

      You’ll see code like this:

      app/Console/Commands/LinkNew.php

      <?php
      
      namespace AppConsoleCommands;
      
      use AppModelsLink;
      use IlluminateConsoleCommand;
      use IlluminateSupportFacadesDB;
      
      class LinkNew extends Command
      {
          /**
           * The name and signature of the console command.
           *
           * @var string
           */
          protected $signature="link:new";
      
          /**
           * The console command description.
           *
           * @var string
           */
          protected $description = 'Create a New Link';
      
          /**
           * Create a new command instance.
           *
           * @return void
           */
          public function __construct()
          {
              parent::__construct();
          }
      
          /**
           * Execute the console command.
           *
           * @return int
           */
          public function handle()
          {
              $url = $this->ask('Link URL:');
      
              if (!filter_var($url, FILTER_VALIDATE_URL)) {
                  $this->error("Invalid URL. Exiting...");
                  return 1;
              }
      
              $description = $this->ask('Link Description:');
      
              $this->info("New Link:");
              $this->info($url . ' - ' . $description);
      
              if ($this->confirm('Is this information correct?')) {
                  $link = new Link();
                  $link->url = $url;
                  $link->description = $description;
                  $link->save();
      
                  $this->info("Saved.");
              }
      
              return 0;
          }
      }
      
      

      The handle() method is where the command executes its procedures. This is what it does:

      1. The ask() method, made available through the parent IlluminateConsoleCommand class, is a method used to obtain input from a user in the command line. This will prompt a user for a link, and validate the input to make sure it’s a valid URL.
      2. The script then asks for an optional description.
      3. Once values for url and description are obtained, the script will prompt for a confirmation using the confirm() method, available through the parent IlluminateConsoleCommand.
      4. When a confirmation is submitted with y or yes, the script will set up a new link object and save it to the database using the save() method, available through the model’s parent IlluminateDatabaseEloquentModel class.
      5. The script outputs a message to inform the user that the link was saved to the database, using the info output method.

      Note about return values: in the context of command line applications running on bash, non-zero return values are used to signal that the application exited in error, while 0 means it exited with success.

      If you run the link:new command now, it will break before it is finished because the database expects every link to be connected to a list. You’ll need to let the user choose which list a link should be included in, using a default list if none is provided by the user.

      The following code will ask the user to specify a list or leave it blank to use the default list. Then, it will try to locate the list or create a new list using the specified slug in case the list doesn’t exist yet. To retrieve a list provided by the user, this code uses the firstWhere method to find a list based on its slug field. Finally, it saves the new link using the links() relationship that can be accessed from the LinkList object.

      Substitute the current content in your LinkNew command class with:

      app/Console/Commands/LinkNew.php

      <?php
      
      namespace AppConsoleCommands;
      
      use AppModelsLink;
      use AppModelsLinkList;
      use IlluminateConsoleCommand;
      use IlluminateSupportFacadesDB;
      
      class LinkNew extends Command
      {
          /**
           * The name and signature of the console command.
           *
           * @var string
           */
          protected $signature="link:new";
      
          /**
           * The console command description.
           *
           * @var string
           */
          protected $description = 'Create a New Link';
      
          /**
           * Create a new command instance.
           *
           * @return void
           */
          public function __construct()
          {
              parent::__construct();
          }
      
          /**
           * Execute the console command.
           *
           * @return int
           */
          public function handle()
          {
              $url = $this->ask('Link URL');
      
              if (!filter_var($url, FILTER_VALIDATE_URL)) {
                  $this->error("Invalid URL. Exiting...");
                  return 1;
              }
      
              $description = $this->ask('Link Description');
              $list_name = $this->ask('Link List (leave blank to use default)') ?? "default";
      
              $this->info("New Link:");
              $this->info($url . ' - ' . $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 = new Link();
                  $link->url = $url;
                  $link->description = $description;
                  $list->links()->save($link);
      
                  $this->info("Saved.");
              }
      
              return 0;
          }
      }
      
      

      Save and close the file when you’re done. Then, run the command with:

      • docker-compose exec app php artisan link:new

      You’ll be prompted to provide a URL, a description, and a list name, in case you don’t want to save this link to the default list.

      Once you save your new link, if you run the link:show command, you should see the new link added to the results. However, there is no information included in the output about lists yet. You’ll need to update the LinkShow command to include a column that displays this information.

      Open the app/Console/Commands/LinkShow.php file in your code editor:

      app/Console/Commands/LinkShow.php
      

      This is how the class should look like now:

      app/Console/Commands/LinkShow.php

      <?php
      
      namespace AppConsoleCommands;
      
      use AppModelsLink;
      use IlluminateConsoleCommand;
      
      class LinkShow extends Command
      {
          /**
           * The name and signature of the console command.
           *
           * @var string
           */
          protected $signature="link:show";
      
          /**
           * The console command description.
           *
           * @var string
           */
          protected $description = 'List links saved in the database';
      
          /**
           * Create a new command instance.
           *
           * @return void
           */
          public function __construct()
          {
              parent::__construct();
          }
      
          /**
           * Execute the console command.
           *
           * @return int
           */
          public function handle()
          {
              $headers = [ 'id', 'url', 'description' ];
              $links = Link::all(['id', 'url', 'description'])->toArray();
              $this->table($headers, $links);
      
              return 0;
          }
      }
      

      You’ll see that the current handle() method is fetching a certain number of fields and converting the result to an array. By default, results come from Eloquent as an Eloquent Collection, so this function converts them to an array in order to use that data within the table() method. The problem is that when the array conversion is made, you lose the relationship between class models (Link and LinkList), which makes it more difficult to access information about the list that a link is connected to.

      You’ll need to change this code so that it fetches the full Link object, including the related objects from the database. To create an array that is suitable for using with the table() method, you can iterate through the collection of results returned by Link::all().

      Replace the current contents in the app/Console/Commands/LinkShow.php file with the following code:

      app/Console/Commands/LinkShow.php

      <?php
      
      namespace AppConsoleCommands;
      
      use AppModelsLink;
      use IlluminateConsoleCommand;
      
      class LinkShow extends Command
      {
          /**
           * The name and signature of the console command.
           *
           * @var string
           */
          protected $signature="link:show";
      
          /**
           * The console command description.
           *
           * @var string
           */
          protected $description = 'List links saved in the database';
      
          /**
           * Create a new command instance.
           *
           * @return void
           */
          public function __construct()
          {
              parent::__construct();
          }
      
          /**
           * Execute the console command.
           *
           * @return int
           */
          public function handle()
          {
              $headers = [ 'id', 'url', 'list', 'description' ];
              $links = Link::all();
      
              $table_rows = [];
              foreach ($links as $link) {
                  $table_rows[] = [ $link->id, $link->url, $link->link_list->slug, $link->description ];
              }
      
              $this->table($headers, $table_rows);
      
              return 0;
          }
      }
      
      

      Now, if you run the link:show method, you’ll see an additional column showing the list slug:

      Output

      +----+-----------------------------------------------------------------------------------------+-----------+--------------------------------------+ | id | url | list | description | +----+-----------------------------------------------------------------------------------------+-----------+--------------------------------------+ | 1 | https://digitalocean.com | default | DigitalOcean Website | | 2 | https://digitalocean.com/community/tutorials | tutorials | DO Tutorials | | 3 | https://www.digitalocean.com/community/tutorials/initial-server-setup-with-ubuntu-20-04 | tutorials | Initial server setup on ubuntu 20.04 | +----+-----------------------------------------------------------------------------------------+-----------+--------------------------------------+

      Later in this tutorial series, you’ll update the front end and main route code to show links organized into lists. For now, you’ll use the command line to add, migrate, and validate your changes to the database and models.

      The next tutorial in this series will demonstrate another way of inserting new records in the database using Eloquent models, this time through the use of database seeders.

      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