Programster's Blog

Tutorials focusing on Linux, programming, and open-source

Laravel Cheatsheet - Models, Factories, and Database Queries

PHP Laravel

Models
Seeding
1. Artisan - Create Seeder Class
Factories
Database Queries
Migrations
Transactions
1. Basic Method
2. Using A Closure
References

Models

Artisan - Create Model Class

php artisan make:model MyModel

Manually Specifying Table Name

If Laravel can't correctly guess your table name from your model's name, then you may need to manually specify it by setting the $table attribute.

protected $table = 'my_table_name';

Using UUID Primary Keys

I created a separate post for creating a trait that one adds to a model for making it use UUIDs for the primary key/identifier, but is you wish to manually add the code to the model, then you would need to perform the steps below:

If you are using UUIDs as your primary key, be sure to add the following to your models:

protected $primaryKey = 'uuid';

Also, be sure to add the uuid to your model's $casts attribute so it remains a string rather than trying to be casted to an integer.

protected $casts = [
    'uuid' => 'string',
];

Also be sure to set (otherwise it will be cast to integer after calling save()):

public $incrementing = false;

Soft Deletes

To make use of Laravel's soft-delete functionality, just make sure to add the \Illuminate\Database\Eloquent\SoftDeletes trait like so:

class User extends Authenticatable
{
    use \Illuminate\Database\Eloquent\SoftDeletes;

Then make sure to have the deleted_at column in the relevant table, allowing null values. If you set Laravel to using unix timestamps (below), then it needs to be an integer type.

Unix Timestamps for created_at and updated_at

If you would rather not use the SQL timestamp type, and prefer to work with unix timestamp integers, you can get Laravel to do this by editing your model and adding:

protected $casts = [
    'created_at' => 'timestamp',
    'updated_at' => 'timestamp'
];

public function getDateFormat() { return 'U'; }

Unfortunately, settings $casts inside a trait that the model uses does not work. The casts ensures that API responses have them in unix timestamp format.

Disabling Timestamps

By default, Laravel models are expecting the tables to have the created_at and updated_at columns. If you don't want to have these, then add the following to your model:

public $timestamps = false;

Seeding

Artisan - Create Seeder Class

php artisan make:seeder MyTableSeeder

If this seeder is used for populating the database, then you may wish to call it from the DatabaseSeeder.php file like so:

    public function run()
    {
        $this->call([
            MyTableSeeder::class,
        ]);
    }

Factories

Whilst creating your seeder, you are probably going to want to create a lot of model objects with random data, to populate your database to test/dev with. This is where factories come in. Whilst factories could be used for creating "real" models, it is best to use them for creating "fake" models for seeding with. This is why mass assignment protection is automatically disabled when creating models through factories..

Create Factory

Use the following command to create a factory for "MyModel".

php artisan make:factory MyModelFactory --model=MyModel

If a factory is not specific to a given model, then feel free to just remove the --model=MyModel part.

Apply Factory To the Model

Once you have created the factory, you need to "connect" it to the model (e.g. the model will use that factory). This is because in Laravel, one performs operations on the model, rather than calling the factory directly.

We do this by adding the Illuminate\Database\Eloquent\Factories\HasFactory trait to the model.

Laravel will use its smarts to try and auto-magically figure out the factory that relates to the model, but one can manually specify the factory by overwriting the newFactory method like so:

protected static function newFactory()
{
    return Database\Factories\Administration\MyFactory::new();
}

Using The Factory

Once you have created the factory, you can call it to create a bunch of your models:

$numberOfModelsToCreate = 10;
$generatedModels = MyModel::factory()->count($numberOfModelsToCreate)->make();

Notice that we are calling the factory static method on the model, not calling the factory class itself. This is why we have the "Apply It To the Model" step above. The generated models will have been saved to the database. One does not need to manually save these models in order for them to persist.

This will generate the models with the default values specified by the factory.

If you wish to specify certain attribute values for the generated models, then provide them in an associative array like so:

$generatedModel = MyModel::factory()->make([
    'age' => 18,
]);

This will break as soon as you change the name of the column in the database. E.g. you're building fragile code, but at least it should only affect the dev/testing aspects of the code.

If you wish for your factory to create models with a varying set of values, then you can use a sequence like so.

$sequence = new Sequence(
    ['sex' => 'M'],
    ['sex' => 'F'],
);

$generatedModels = MyModel::factory()
    ->count(10)
    ->state($sequence)
    ->create();

Sequences act in a round-robin manner. E.g. it will create a male, then a female, then a male, etc until you get 5 of each sex for the 10 models in this example.

Alternatively, one could specify a closure for the sequence to use based on the index. More info about that in the official docs.

Using the Factory - Creating The Related Objects

In the previous section, we created one set of objects using the factory. It won't take long before you need to create a set of models, with the other models that relate to it. For example, one may wish to create a set of users, with each user having a set of blog posts that they created. If we have set up the has many relationship, and the factories for both models, then this is as easy as:

$user = User::factory()
    ->has(BlogPost::factory()->count(3))
    ->create();

Obviously, its a bit strange if all of our users have exactly 3 blog posts. It would be good if we can have a random count for the number of sub-objects, but I am not sure if that is possible at this point in time. I will update this post if I figure it out or if someone kindly provides a solution in the comments.

Additional Factory Features

This is just a cheatsheet to cover the basics. You may be interested in some of these topics though:

Database Queries

Where Clauses

The following queries all do the same thing:

User::where('email', $email)->exists();
User::where('email', '=', $email)->exists();

When you need to perform multiple where clauses:

$exists =
    MyModel::where('user_id', '=', $userId)
    ->where('course_id', '=', $courseId)
    ->exists();

// My preferred way
$whereClauses = [
    ['user_id', '=', $userId],
    ['course_id', '=', $courseId],
];

$exists = MyModel::where($whereClauses)->exists();

The above examples are all for AND clauses. If you need an OR relationship then you need to use orWhere. E.g.

$exists =
    MyModel::where('user_id', '=', $userId)
    ->orWhere('course_id', '=', $courseId)
    ->exists();

Example Update Statement

The following query will update the users table to set the ID to a new UUID where the legacy_id matches $legacyId

DB::table("users")
  ->where("legacy_id", "=", $legacyId)
  ->update(["id" => Str::orderedUuid()]);

The ->where clause must come before ->update.

Batch Update

It can seem that performing a batch update is impossible, or extremely difficult, but actually it's easy enough, but one needs to use escaping(through PDO quote) instead of prepared statements. It seems that Laravel is completely built around the use of prepared statements instead.

Here is an example of how one can do a batch update, whereby I am generating new UUID values for each row in the users table.

$users = DB::table("users")->select("legacy_id")->get();
$updateQueries = [];

foreach ($users as $user)
{
    $escapedUuid = DB::getPdo()->quote(Str::orderedUuid());
    $updateQueries[] = 'UPDATE "users" SET "id" = ' . $escapedUuid . ' WHERE "legacy_id" = ' . $user->legacy_id;
}

// one has to use "unprepared" because all other operations use prepared statements that can't handle multiple
// queries in one go.
$bulkUpdateQuery = implode(";", $updateQueries) . ";";
$result = DB::unprepared($bulkUpdateQuery);

Checking Uniqueness

Often you want to check if a record already exists before attempting an insert. For example, when adding a new user, you wish to check that a user with the same email doesn't already exist. For this, do the following:

if (User::where('email', '=', $email)->exists())
{
    throw new Exception("A user with that email already exists.");
}

Loading By Unique Value

If you have a unique attribute on your table and you want to load based on that, you can put something like this in your model:

/**
 * Fetch an account from the email address
 * @param string $email - the email of the account we wish to fetch.
 * @throws \Illuminate\Database\Eloquent\ModelNotFoundException
 */
public static function loadFromEmail(string $email) : Account
{
    return Account::where('email', '=', $email)->firstOrFail();
}

The beauty of this is that you no longer need to use type hinting comments as you have a return type on your function.

Pluck

You can use ->pluck() to just get the values of one column. For example, the code below will get you a collection object of all the titles of posts where the category_id is 3.

$postTitles = DB::table("posts")
    ->where("category_id", 3)
    ->pluck("title");

The specific collection object is one of Illuminate\Support\Collection

Converting Collection To Array

Queries will return Laravel collections. However, you often want the array form instead. You can do this by executing the ->all() method on the collection. The example below will get an array of all the post titles.

$postTitles = DB::table("posts")
    ->where("category_id", 3)
    ->pluck("title")
    ->all();

Raw Select

Sometimes you need to do a complicated select query that either has joins, or nexted queries, in which case it may be easier just to write it raw. Here is an example:

$rawSelectQuery =
    "SELECT * FROM `users`" .
    " WHERE `type_id`={$myTypeObject->id}" .
    " AND `id` in (" .
        "SELECT `some_id` FROM `table2` WHERE `someColumnName`={$someValue}" .
    ")" .
    " OR `id` in (SELECT `some_id` FROM `global_items`)";

$results = \DB::select(\DB::raw($rawSelectQuery));

The code above would perform the query without any escaping performed by the framework, so it would be up to you to ensure that the values are escaped.

Alternatively, you could have the framework perform value-binding (prepared statements) for safety, by wrapping in DB::select like so:

$rawQuery =
    "SELECT * FROM `users`" .
    " WHERE `type_id`= :typeId .
    " AND `id` in (" .
        "SELECT `some_id` FROM `table2` WHERE `someColumnName`= :someOtherValue .
    ")" .
    " OR `id` in (SELECT `some_id` FROM `global_items`)";

$substitutions = array(
    'typeId' => $someUnescapedInputFromTheUser,
    'someOtherValue' => $someOtherUnescapedInputFromTheUser,
);

$results = DB::select(
    DB::raw($rawQuery), 
    $substitutions
);

Please note that in both of the above examples, the returned result is simply an array of stdClass objects representing the rows in the database. Thus one can loop over the results like so:

foreach ($results as $row)
{
    $attribute1 = $row->attribute1;
    $attribute2 = $row->attribute2;
}

Using DB::select expects a return of data from the database, and will result in an error if there isn't. If you are simply executing a statement with no result, such as an update request, then just use DB::statement. If you want it to be an unprepared statement (no value binding), then use DB::unprepared.

Another good blog post specifically on raw queries.

Using Hydrate to Convert Raw Select Query To Models

If one wished to retrieve an array of model objects instead, then one would use the hydrate static method on the model like so:

$users = User::hydrate(\DB::select($rawUserSelectQuery))->all();

The ->all() simply converts the result from a Laravel collection to the underlying array.

Inserting Data

This is one way you could insert multiple rows into a database at once (other than running a raw/unprepared query that you build yourself).

DB::table('users')->insert([
    ['email' => 'taylor@example.com', 'votes' => 0],
    ['email' => 'dayle@example.com', 'votes' => 0]
]);

Migrations

Warning - Can't Mix and Match

All of the instructions below that are to do with migrations use static methods on the Schema class. E.g. Schema::create. When working on large migrations that perform a lot of operations, such as creating a table, and then filling it with data, it can be tempting to mix and match use of Schema:: with other statements like DB::statement or DB::raw. Unfortunately, this will usually result in issues, as Schema:: code builds commands that get added to a queue, which get executed at the end of each migration class/file, whereas other code, such as DB::statement act immediately.

This would mean you would get an error if you were to create a table using Schema:: and then tried to fill it with some data using DB::satatement. This is because the table would not exist yet when DB::satatement executed its query.

Unfortunately, it appears there is no way to get Schema to flush its queue in the middle of the migration, so if you come across this issue, you will need to split your migration into two or more migrations, so that the queries built by Schema have executed before your DB::statement code. E.g. add one migration for creating the tables. Then create another migration for filling the tables with the data.

Create Table

Schema::create('my_things', function (Blueprint $table) {
    $table->uuid('id')->primary();
    $table->string('some_nullable_string_field')->nullable();
    $table->uuid('user_id')->unique();
    $table->integer('created_at');
    $table->integer('updated_at');

    // make user_id field a foreign key pointing to users table.
    $table->foreign('user_id')->references('id')->on('users');
});

This example is what I would use because I use unix timestamps for my timestamps, and UUIDs for my identifiers.

Change Column Type

Schema::table('my_table_name', function (Blueprint $table) {
    $table->bigInteger('my_column_name')->change();
});

Rename Column

Schema::table('my_table_name', function (Blueprint $table) {
    $table->renameColumn('original_name', 'new_name');
});

Drop Table

Schema::drop('my_table_name');

Rename Table

Schema::rename('original_table_name', 'new_table_name');

Add Foreign Key

Schema::table('my_table_name', function (Blueprint $table) {
    $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade')->onUpdate('cascade');
});

Create Combined Unique Key

Schema::table('my_table_name', function (Blueprint $table) {
    $table->unique(['column1', 'column2']);
});

Transactions

Database transactions are incredibly useful/important for ensuring that operations either complete in their entirety or not at all. There is nothing worse that an operation only half-completing and failing to clean up. Luckily, transactions are easy to achieve in Laravel, and can be performed around normal database statements and/or Eloquent operations, like the two different methods shown below:

Basic Method

try 
{
    \DB::beginTransaction();

    $user = \Auth::user();
    $user->fill($request->all());
    $user->push();

    \DB::commit();
} 
catch (Throwable $e) 
{
    \DB::rollback();
}

Using A Closure

Alternatively, you can put all of the operations within a closure like as follows:

DB::transaction(function() {
    DB::statement(/* stuff here */);
    DB::insert(/* stuff here */);
    DB::commit();
});

Everything inside the Closure executes within a transaction. If an exception occurs it will rollback automatically.

Be careful if operating on objects though, as you will likely need to use use like shown below:

DB::transaction(function() use ($user, $request) {
    DB::statement(/* stuff here */);

    $user->fill($request->all());
    $user->push();

    DB::commit();
});

References

Last updated: 6th July 2023
First published: 18th September 2021

This blog is created by Stuart Page

I'm a freelance web developer and technology consultant based in Surrey, UK, with over 10 years experience in web development, DevOps, Linux Administration, and IT solutions.

Need support with your infrastructure or web services?

Get in touch