Tutorial

How To Insert New Database Records in Laravel Eloquent

PHPPHP FrameworksDatabasesLaravel
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 App\Console\Commands;

use App\Models\Link;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

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 Illuminate\Console\Command 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 Illuminate\Console\Command.
  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 Illuminate\Database\Eloquent\Model 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 App\Console\Commands;

use App\Models\Link;
use App\Models\LinkList;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

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 App\Console\Commands;

use App\Models\Link;
use Illuminate\Console\Command;

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 App\Console\Commands;

use App\Models\Link;
use Illuminate\Console\Command;

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.

Creative Commons License