Laravel provides a suite of tools for interacting with your application’s databases, but the most notable is Eloquent, Laravel’s ActiveRecord ORM (object-relational mapper).
Eloquent is one of Laravel’s most popular and influential features. It’s a great example of how Laravel is different from the majority of PHP frameworks; in a world of DataMapper ORMs that are powerful but complex, Eloquent stands out for its simplicity. There’s one class per table, which is responsible for retrieving, representing, and persisting data in that table.
Whether or not you choose to use Eloquent, however, you’ll still get a ton of benefit from the other database tools Laravel provides. So, before we dig into Eloquent, we’ll start by covering the basics of Laravel’s database functionality: migrations, seeders, and the query builder.
Then we’ll cover Eloquent: defining your models; inserting, updating, and deleting; customizing your responses with accessors, mutators, and attribute casting; and finally relationships. There’s a lot going on here, and it’s easy to get overwhelmed, but if we take it one step at a time we’ll make it through.
Before we get into how to use Laravel’s database tools, let’s pause for a second and go over how to configure your database credentials and connections.
The configuration for database access lives in config/database.php and .env. Like many other configuration areas in Laravel, you can define multiple “connections” and then decide which the code will use by default.
By default, there’s one connection for each of the drivers, as you can see in Example 5-1.
'connections'
=>
[
'sqlite'
=>
[
'driver'
=>
'sqlite'
,
'url'
=>
env
(
'DATABASE_URL'
),
'database'
=>
env
(
'DB_DATABASE'
,
database_path
(
'database.sqlite'
)),
'prefix'
=>
''
,
'foreign_key_constraints'
=>
env
(
'DB_FOREIGN_KEYS'
,
true
),
],
'mysql'
=>
[
'driver'
=>
'mysql'
,
'url'
=>
env
(
'DATABASE_URL'
),
'host'
=>
env
(
'DB_HOST'
,
'127.0.0.1'
),
'port'
=>
env
(
'DB_PORT'
,
'3306'
),
'database'
=>
env
(
'DB_DATABASE'
,
'forge'
),
'username'
=>
env
(
'DB_USERNAME'
,
'forge'
),
'password'
=>
env
(
'DB_PASSWORD'
,
''
),
'unix_socket'
=>
env
(
'DB_SOCKET'
,
''
),
'charset'
=>
'utf8mb4'
,
'collation'
=>
'utf8mb4_unicode_ci'
,
'prefix'
=>
''
,
'prefix_indexes'
=>
true
,
'strict'
=>
true
,
'engine'
=>
null
,
'options'
=>
extension_loaded
(
'pdo_mysql'
)
?
array_filter
([
PDO
::
MYSQL_ATTR_SSL_CA
=>
env
(
'MYSQL_ATTR_SSL_CA'
),
])
:
[],
],
'pgsql'
=>
[
'driver'
=>
'pgsql'
,
'url'
=>
env
(
'DATABASE_URL'
),
'host'
=>
env
(
'DB_HOST'
,
'127.0.0.1'
),
'port'
=>
env
(
'DB_PORT'
,
'5432'
),
'database'
=>
env
(
'DB_DATABASE'
,
'forge'
),
'username'
=>
env
(
'DB_USERNAME'
,
'forge'
),
'password'
=>
env
(
'DB_PASSWORD'
,
''
),
'charset'
=>
'utf8'
,
'prefix'
=>
''
,
'prefix_indexes'
=>
true
,
'schema'
=>
'public'
,
'sslmode'
=>
'prefer'
,
],
'sqlsrv'
=>
[
'driver'
=>
'sqlsrv'
,
'url'
=>
env
(
'DATABASE_URL'
),
'host'
=>
env
(
'DB_HOST'
,
'localhost'
),
'port'
=>
env
(
'DB_PORT'
,
'1433'
),
'database'
=>
env
(
'DB_DATABASE'
,
'forge'
),
'username'
=>
env
(
'DB_USERNAME'
,
'forge'
),
'password'
=>
env
(
'DB_PASSWORD'
,
''
),
'charset'
=>
'utf8'
,
'prefix'
=>
''
,
'prefix_indexes'
=>
true
,
],
]
Nothing is stopping you from deleting or modifying these named connections or creating your own. You can create new named connections, and you’ll be able to set the drivers (MySQL, Postgres, etc.) in them. So, while there’s one connection per driver by default, that’s not a constraint; you could have five different connections, all with the mysql
driver, if you wanted.
Each connection allows you to define the properties necessary for connecting to and customizing each connection type.
There are a few reasons for the idea of multiple drivers. To start with, the “connections” section as it comes out of the box is a simple template that makes it easy to start apps that use any of the supported database connection types. In many apps, you can pick the database connection you’ll be using, fill out its information, and even delete the others if you’d like. I usually just keep them all there, in case I might eventually use them.
But there are also some cases where you might need multiple connections within the same application. For example, you might use different database connections for two different types of data, or you might read from one and write to another. Support for multiple connections makes this possible.
Often services like Heroku will provide an environment variable with a URL that contains all of the information you need to connect to the database. It’ll look something like this:
DATABASE_URL="mysql://root:[email protected]/forge?charset=UTF-8"
In Laravel 5.8+ you don’t have to write code to parse this variable out; instead, pass it in as the DATABASE_URL
environment variable (or assign the config(connections.mysql.url)
configuration option to equal another environment variable) and Laravel will parse out that URL for you.
The config/database.php configuration section has quite a few other configuration settings. You can configure Redis access, customize the table name used for migrations, determine the default connection, and toggle whether non-Eloquent calls return stdClass
or array instances.
With any service in Laravel that allows connections from multiple sources—sessions can be backed by the database or file storage, the cache can use Redis or Memcached, databases can use MySQL or PostgreSQL—you can define multiple connections and also choose that a particular connection will be the “default,” meaning it will be used any time you don’t explicitly ask for a particular connection. Here’s how you ask for a specific connection, if you want to:
$users
=
DB
::
connection
(
'secondary'
)
->
select
(
'select * from users'
);
Modern frameworks like Laravel make it easy to define your database structure with code-driven migrations. Every new table, column, index, and key can be defined in code, and any new environment can be brought from bare database to your app’s perfect schema in seconds.
A migration is a single file that defines two things: the modifications desired when running this migration up and, optionally, the modifications desired when running this migration down.
Example 5-2 shows what the default “create users table” migration that comes with Laravel looks like.
<?
php
use
IlluminateDatabaseSchemaBlueprint
;
use
IlluminateDatabaseMigrationsMigration
;
class
CreateUsersTable
extends
Migration
{
/**
* Run the migrations.
*
* @return void
*/
public
function
up
()
{
Schema
::
create
(
'users'
,
function
(
Blueprint
$table
)
{
$table
->
bigIncrements
(
'id'
);
$table
->
string
(
'name'
);
$table
->
string
(
'email'
)
->
unique
();
$table
->
timestamp
(
'email_verified_at'
)
->
nullable
();
$table
->
string
(
'password'
);
$table
->
rememberToken
();
$table
->
timestamps
();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public
function
down
()
{
Schema
::
dropIfExists
(
'users'
);
}
}
The email_verified_at
column is only present in apps built in Laravel 5.7 and later. It stores a timestamp indicating when the user verified their email address.
As you can see, we have an up()
method and a down()
method. up()
tells the migration to create a new table named users
with a few fields, and down()
tells it to drop the users
table.
As you will see in Chapter 8, Laravel provides a series of command-line tools you can use to interact with your app and generate boilerplate files. One of these commands allows you to create a migration file. You can run it using php artisan make:migration
, and it has a single parameter, which is the name of the migration. For example, to create the table we just covered, you would run php artisan make:migration create_users_table
.
There are two flags you can optionally pass to this command. --create=table_name
prefills the migration with code designed to create a table named table_name
, and --table=table_name
just prefills the migration for modifications to an existing table. Here are a few examples:
php
artisan
make
:
migration
create_users_table
php
artisan
make
:
migration
add_votes_to_users_table
--
table
=
users
php
artisan
make
:
migration
create_users_table
--
create
=
users
We already saw in the default create_users_table
migration that our migrations depend on the Schema
facade and its methods. Everything we can do in these migrations will rely on the methods of Schema
.
To create a new table in a migration, use the create()
method—the first parameter is the table name, and the second is a closure that defines its columns:
Schema
::
create
(
'users'
,
function
(
Blueprint
$table
)
{
// Create columns here
});
To create new columns in a table, whether in a create table call or a modify table call, use the instance of Blueprint
that’s passed into your closure:
Schema
::
create
(
'users'
,
function
(
Blueprint
$table
)
{
$table
->
string
(
'name'
);
});
Let’s look at the various methods available on Blueprint
instances for creating columns. I’ll describe how they work in MySQL, but if you’re using another database, Laravel will just use the closest equivalent.
The following are the simple field Blueprint
methods:
integer(colName)
, tinyInteger(colName)
, smallInteger(colName)
, mediumInteger(colName)
, bigInteger(colName)
string(colName, length)
binary(colName)
boolean(colName)
char(colName, length)
datetime(colName)
decimal(colName, precision, scale)
Adds a DECIMAL
column, with precision and scale—for example, decimal('amount', 5, 2)
specifies a precision of 5 and a scale of 2
double(colName, total digits, digits after decimal)
Adds a DOUBLE
column—for example, double('tolerance', 12, 8)
specifies 12 digits long, with 8 of those digits to the right of the decimal place, as in 7204.05691739
enum(colName, [choiceOne, choiceTwo])
float(colName, precision, scale)
json(colName)
and jsonb(colName)
Adds a JSON
or JSONB
column (or a TEXT
column in Laravel 5.1)
text(colName)
, mediumText(colName)
, longText(colName)
time(colName)
timestamp(colName)
uuid(colName)
And these are the special (joined) Blueprint
methods:
increments(colName)
and bigIncrements(colName)
Add an unsigned incrementing INTEGER
or BIG INTEGER
primary key ID
timestamps()
and nullableTimestamps()
rememberToken()
Adds a remember_token
column (VARCHAR(100)
) for user “remember me” tokens
softDeletes()
morphs(colName)
For a provided colName
, adds an integer colName_id
and a string colName_type
(e.g., morphs(tag)
adds integer tag_id
and string tag_type
); for use in polymorphic relationships
Most of the properties of a field definition—its length, for example—are set as the second parameter of the field creation method, as we saw in the previous section. But there are a few other properties that we’ll set by chaining more method calls after the creation of the column. For example, this email
field is nullable and will be placed (in MySQL) right after the last_name
field:
Schema
::
table
(
'users'
,
function
(
Blueprint
$table
)
{
$table
->
string
(
'email'
)
->
nullable
()
->
after
(
'last_name'
);
});
The following methods are used to set additional properties of a field:
nullable()
default('default content')
Specifies the default content for this column if no value is provided
unsigned()
Marks integer columns as unsigned (not negative or positive, but just an integer)
first()
(MySQL only)after(colName)
(MySQL only)unique()
primary()
index()
Note that unique()
, primary()
, and index()
can also be used outside of the fluent column building context, which we’ll cover later.
To modify a column, just write the code you would write to create the column as if it were new, and then append a call to the change()
method after it.
Before you modify any columns (or drop any columns in SQLite), you’ll need to run composer require doctrine/dbal
.
So, if we have a string column named name
that has a length of 255
and we want to change its length to 100
, this is how we would write it:
Schema
::
table
(
'users'
,
function
(
Blueprint
$table
)
{
$table
->
string
(
'name'
,
100
)
->
change
();
});
The same is true if we want to adjust any of its properties that aren’t defined in the method name. To make a field nullable, we do this:
Schema
::
table
(
'contacts'
,
function
(
Blueprint
$table
)
{
$table
->
string
(
'deleted_at'
)
->
nullable
()
->
change
();
});
Here’s how we rename a column:
Schema
::
table
(
'contacts'
,
function
(
Blueprint
$table
)
{
$table
->
renameColumn
(
'promoted'
,
'is_promoted'
);
});
And this is how we drop a column:
Schema
::
table
(
'contacts'
,
function
(
Blueprint
$table
)
{
$table
->
dropColumn
(
'votes'
);
});
If you try to drop or modify multiple columns within a single migration closure and you are using SQLite, you’ll run into errors.
In Chapter 12 I recommend that you use SQLite for your testing database, so even if you’re using a more traditional database, you may want to consider this a limitation for testing purposes.
However, you don’t have to create a new migration for each. Instead, just create multiple calls to Schema::table()
within the up()
method of your migration:
public
function
up
()
{
Schema
::
table
(
'contacts'
,
function
(
Blueprint
$table
)
{
$table
->
dropColumn
(
'is_promoted'
);
});
Schema
::
table
(
'contacts'
,
function
(
Blueprint
$table
)
{
$table
->
dropColumn
(
'alternate_email'
);
});
}
We’ve covered how to create, modify, and delete columns. Let’s move on to indexing and relating them.
If you’re not familiar with indexes, your databases can survive if you just never use them, but they’re pretty important for performance optimization and for some data integrity controls with regard to related tables. I’d recommend reading up on them, but if you absolutely must, you can skip this section for now.
Check out Example 5-3 for examples of how to add indexes to your column.
// After columns are created...
$table
->
primary
(
'primary_id'
);
// Primary key; unnecessary if used increments()
$table
->
primary
([
'first_name'
,
'last_name'
]);
// Composite keys
$table
->
unique
(
'email'
);
// Unique index
$table
->
unique
(
'email'
,
'optional_custom_index_name'
);
// Unique index
$table
->
index
(
'amount'
);
// Basic index
$table
->
index
(
'amount'
,
'optional_custom_index_name'
);
// Basic index
Note that the first example, primary()
, is not necessary if you’re using the increments()
or bigIncrements()
methods to create your index; this will automatically add a primary key index for you.
We can remove indexes as shown in Example 5-4.
$table
->
dropPrimary
(
'contacts_id_primary'
);
$table
->
dropUnique
(
'contacts_email_unique'
);
$table
->
dropIndex
(
'optional_custom_index_name'
);
// If you pass an array of column names to dropIndex, it will
// guess the index names for you based on the generation rules
$table
->
dropIndex
([
'email'
,
'amount'
]);
To add a foreign key that defines that a particular column references a column on another table, Laravel’s syntax is simple and clear:
$table
->
foreign
(
'user_id'
)
->
references
(
'id'
)
->
on
(
'users'
);
Here we’re adding a foreign
index on the user_id
column, showing that it references the id
column on the users
table. Couldn’t get much simpler.
If we want to specify foreign key constraints, we can do that too, with onDelete()
and onUpdate()
. For example:
$table
->
foreign
(
'user_id'
)
->
references
(
'id'
)
->
on
(
'users'
)
->
onDelete
(
'cascade'
);
To drop a foreign key, we can either delete it by referencing its index name (which is automatically generated by combining the names of the columns and tables being referenced):
$table
->
dropForeign
(
'contacts_user_id_foreign'
);
or by passing it an array of the fields that it’s referencing in the local table:
$table
->
dropForeign
([
'user_id'
]);
Once you have your migrations defined, how do you run them? There’s an Artisan command for that:
php artisan migrate
This command runs all “outstanding” migrations (by running the up()
method on each). Laravel keeps track of which migrations you have run and which you haven’t. Every time you run this command, it checks whether you’ve run all available migrations, and if you haven’t, it’ll run any that remain.
There are a few options in this namespace that you can work with. First, you can run your migrations and your seeds (which we’ll cover next):
php artisan migrate --seed
You can also run any of the following commands:
migrate:install
Creates the database table that keeps track of which migrations you have and haven’t run; this is run automatically when you run your migrations, so you can basically ignore it.
migrate:reset
Rolls back every database migration you’ve run on this instance.
migrate:refresh
Rolls back every database migration you’ve run on this instance, and then runs every migration available. It’s the same as running migrate:reset
and then migrate
, one after the other.
migrate:fresh
Drops all of your tables and runs every migration again. It’s the same as refresh
but doesn’t bother with the “down” migrations—it just deletes the tables and then runs the “up” migrations again.
migrate:rollback
Rolls back just the migrations that ran the last time you ran migrate
, or, with the added option --step=n
, rolls back the number of migrations you specify.
migrate:status
Shows a table listing every migration, with a Y
or N
next to each showing whether or not it has run yet in this environment.
If you’re running migrations on your local machine and your .env file points to a database in a Vagrant box, your migrations will fail. You’ll need to ssh
into your Vagrant box and then run the migrations from there. The same is true for seeds and any other Artisan commands that affect or read from the database.
Seeding with Laravel is so simple, it has gained widespread adoption as a part of normal development workflows in a way it hasn’t in previous PHP frameworks. There’s a database/seeds folder that comes with a DatabaseSeeder
class, which has a run()
method that is called when you call the seeder.
There are two primary ways to run the seeders: along with a migration, or separately.
To run a seeder along with a migration, just add --seed
to any migration call:
php
artisan
migrate
--
seed
php
artisan
migrate
:
refresh
--
seed
And to run it independently:
php
artisan
db
:
seed
php
artisan
db
:
seed
--
class
=
VotesTableSeeder
This will call the run()
method of the DatabaseSeeder
by default, or the seeder class specified by --class
.
To create a seeder, use the make:seeder
Artisan command:
php
artisan
make
:
seeder
ContactsTableSeeder
You’ll now see a ContactsTableSeeder
class show up in the database/seeds directory. Before we edit it, let’s add it to the DatabaseSeeder
class, as shown in Example 5-5, so it will run when we run our seeders.
// database/seeds/DatabaseSeeder.php
...
public
function
run
()
{
$this
->
call
(
ContactsTableSeeder
::
class
);
}
Now let’s edit the seeder itself. The simplest thing we can do there is manually insert a record using the DB
facade, as illustrated in Example 5-6.
<?
php
use
IlluminateDatabaseSeeder
;
use
IlluminateDatabaseEloquentModel
;
class
ContactsTableSeeder
extends
Seeder
{
public
function
run
()
{
DB
::
table
(
'contacts'
)
->
insert
([
'name'
=>
'Lupita Smith'
,
'email'
=>
'[email protected]'
,
]);
}
}
This will get us a single record, which is a good start. But for truly functional seeds, you’ll likely want to loop over some sort of random generator and run this insert()
many times, right? Laravel has a feature for that.
Model factories define one (or more) patterns for creating fake entries for your database tables. By default each factory is named after an Eloquent class, but you can also just name them after the table if you’re not going to work with Eloquent. Example 5-7 shows the same factory set up both ways.
$factory
->
define
(
User
::
class
,
function
(
FakerGenerator
$faker
)
{
return
[
'name'
=>
$faker
->
name
,
];
});
$factory
->
define
(
'users'
,
function
(
FakerGenerator
$faker
)
{
return
[
'name'
=>
$faker
->
name
,
];
});
Theoretically you can name these factories anything you like, but naming the factory after your Eloquent class is the most idiomatic approach.
Model factories are located in database/factories. In Laravel 5.5 and later each factory is usually defined in its own class, with a key (name) and a closure defining how to create a new instance of the defined class. The $factory->define()
method takes the factory name as the first parameter and a closure that’s run for each generation as the second parameter.
In Laravel prior to 5.5, all factories should be defined in database/factories/ModelFactory.php. There are no separate classes for each factory until 5.5.
To generate a new factory class, use the Artisan make:factory
command; just like with naming the factory keys, it’s also most common to name factory classes after the Eloquent models they’re meant to generate instances of:
php
artisan
make
:
factory
ContactFactory
This will generate a new file within the database/factories directory called ContactFactory.php. The simplest factory we could define for a contact might look something like Example 5-8:
$factory
->
define
(
Contact
::
class
,
function
(
FakerGenerator
$faker
)
{
return
[
'name'
=>
'Lupita Smith'
,
'email'
=>
'[email protected]'
,
];
});
Now we can use the factory()
global helper to create an instance of Contact
in our seeding and testing:
// Create one
$contact
=
factory
(
Contact
::
class
)
->
create
();
// Create many
factory
(
Contact
::
class
,
20
)
->
create
();
However, if we used that factory to create 20 contacts, all 20 would have the same information. That’s less useful.
We will get even more benefit from model factories when we take advantage of the instance of Faker
that’s passed into the closure; Faker
makes it easy to randomize the creation of structured fake data. The previous example now turns into Example 5-9.
$factory
->
define
(
Contact
::
class
,
function
(
FakerGenerator
$faker
)
{
return
[
'name'
=>
$faker
->
name
,
'email'
=>
$faker
->
,
];
});
Now, every time we create a fake contact using this model factory, all of our properties will be randomly generated.
There are two primary contexts in which we’ll use model factories: testing, which we’ll cover in Chapter 12, and seeding, which we’ll cover here. Let’s write a seeder using a model factory; take a look at Example 5-10.
$post
=
factory
(
Post
::
class
)
->
create
([
'title'
=>
'My greatest post ever'
,
]);
// Pro-level factory; but don't get overwhelmed!
factory
(
User
::
class
,
20
)
->
create
()
->
each
(
function
(
$u
)
use
(
$post
)
{
$post
->
comments
()
->
save
(
factory
(
Comment
::
class
)
->
make
([
'user_id'
=>
$u
->
id
,
]));
});
To create an object, we use the factory()
global helper and pass it the name of the factory—which, as we just saw, is the name of the Eloquent class we’re generating an instance of. That returns the factory, and then we can run one of two methods on it: make()
or create()
.
Both methods generate an instance of this specified model, using the definition in the factory file. The difference is that make()
creates the instance but doesn’t (yet) save it to the database, whereas create()
saves it to the database instantly. You can see both in use in the two examples in Example 5-10.
The second example will make more sense once we cover relationships in Eloquent later in this chapter.
If you pass an array to either make()
or create()
, you can override specific keys from the factory, like we did in Example 5-10 to set the user_id
on the comment and to manually set the title of our post.
If you pass a number as the second parameter to the factory()
helper, you can specify that you’re creating more than one instance. Instead of returning a single instance, it’ll return a collection of instances. This means you can treat the result like an array, you can associate each of its instances with another entity, or you can use other entity methods on each instance—like we used each()
in Example 5-10 to add a comment from each newly created user.
Now that we’ve covered the most common uses for and arrangements of model factories, let’s dive into some of the more complicated ways we can use them.
Sometimes you need to create a related item along with the item you’re creating. You can use a closure on that property to create a related item and pull its ID, as shown in Example 5-11.
$factory
->
define
(
Contact
::
class
,
function
(
FakerGenerator
$faker
)
{
return
[
'name'
=>
'Lupita Smith'
,
'email'
=>
'[email protected]'
,
'company_id'
=>
function
()
{
return
factory
(
AppCompany
::
class
)
->
create
()
->
id
;
},
];
});
Each closure is passed a single parameter, which contains the array form of the generated item up until that point. This can be used in other ways, as demonstrated in Example 5-12.
$factory
->
define
(
Contact
::
class
,
function
(
FakerGenerator
$faker
)
{
return
[
'name'
=>
'Lupita Smith'
,
'email'
=>
'[email protected]'
,
'company_id'
=>
function
()
{
return
factory
(
AppCompany
::
class
)
->
create
()
->
id
;
},
'company_size'
=>
function
(
$contact
)
{
// Uses the "company_id" property generated above
return
AppCompany
::
find
(
$contact
[
'company_id'
])
->
size
;
},
];
});
Let’s go back to ContactFactory.php (from Example 5-8 and Example 5-9) for a second. We have a base Contact
factory defined:
$factory
->
define
(
Contact
::
class
,
function
(
FakerGenerator
$faker
)
{
return
[
'name'
=>
$faker
->
name
,
'email'
=>
$faker
->
,
];
});
But sometimes you need more than one factory for a class of object. What if we need to be able to add some contacts who are very important people (VIPs)? We can use the state()
method to define a second factory state for this, as seen in Example 5-13. The first parameter to state()
is still the name of the entity you’re generating, the second is the name of your state, and the third is an array of any attributes you want to specifically set for this state.
$factory
->
define
(
Contact
::
class
,
function
(
FakerGenerator
$faker
)
{
return
[
'name'
=>
$faker
->
name
,
'email'
=>
$faker
->
,
];
});
$factory
->
state
(
Contact
::
class
,
'vip'
,
[
'vip'
=>
true
,
]);
If the modified attributes require more than a simple static value, you can pass a closure instead of an array as the second parameter and then return an array of the attributes you want to modify, like in Example 5-14.
$factory
->
state
(
Contact
::
class
,
'vip'
,
function
(
FakerGenerator
$faker
)
{
return
[
'vip'
=>
true
,
'company'
=>
$faker
->
company
,
];
});
Now, let’s make an instance of a specific state:
$vip
=
factory
(
Contact
::
class
)
->
state
(
'vip'
)
->
create
();
$vips
=
factory
(
Contact
::
class
,
3
)
->
state
(
'vip'
)
->
create
();
In projects running versions of Laravel prior to 5.3, factory states were called factory types, and you’ll want to use $factory->defineAs()
instead of $factory->state()
. You can learn more about this in the 5.2 docs.
Whew. That was a lot. Don’t worry if that was tough to follow—the last bit was definitely higher-level stuff. Let’s get back down to the basics and talk about the core of Laravel’s database tooling: the query builder.
Now that you’re connected and you’ve migrated and seeded your tables, let’s get started with how to use the database tools. At the core of every piece of Laravel’s database functionality is the query builder, a fluent interface for interacting with several different types of databases with a single clear API.
Laravel’s database architecture can connect to MySQL, Postgres, SQLite, and SQL Server through a single interface, with just the change of a few configuration settings.
If you’ve ever used a PHP framework, you’ve likely used a tool that allows you to run “raw” SQL queries with basic escaping for security. The query builder is that, with a lot of convenience layers and helpers on top. So, let’s start with some simple calls.
Before we get into building complex queries with fluent method chaining, let’s take a look at a few sample query builder commands. The DB
facade is used both for query builder chaining and for simpler raw queries, as illustrated in Example 5-15.
// Basic statement
DB
::
statement
(
'drop table users'
);
// Raw select, and parameter binding
DB
::
select
(
'select * from contacts where validated = ?'
,
[
true
]);
// Select using the fluent builder
$users
=
DB
::
table
(
'users'
)
->
get
();
// Joins and other complex calls
DB
::
table
(
'users'
)
->
join
(
'contacts'
,
function
(
$join
)
{
$join
->
on
(
'users.id'
,
'='
,
'contacts.user_id'
)
->
where
(
'contacts.type'
,
'donor'
);
})
->
get
();
As you saw in Example 5-15, it’s possible to make any raw call to the database using the DB
facade and the statement()
method: DB::statement('SQL statement here')
.
But there are also specific methods for various common actions: select()
, insert()
, update()
, and delete()
. These are still raw calls, but there are differences. First, using update()
and delete()
will return the number of rows affected, whereas statement()
won’t; second, with these methods it’s clearer to future developers exactly what sort of statement you’re making.
Laravel’s database architecture allows for the use of PDO parameter binding, which protects your queries from potential SQL attacks. Passing a parameter to a statement is as simple as replacing the value in your statement with a ?
, then adding the value to the second parameter of your call:
$usersOfType
=
DB
::
select
(
'select * from users where type = ?'
,
[
$type
]
);
You can also name those parameters for clarity:
$usersOfType
=
DB
::
select
(
'select * from users where type = :type'
,
[
'type'
=>
$userType
]
);
From here, the raw commands all look pretty much the same. Raw inserts look like this:
DB
::
insert
(
'insert into contacts (name, email) values (?, ?)'
,
[
'sally'
,
'[email protected]'
]
);
Up until now, we haven’t actually used the query builder, per se. We’ve just used simple method calls on the DB
facade. Let’s actually build some queries.
The query builder makes it possible to chain methods together to, you guessed it, build a query. At the end of your chain you’ll use some method—likely get()
—to trigger the actual execution of the query you’ve just built.
Let’s take a look at a quick example:
$usersOfType
=
DB
::
table
(
'users'
)
->
where
(
'type'
,
$type
)
->
get
();
Here, we built our query—users
table, $type
type—and then we executed the query and got our result. Note that, unlike the previous calls, this will return a collection of stdClass objects instead of an array.
Let’s take a look at what methods the query builder allows you to chain. The methods can be split up into what I’ll call constraining methods, modifying methods, conditional methods, and ending/returning methods.
These methods take the query as it is and constrain it to return a smaller subset of possible data:
select()
Allows you to choose which columns you’re selecting:
$emails
=
DB
::
table
(
'contacts'
)
->
select
(
'email'
,
'email2 as second_email'
)
->
get
();
// Or
$emails
=
DB
::
table
(
'contacts'
)
->
select
(
'email'
)
->
addSelect
(
'email2 as second_email'
)
->
get
();
where()
Allows you to limit the scope of what’s being returned using WHERE
. By default, the signature of the where()
method is that it takes three parameters—the column, the comparison operator, and the value:
$newContacts
=
DB
::
table
(
'contact'
)
->
where
(
'created_at'
,
'>'
,
now
()
->
subDay
())
->
get
();
However, if your comparison is =
, which is the most common comparison, you can drop the second operator:
$vipContacts = DB::table('contacts')->where('vip',true)->get();
If you want to combine where()
statements, you can either chain them after each other, or pass an array of arrays:
$newVips
=
DB
::
table
(
'contacts'
)
->
where
(
'vip'
,
true
)
->
where
(
'created_at'
,
'>'
,
now
()
->
subDay
());
// Or
$newVips
=
DB
::
table
(
'contacts'
)
->
where
([
[
'vip'
,
true
],
[
'created_at'
,
'>'
,
now
()
->
subDay
()],
]);
orWhere()
Creates simple OR WHERE
statements:
$priorityContacts
=
DB
::
table
(
'contacts'
)
->
where
(
'vip'
,
true
)
->
orWhere
(
'created_at'
,
'>'
,
now
()
->
subDay
())
->
get
();
To create a more complex OR WHERE
statement with multiple conditions, pass orWhere()
a closure:
$contacts
=
DB
::
table
(
'contacts'
)
->
where
(
'vip'
,
true
)
->
orWhere
(
function
(
$query
)
{
$query
->
where
(
'created_at'
,
'>'
,
now
()
->
subDay
())
->
where
(
'trial'
,
false
);
})
->
get
();
If you are using orWhere()
calls in conjunction with multiple where()
calls, you need to be very careful to ensure the query is doing what you think it is. This isn’t because of any fault with Laravel, but because a query like the following might not do what you expect:
$canEdit
=
DB
::
table
(
'users'
)
->
where
(
'admin'
,
true
)
->
orWhere
(
'plan'
,
'premium'
)
->
where
(
'is_plan_owner'
,
true
)
->
get
();
SELECT
*
FROM
users
WHERE
admin
=
1
OR
plan
=
'premium'
AND
is_plan_owner
=
1
;
If you want to write SQL that says “if this OR (this and this),” which is clearly the intention in the previous example, you’ll want to pass a closure into the orWhere()
call:
$canEdit
=
DB
::
table
(
'users'
)
->
where
(
'admin'
,
true
)
->
orWhere
(
function
(
$query
)
{
$query
->
where
(
'plan'
,
'premium'
)
->
where
(
'is_plan_owner'
,
true
);
})
->
get
();
SELECT
*
FROM
users
WHERE
admin
=
1
OR
(
plan
=
'premium'
AND
is_plan_owner
=
1
);
whereBetween(colName, [low, high])
Allows you to scope a query to return only rows where a column is between two values (inclusive of the two values):
$mediumDrinks
=
DB
::
table
(
'drinks'
)
->
whereBetween
(
'size'
,
[
6
,
12
])
->
get
();
The same works for whereNotBetween()
, but it will select the inverse.
whereIn(colName, [1, 2, 3])
Allows you to scope a query to return only rows where a column value is in an explicitly provided list of options:
$closeBy
=
DB
::
table
(
'contacts'
)
->
whereIn
(
'state'
,
[
'FL'
,
'GA'
,
'AL'
])
->
get
();
The same works for whereNotIn()
, but it will select the inverse.
whereNull(colName)
and whereNotNull(colName)
Allow you to select only rows where a given column is NULL
or is NOT NULL
, respectively.
whereRaw()
Allows you to pass in a raw, unescaped string to be added after the WHERE
statement:
$goofs = DB::table('contacts')->whereRaw('id = 12345')->get()
Any SQL queries passed to whereRaw()
will not be escaped. Use this method carefully and infrequently; this is a prime opportunity for SQL injection attacks in your app.
whereExists()
Allows you to select only rows that, when passed into a provided subquery, return at least one row. Imagine you only want to get those users who have left at least one comment:
$commenters
=
DB
::
table
(
'users'
)
->
whereExists
(
function
(
$query
)
{
$query
->
select
(
'id'
)
->
from
(
'comments'
)
->
whereRaw
(
'comments.user_id = users.id'
);
})
->
get
();
distinct()
Selects only rows where the selected data is unique when compared to the other rows in the returned data. Usually this is paired with select()
, because if you use a primary key, there will be no duplicated rows:
$lastNames = DB::table('contacts')->select('city')->distinct()->get();
These methods change the way the query’s results will be output, rather than just limiting its results:
orderBy(colName, direction)
Orders the results. The second parameter may be either asc
(the default, ascending order) or desc
(descending order):
$contacts
=
DB
::
table
(
'contacts'
)
->
orderBy
(
'last_name'
,
'asc'
)
->
get
();
groupBy()
and having()
or havingRaw()
Groups your results by a column. Optionally, having()
and havingRaw()
allow you to filter your results based on properties of the groups. For example, you could look for only cities with at least 30 people in them:
$populousCities
=
DB
::
table
(
'contacts'
)
->
groupBy
(
'city'
)
->
havingRaw
(
'count(contact_id) > 30'
)
->
get
();
skip()
and take()
Most often used for pagination, these allow you to define how many rows to return and how many to skip before starting the return—like a page number and a page size in a pagination system:
// returns rows 31-40
$page4
=
DB
::
table
(
'contacts'
)
->
skip
(
30
)
->
take
(
10
)
->
get
();
latest(colName)
and oldest(colName)
Sort by the passed column (or created_at
if no column name is passed) in descending (latest()
) or ascending (oldest()
) order.
inRandomOrder()
There are two methods, available in Laravel 5.2 and later, that allow you to conditionally apply their “contents” (a closure you pass to them) based on the Boolean state of a value you pass in:
when()
Given a truthy first parameter, applies the query modification contained in the closure; given a falsy first parameter, it does nothing. Note that the first parameter could be a Boolean (e.g., $ignoreDrafts
, set to true
or false
), an optional value ($status
, pulled from user input and defaulting to null
), or a closure that returns either; what matters is that it evaluates to truthy or falsy. For example:
$status
=
request
(
'status'
);
// Defaults to null if not set
$posts
=
DB
::
table
(
'posts'
)
->
when
(
$status
,
function
(
$query
)
use
(
$status
)
{
return
$query
->
where
(
'status'
,
$status
);
})
->
get
();
// Or
$posts
=
DB
::
table
(
'posts'
)
->
when
(
$ignoreDrafts
,
function
(
$query
)
{
return
$query
->
where
(
'draft'
,
false
);
})
->
get
();
You can also pass a third parameter, another closure, which will only be applied if the first parameter is falsy.
unless()
The exact inverse of when()
. If the first parameter is falsy, it will run the second closure.
These methods stop the query chain and trigger the execution of the SQL query. Without one of these at the end of the query chain, your return will always just be an instance of the query builder; chain one of these onto a query builder and you’ll actually get a result:
get()
Gets all results for the built query:
$contacts
=
DB
::
table
(
'contacts'
)
->
get
();
$vipContacts
=
DB
::
table
(
'contacts'
)
->
where
(
'vip'
,
true
)
->
get
();
first()
and firstOrFail()
Get only the first result—like get()
, but with a LIMIT 1
added:
$newestContact
=
DB
::
table
(
'contacts'
)
->
orderBy
(
'created_at'
,
'desc'
)
->
first
();
first()
fails silently if there are no results, whereas firstOrFail()
will throw an exception.
If you pass an array of column names to either method, it will return the data for just those columns instead of all columns.
find(id)
and findOrFail(id)
Like first()
, but you pass in an ID value that corresponds to the primary key to look up. find()
fails silently if a row with that ID doesn’t exist, while findOrFail()
will throw an exception:
$contactFive
=
DB
::
table
(
'contacts'
)
->
find
(
5
);
value()
Plucks just the value from a single field from the first row. Like first()
, but if you only want a single column:
$newestContactEmail
=
DB
::
table
(
'contacts'
)
->
orderBy
(
'created_at'
,
'desc'
)
->
value
(
'email'
);
count()
Returns an integer count of all of the matching results:
$countVips
=
DB
::
table
(
'contacts'
)
->
where
(
'vip'
,
true
)
->
count
();
min()
and max()
Return the minimum or maximum value of a particular column:
$highestCost
=
DB
::
table
(
'orders'
)
->
max
(
'amount'
);
sum()
and avg()
Return the sum or average of all of the values in a particular column:
$averageCost
=
DB
::
table
(
'orders'
)
->
where
(
'status'
,
'completed'
)
->
avg
(
'amount'
);
dd()
and dump()
Dump the underlying the underlying SQL query and the bindings, and, if using dd()
, ends the script.
DB
::
table
(
'users'
)
->
where
(
'name'
,
'Wilbur Powery'
)
->
dd
();
// "select * from "users" where "name" = ?"
// array:1 [ 0 => "Wilbur Powery"]
You’ve already seen a few custom methods for raw statements—for example, select()
has a selectRaw()
counterpart that allows you to pass in a string for the query builder to place after the WHERE
statement.
You can also, however, pass in the result of a DB::raw()
call to almost any method in the query builder to achieve the same result:
$contacts
=
DB
::
table
(
'contacts'
)
->
select
(
DB
::
raw
(
'*, (score * 100) AS integer_score'
))
->
get
();
Joins can sometimes be a pain to define, and there’s only so much a framework can do to make them simpler, but the query builder does its best. Let’s look at a sample:
$users
=
DB
::
table
(
'users'
)
->
join
(
'contacts'
,
'users.id'
,
'='
,
'contacts.user_id'
)
->
select
(
'users.*'
,
'contacts.name'
,
'contacts.status'
)
->
get
();
The join()
method creates an inner join. You can also chain together multiple joins one after another, or use leftJoin()
to get a left join.
Finally, you can create more complex joins by passing a closure into the join()
method:
DB
::
table
(
'users'
)
->
join
(
'contacts'
,
function
(
$join
)
{
$join
->
on
(
'users.id'
,
'='
,
'contacts.user_id'
)
->
orOn
(
'users.id'
,
'='
,
'contacts.proxy_user_id'
);
})
->
get
();
The insert()
method is pretty simple. Pass it an array to insert a single row or an array of arrays to insert multiple rows, and use insertGetId()
instead of insert()
to get the autoincrementing primary key ID back as a return:
$id
=
DB
::
table
(
'contacts'
)
->
insertGetId
([
'name'
=>
'Abe Thomas'
,
'email'
=>
'[email protected]'
,
]);
DB
::
table
(
'contacts'
)
->
insert
([
[
'name'
=>
'Tamika Johnson'
,
'email'
=>
'[email protected]'
],
[
'name'
=>
'Jim Patterson'
,
'email'
=>
'[email protected]'
],
]);
Updates are also simple. Create your update query and, instead of get()
or first()
, just use update()
and pass it an array of parameters:
DB
::
table
(
'contacts'
)
->
where
(
'points'
,
'>'
,
100
)
->
update
([
'status'
=>
'vip'
]);
You can also quickly increment and decrement columns using the increment()
and decrement()
methods. The first parameter of each is the column name, and the second (optional) is the number to increment/decrement by:
DB
::
table
(
'contacts'
)
->
increment
(
'tokens'
,
5
);
DB
::
table
(
'contacts'
)
->
decrement
(
'tokens'
);
If you have JSON columns, you can update or select rows based on aspects of the JSON structure by using the arrow syntax to traverse children:
// Select all records where the "isAdmin" property of the "options"
// JSON column is set to true
DB
::
table
(
'users'
)
->
where
(
'options->isAdmin'
,
true
)
->
get
();
// Update all records, setting the "verified" property
// of the "options" JSON column to true
DB
::
table
(
'users'
)
->
update
([
'options->isVerified'
,
true
]);
If you’re not familiar with database transactions, they’re a tool that allows you to wrap up a series of database queries to be performed in a batch, which you can choose to roll back, undoing the entire series of queries. Transactions are often used to ensure that all or none, but not some, of a series of related queries are performed—if one fails, the ORM will roll back the entire series of queries.
With the Laravel query builder’s transaction feature, if any exceptions are thrown at any point within the transaction closure, all the queries in the transaction will be rolled back. If the transaction closure finishes successfully, all the queries will be committed and not rolled back.
Let’s take a look at the sample transaction in Example 5-16.
DB
::
transaction
(
function
()
use
(
$userId
,
$numVotes
)
{
// Possibly failing DB query
DB
::
table
(
'users'
)
->
where
(
'id'
,
$userId
)
->
update
([
'votes'
=>
$numVotes
]);
// Caching query that we don't want to run if the above query fails
DB
::
table
(
'votes'
)
->
where
(
'user_id'
,
$userId
)
->
delete
();
});
In this example, we can assume we had some previous process that summarized the number of votes from the votes
table for a given user. We want to cache that number in the users
table and then wipe those votes from the votes
table. But, of course, we don’t want to wipe the votes until the update to the users
table has run successfully. And we don’t want to keep the updated number of votes in the users
table if the votes
table deletion fails.
If anything goes wrong with either query, the other won’t be applied. That’s the magic of database transactions.
Note that you can also manually begin and end transactions—and this applies both for query builder queries and for Eloquent queries. Start with DB::begin
Transaction()
, end with DB::commit()
, and abort with DB::rollBack()
:
DB
::
beginTransaction
();
// Take database actions
if
(
$badThingsHappened
)
{
DB
::
rollBack
();
}
// Take other database actions
DB
::
commit
();
Now that we’ve covered the query builder, let’s talk about Eloquent, Laravel’s flagship database tool that’s built on the query builder.
Eloquent is an ActiveRecord ORM, which means it’s a database abstraction layer that provides a single interface to interact with multiple database types. “ActiveRecord” means that a single Eloquent class is responsible for not only providing the ability to interact with the table as a whole (e.g., User::all()
gets all users), but also representing an individual table row (e.g., $sharon = new User
). Additionally, each instance is capable of managing its own persistence; you can call $sharon->save()
or $sharon
->
delete()
.
Eloquent has a primary focus on simplicity, and like the rest of the framework, it relies on “convention over configuration” to allow you to build powerful models with minimal code.
For example, you can perform all of the operations in Example 5-18 with the model defined in Example 5-17.
<?
php
use
IlluminateDatabaseEloquentModel
;
class
Contact
extends
Model
{}
// In a controller
public
function
save
(
Request
$request
)
{
// Create and save a new contact from user input
$contact
=
new
Contact
();
$contact
->
first_name
=
$request
->
input
(
'first_name'
);
$contact
->
last_name
=
$request
->
input
(
'last_name'
);
$contact
->
=
$request
->
input
(
'email'
);
$contact
->
save
();
return
redirect
(
'contacts'
);
}
public
function
show
(
$contactId
)
{
// Return a JSON representation of a contact based on a URL segment;
// if the contact doesn't exist, throw an exception
return
Contact
::
findOrFail
(
$contactId
);
}
public
function
vips
()
{
// Unnecessarily complex example, but still possible with basic Eloquent
// class; adds a "formalName" property to every VIP entry
return
Contact
::
where
(
'vip'
,
true
)
->
get
()
->
map
(
function
(
$contact
)
{
$contact
->
formalName
=
"The exalted
{
$contact
->
first_name
}
of the
{
$contact
->
last_name
}
s"
;
return
$contact
;
});
}
How? Convention. Eloquent assumes the table name (Contact
becomes contacts
), and with that you have a fully functional Eloquent model.
Let’s cover how we work with Eloquent models.
First, let’s create a model. There’s an Artisan command for that:
php artisan make:model Contact
This is what we’ll get, in app/Contact.php:
<?
php
namespace
App
;
use
IlluminateDatabaseEloquentModel
;
class
Contact
extends
Model
{
//
}
If you want to automatically create a migration when you create your model, pass the -m
or --migration
flag:
php
artisan
make
:
model
Contact
--
migration
The default behavior for table names is that Laravel “snake cases” and pluralizes your class name, so SecondaryContact
would access a table named secondary_contacts
. If you’d like to customize the name, set the $table
property explicitly on the model:
protected
$table
=
'contacts_secondary'
;
Laravel assumes, by default, that each table will have an autoincrementing integer primary key, and it will be named id
.
If you want to change the name of your primary key, change the $primaryKey
property:
protected
$primaryKey
=
'contact_id'
;
And if you want to set it to be nonincrementing, use:
public
$incrementing
=
false
;
Eloquent expects every table to have created_at
and updated_at
timestamp columns. If your table won’t have them, disable the $timestamps
functionality:
public
$timestamps
=
false
;
You can customize the format Eloquent uses to store your timestamps to the database by setting the $dateFormat
class property to a custom string. The string will be parsed using PHP’s date()
syntax, so the following example will store the date as seconds since the Unix epoch:
protected
$dateFormat
=
'U'
;
Most of the time you pull data from your database with Eloquent, you’ll use static calls on your Eloquent model.
Let’s start by getting everything:
$allContacts
=
Contact
::
all
();
That was easy. Let’s filter it a bit:
$vipContacts
=
Contact
::
where
(
'vip'
,
true
)
->
get
();
We can see that the Eloquent
facade gives us the ability to chain constraints, and from there the constraints get very familiar:
$newestContacts
=
Contact
::
orderBy
(
'created_at'
,
'desc'
)
->
take
(
10
)
->
get
();
It turns out that once you move past the initial facade name, you’re just working with Laravel’s query builder. You can do a lot more—we’ll cover that soon—but everything you can do with the query builder on the DB
facade you can do on your Eloquent objects.
Like we covered earlier in the chapter, you can use first()
to return only the first record from a query, or find()
to pull just the record with the provided ID. For either, if you append “OrFail” to the method name, it will throw an exception if there are no matching results. This makes findOrFail()
a common tool for looking up an entity by a URL segment (or throwing an exception if a matching entity doesn’t exist), like you can see in Example 5-19.
// ContactController
public
function
show
(
$contactId
)
{
return
view
(
'contacts.show'
)
->
with
(
'contact'
,
Contact
::
findOrFail
(
$contactId
));
}
Any method intended to return a single record (first()
, firstOrFail()
, find()
, or findOrFail()
) will return an instance of the Eloquent class. So, Contact::first()
will return an instance of the class Contact
with the data from the first row in the table filling it out.
As you can see in Example 5-19, we don’t need to catch Eloquent’s model not found exception (IlluminateDatabaseEloquent
ModelNotFoundException)
in our controllers; Laravel’s routing system will catch it and throw a 404 for us.
You could, of course, catch that particular exception and handle it, if you’d like.
get()
works with Eloquent just like it does in normal query builder calls—build a query and call get()
at the end to get the results:
$vipContacts
=
Contact
::
where
(
'vip'
,
true
)
->
get
();
However, there is an Eloquent-only method, all()
, which you’ll often see people use when they want to get an unfiltered list of all data in the table:
$contacts
=
Contact
::
all
();
Any time you can use all()
, you could use get()
. Contact::get()
has the same response as Contact::all()
. However, the moment you start modifying your query—adding a where()
filter, for example—all()
will no longer work, but get()
will continue working.
So, even though all()
is very common, I’d recommend using get()
for everything, and ignoring the fact that all()
even exists.
The other thing that’s different about Eloquent’s get()
method (versus all()
) is that, prior to Laravel 5.3, it returned an array of models instead of a collection. In 5.3 and later, they both return collections.
If you’ve ever needed to process a large amount (thousands or more) of records at a time, you may have run into memory or locking issues. Laravel makes it possible to break your requests into smaller pieces (chunks) and process them in batches, keeping the memory load of your large request smaller. Example 5-20 illustrates the use of chunk()
to split a query into “chunks” of 100 records each.
Contact
::
chunk
(
100
,
function
(
$contacts
)
{
foreach
(
$contacts
as
$contact
)
{
// Do something with $contact
}
});
Inserting and updating values is one of the places where Eloquent starts to diverge from normal query builder syntax.
There are two primary ways to insert a new record using Eloquent.
First, you can create a new instance of your Eloquent class, set your properties manually, and call save()
on that instance, like in Example 5-21.
$contact
=
new
Contact
;
$contact
->
name
=
'Ken Hirata'
;
$contact
->
=
'[email protected]'
;
$contact
->
save
();
// or
$contact
=
new
Contact
([
'name'
=>
'Ken Hirata'
,
'email'
=>
'[email protected]'
,
]);
$contact
->
save
();
// or
$contact
=
Contact
::
make
([
'name'
=>
'Ken Hirata'
,
'email'
=>
'[email protected]'
,
]);
$contact
->
save
();
Until you save()
, this instance of Contact
represents the contact fully—except it has never been saved to the database. That means it doesn’t have an id
, if the application quits it won’t persist, and it doesn’t have its created_at
and updated_at
values set.
You can also pass an array to Model::create()
, as shown in Example 5-22. Unlike make()
, create()
saves the instance to the database as soon as it’s called.
$contact
=
Contact
::
create
([
'name'
=>
'Keahi Hale'
,
'email'
=>
'[email protected]'
,
]);
Also be aware that in any context where you are passing an array (to new Model()
, Model::make()
, Model::create()
, or Model::update()
), every property you set via Model::create()
has to be approved for “mass assignment,” which we’ll cover shortly. This is not necessary with the first example in Example 5-21, where you assign each property individually.
Note that if you’re using Model::create()
, you don’t need to save()
the instance—that’s handled as a part of the model’s create()
method.
Updating records looks very similar to inserting. You can get a specific instance, change its properties, and then save, or you can make a single call and pass an array of updated properties. Example 5-23 illustrates the first approach.
$contact
=
Contact
::
find
(
1
);
$contact
->
=
'[email protected]'
;
$contact
->
save
();
Since this record already exists, it will already have a created_at
timestamp and an id
, which will stay the same, but the updated_at
field will be changed to the current date and time. Example 5-24 illustrates the second approach.
Contact
::
where
(
'created_at'
,
'<'
,
now
()
->
subYear
())
->
update
([
'longevity'
=>
'ancient'
]);
// or
$contact
=
Contact
::
find
(
1
);
$contact
->
update
([
'longevity'
=>
'ancient'
]);
This method expects an array where each key is the column name and each value is the column value.
We’ve looked at a few examples of how to pass arrays of values into Eloquent class methods. However, none of these will actually work until you define which fields are “fillable” on the model.
The goal of this is to protect you from (possibly malicious) user input accidentally setting new values on fields you don’t want changed. Consider the common scenario in Example 5-25.
// ContactController
public
function
update
(
Contact
$contact
,
Request
$request
)
{
$contact
->
update
(
$request
->
all
());
}
If you’re not familiar with the Illuminate Request
object, Example 5-25 will take every piece of user input and pass it to the update()
method. That all()
method includes things like URL parameters and form inputs, so a malicious user could easily add some things in there, like id
and owner_id
, that you likely don’t want updated.
Thankfully, that won’t actually work until you define your model’s fillable fields. You can either whitelist the fillable fields, or blacklist the “guarded” fields to determine which fields can or cannot be edited via “mass assignment”—that is, by passing an array of values into either create()
or update()
. Note that nonfillable properties can still be changed by direct assignment (e.g., $contact->password = 'abc';
). Example 5-26 shows both approaches.
class
Contact
{
protected
$fillable
=
[
'name'
,
'email'
];
// or
protected
$guarded
=
[
'id'
,
'created_at'
,
'updated_at'
,
'owner_id'
];
}
In Example 5-25, we needed Eloquent’s mass assignment guard because we were using the all()
method on the Request
object to pass in the entirety of the user input.
Eloquent’s mass assignment protection is a great tool here, but there’s also a helpful trick to keep you from accepting any old input from the user.
The Request
class has an only()
method that allows you to pluck only a few keys from the user input. So now you can do this:
Contact
::
create
(
$request
->
only
(
'name'
,
'email'
));
Sometimes you want to tell your application, “Get me an instance with these properties, or if it doesn’t exist, create it.” This is where the firstOr*()
methods come in.
The firstOrCreate()
and firstOrNew()
methods take an array of keys and values as their first parameter:
$contact
=
Contact
::
firstOrCreate
([
'email'
=>
'[email protected]'
]);
They’ll both look for and retrieve the first record matching those parameters, and if there are no matching records, they’ll create an instance with those properties; firstOrCreate()
will persist that instance to the database and then return it, while firstOrNew()
will return it without saving it.
If you pass an array of values as the second parameter, those values will be added to the created entry (if it’s created) but won’t be used to look up whether the entry exists.
Deleting with Eloquent is very similar to updating with Eloquent, but with (optional) soft deletes, you can archive your deleted items for later inspection or even recovery.
The simplest way to delete a model record is to call the delete()
method on the instance itself:
$contact
=
Contact
::
find
(
5
);
$contact
->
delete
();
However, if you only have the ID, there’s no reason to look up an instance just to delete it; you can pass an ID or an array of IDs to the model’s destroy()
method to delete them directly:
Contact
::
destroy
(
1
);
// or
Contact
::
destroy
([
1
,
5
,
7
]);
Finally, you can delete all of the results of a query:
Contact
::
where
(
'updated_at'
,
'<'
,
now
()
->
subYear
())
->
delete
();
Soft deletes mark database rows as deleted without actually deleting them from the database. This gives you the ability to inspect them later, to have records that show more than “no information, deleted” when displaying historic information, and to allow your users (or admins) to restore some or all data.
The hard part about handcoding an application with soft deletes is that every query you ever write will need to exclude the soft-deleted data. Thankfully, if you use Eloquent’s soft deletes, every query you ever make will be scoped to ignore soft deletes by default, unless you explicitly ask to bring them back.
Eloquent’s soft delete functionality requires a deleted_at
column to be added to the table. Once you enable soft deletes on that Eloquent model, every query you ever write (unless you explicitly include soft-deleted records) will be scoped to ignore soft-deleted rows.
You enable soft deletes by doing three things: adding the deleted_at
column in a migration, importing the SoftDeletes
trait in the model, and adding the deleted_at
column to your $dates
property. There’s a softDeletes()
method available on the schema builder to add the deleted_at
column to a table, as you can see in Example 5-27. And Example 5-28 shows an Eloquent model with soft deletes enabled.
Schema
::
table
(
'contacts'
,
function
(
Blueprint
$table
)
{
$table
->
softDeletes
();
});
<?
php
use
IlluminateDatabaseEloquentModel
;
use
IlluminateDatabaseEloquentSoftDeletes
;
class
Contact
extends
Model
{
use
SoftDeletes
;
// use the trait
protected
$dates
=
[
'deleted_at'
];
// mark this column as a date
}
Once you make these changes, every delete()
and destroy()
call will now set the deleted_at
column on your row to be the current date and time instead of deleting that row. And all future queries will exclude that row as a result.
So, how do we get soft-deleted items?
First, you can add soft-deleted items to a query:
$allHistoricContacts
=
Contact
::
withTrashed
()
->
get
();
Next, you can use the trashed()
method to see if a particular instance has been soft-deleted:
if
(
$contact
->
trashed
())
{
// do something
}
Finally, you can get only soft-deleted items:
$deletedContacts
=
Contact
::
onlyTrashed
()
->
get
();
We’ve covered “filtered” queries, meaning any query where we’re not just returning every result for a table. But every time we’ve written them so far in this chapter, it’s been a manual process using the query builder.
Local and global scopes in Eloquent allow you to define prebuilt “scopes” (filters) that you can use either every time a model is queried (“global”) or every time you query it with a particular method chain (“local”).
Local scopes are the simplest to understand. Let’s take this example:
$activeVips
=
Contact
::
where
(
'vip'
,
true
)
->
where
(
'trial'
,
false
)
->
get
();
First of all, if we write this combination of query methods over and over, it will get tedious. But additionally, the knowledge of how to define someone being an “active VIP” is now spread around our application. We want to centralize that knowledge. What if we could just write this?
$activeVips
=
Contact
::
activeVips
()
->
get
();
We can—it’s called a local scope. And it’s easy to define on the Contact
class, as you can see in Example 5-29.
class
Contact
{
public
function
scopeActiveVips
(
$query
)
{
return
$query
->
where
(
'vip'
,
true
)
->
where
(
'trial'
,
false
);
}
To define a local scope, we add a method to the Eloquent class that begins with “scope” and then contains the title-cased version of the scope name. This method is passed a query builder and needs to return a query builder, but of course you can modify the query before returning—that’s the whole point.
You can also define scopes that accept parameters, as shown in Example 5-30.
class
Contact
{
public
function
scopeStatus
(
$query
,
$status
)
{
return
$query
->
where
(
'status'
,
$status
);
}
And you use them in the same way, just passing the parameter to the scope:
$friends
=
Contact
::
status
(
'friend'
)
->
get
();
In Laravel 5.8+, you can also chain orWhere()
between two local scopes.
$activeOrVips
=
Contact
::
active
()
->
orWhere
()
->
vip
()
->
get
();
Remember how we talked about soft deletes only working if you scope every query on the model to ignore the soft-deleted items? That’s a global scope. And we can define our own global scopes, which will be applied on every query made from a given model.
There are two ways to define a global scope: using a closure or using an entire class. In each, you’ll register the defined scope in the model’s boot()
method. Let’s start with the closure method, illustrated in Example 5-31.
...
class
Contact
extends
Model
{
protected
static
function
boot
()
{
parent
::
boot
();
static
::
addGlobalScope
(
'active'
,
function
(
Builder
$builder
)
{
$builder
->
where
(
'active'
,
true
);
});
}
That’s it. We just added a global scope named active
, and now every query on this model will be scoped to only rows with active
set to true
.
Next, let’s try the longer way, as shown in Example 5-32. Create a class that implements IlluminateDatabaseEloquentScope
, which means it will have an apply()
method that takes an instance of a query builder and an instance of the model.
<?
php
namespace
AppScopes
;
use
IlluminateDatabaseEloquentScope
;
use
IlluminateDatabaseEloquentModel
;
use
IlluminateDatabaseEloquentBuilder
;
class
ActiveScope
implements
Scope
{
public
function
apply
(
Builder
$builder
,
Model
$model
)
{
return
$builder
->
where
(
'active'
,
true
);
}
}
To apply this scope to a model, once again override the parent’s boot()
method and call addGlobalScope()
on the class using static
, as shown in Example 5-33.
<?
php
use
AppScopesActiveScope
;
use
IlluminateDatabaseEloquentModel
;
class
Contact
extends
Model
{
protected
static
function
boot
()
{
parent
::
boot
();
static
::
addGlobalScope
(
new
ActiveScope
);
}
}
You may have noticed that several of these examples have used the class Contact
, with no namespace. This is abnormal, and I’ve only done this to save space in the book. Normally even your top-level models would live at something like AppContact
.
There are three ways to remove a global scope, and all three use the withoutGlobalScope()
or withoutGlobalScopes()
methods. If you’re removing a closure-based scope, the first parameter of that scope’s addGlobalScope()
registration will be the key you used to enable it:
$allContacts
=
Contact
::
withoutGlobalScope
(
'active'
)
->
get
();
If you’re removing a single class-based global scope, you can pass the class name to withoutGlobalScope()
or withoutGlobalScopes()
:
Contact
::
withoutGlobalScope
(
ActiveScope
::
class
)
->
get
();
Contact
::
withoutGlobalScopes
([
ActiveScope
::
class
,
VipScope
::
class
])
->
get
();
Or, you can just disable all global scopes for a query:
Contact
::
withoutGlobalScopes
()
->
get
();
Now that we’ve covered how to get records into and out of the database with Eloquent, let’s talk about decorating and manipulating the individual attributes on your Eloquent models.
Accessors, mutators, and attribute casting all allow you to customize the way individual attributes of Eloquent instances are input or output. Without using any of these, each attribute of your Eloquent instance is treated like a string, and you can’t have any attributes on your models that don’t exist on the database. But we can change that.
Accessors allow you to define custom attributes on your Eloquent models for when you are reading data from the model instance. This may be because you want to change how a particular column is output, or because you want to create a custom attribute that doesn’t exist in the database table at all.
You define an accessor by writing a method on your model with the following structure: get{PascalCasedPropertyName}Attribute
. So, if your property name is first_name
, the accessor method would be named getFirstNameAttribute
.
Let’s try it out. First, we’ll decorate a preexisting column (Example 5-34).
// Model definition:
class
Contact
extends
Model
{
public
function
getNameAttribute
(
$value
)
{
return
$value
?:
'(No name provided)'
;
}
}
// Accessor usage:
$name
=
$contact
->
name
;
But we can also use accessors to define attributes that never existed in the database, as seen in Example 5-35.
// Model definition:
class
Contact
extends
Model
{
public
function
getFullNameAttribute
()
{
return
$this
->
first_name
.
' '
.
$this
->
last_name
;
}
}
// Accessor usage:
$fullName
=
$contact
->
full_name
;
Mutators work the same way as accessors, except they’re for determining how to process setting the data instead of getting it. Just like with accessors, you can use them to modify the process of writing data to existing columns, or to allow for setting columns that don’t exist in the database.
You define a mutator by writing a method on your model with the following structure: set{PascalCasedPropertyName}Attribute
. So, if your property name is first_name
, the mutator method would be named setFirstNameAttribute
.
Let’s try it out. First, we’ll add a constraint to updating a preexisting column (Example 5-36).
// Defining the mutator
class
Order
extends
Model
{
public
function
setAmountAttribute
(
$value
)
{
$this
->
attributes
[
'amount'
]
=
$value
>
0
?
$value
:
0
;
}
}
// Using the mutator
$order
->
amount
=
'15'
;
This reveals that the way mutators are expected to “set” data on the model is by setting it in $this->attributes
with the column name as the key.
Now let’s add a proxy column for setting, as shown in Example 5-37.
// Defining the mutator
class
Order
extends
Model
{
public
function
setWorkgroupNameAttribute
(
$workgroupName
)
{
$this
->
attributes
[
'email'
]
=
"
{
$workgroupName
}
@ourcompany.com"
;
}
}
// Using the mutator
$order
->
workgroup_name
=
'jstott'
;
As you can probably guess, it’s relatively uncommon to create a mutator for a non-existent column, because it can be confusing to set one property and have it change a different column—but it is possible.
You can probably imagine writing accessors to cast all of your integer-type fields as integers, encode and decode JSON to store in a TEXT
column, or convert TINYINT
0
and 1
to and from Boolean values.
Thankfully, there’s a system for that in Eloquent already. It’s called attribute casting, and it allows you to define that any of your columns should always be treated, both on read and on write, as if they are of a particular data type. The options are listed in Table 5-1.
Type | Description |
---|---|
|
Casts with PHP ( |
|
Casts with PHP ( |
|
Casts with PHP ( |
|
Casts with PHP ( |
|
Parses to/from JSON, as a |
|
Parses to/from JSON, as an array |
|
Parses to/from JSON, as a collection |
|
Parses from database |
|
Parses from database |
Example 5-38 shows how you use attribute casting in your model.
class
Contact
{
protected
$casts
=
[
'vip'
=>
'boolean'
,
'children_names'
=>
'array'
,
'birthday'
=>
'date'
,
];
}
You can choose for particular columns to be mutated as timestamp
columns by adding them to the dates
array, as seen in Example 5-39.
class
Contact
{
protected
$dates
=
[
'met_at'
,
];
}
By default, this array contains created_at
and updated_at
, so adding entries to dates
just adds them to the list.
However, there’s no difference between adding columns to this list and adding them to $this->casts
as timestamp
, so this is becoming a bit of an unnecessary feature now that attribute casting can cast timestamps (new since Laravel 5.2).
When you make any query call in Eloquent that has the potential to return multiple rows, instead of an array they’ll come packaged in an Eloquent collection, which is a specialized type of collection. Let’s take a look at collections and Eloquent collections, and what makes them better than plain arrays.
Laravel’s Collection
objects (IlluminateSupportCollection
) are a little bit like arrays on steroids. The methods they expose on array-like objects are so helpful that, once you’ve been using them for a while, you’ll likely want to pull them into non-Laravel projects—which you can, with the Tightenco/Collect package.
The simplest way to create a collection is to use the collect()
helper. Either pass an array in, or use it without arguments to create an empty collection and then push items into it later. Let’s try it:
$collection
=
collect
([
1
,
2
,
3
]);
Now let’s say we want to filter out any even numbers:
$odds
=
$collection
->
reject
(
function
(
$item
)
{
return
$item
%
2
===
0
;
});
Or what if we want to get a version of the collection where each item is multiplied by 10? We can do that as follows:
$multiplied
=
$collection
->
map
(
function
(
$item
)
{
return
$item
*
10
;
});
We can even get only the even numbers, multiply them all by 10, and reduce them to a single number by sum()
:
$sum
=
$collection
->
filter
(
function
(
$item
)
{
return
$item
%
2
==
0
;
})
->
map
(
function
(
$item
)
{
return
$item
*
10
;
})
->
sum
();
As you can see, collections provide a series of methods, which can optionally be chained, to perform functional operations on your arrays. They provide the same functionality as native PHP methods like array_map()
and array_reduce()
, but you don’t have to memorize PHP’s unpredictable parameter order, and the method chaining syntax is infinitely more readable.
There are more than 60 methods available on the Collection
class, including methods like max()
, whereIn()
, flatten()
, and flip()
, and there’s not enough space to cover them all here. We’ll talk about more in Chapter 17, or you can check out the Laravel collections docs to see all of the methods.
Each Eloquent collection is a normal collection, but extended for the particular needs of a collection of Eloquent results.
Once again, there’s not enough room here to cover all of the additions, but they’re centered around the unique aspects of interacting with a collection not just of generic objects, but objects meant to represent database rows.
For example, every Eloquent collection has a method called modelKeys()
that returns an array of the primary keys of every instance in the collection. find($id)
looks for an instance that has the primary key of $id
.
One additional feature available here is the ability to define that any given model should return its results wrapped in a specific class of collection. So, if you want to add specific methods to any collection of objects of your Order
model—possibly related to summarizing the financial details of your orders—you could create a custom OrderCollection
that extends IlluminateDatabaseEloquentCollection
, and then register it in your model, as shown in Example 5-40.
...
class
OrderCollection
extends
Collection
{
public
function
sumBillableAmount
()
{
return
$this
->
reduce
(
function
(
$carry
,
$order
)
{
return
$carry
+
(
$order
->
billable
?
$order
->
amount
:
0
);
},
0
);
}
}
...
class
Order
extends
Model
{
public
function
newCollection
(
array
$models
=
[])
{
return
new
OrderCollection
(
$models
);
}
Now, any time you get back a collection of Order
s (e.g., from Order::all()
), it’ll actually be an instance of the OrderCollection
class:
$orders
=
Order
::
all
();
$billableAmount
=
$orders
->
sumBillableAmount
();
Serialization is what happens when you take something complex—an array, or an object—and convert it to a string. In a web-based context, that string is often JSON, but it could take other forms as well.
Serializing complex database records can be, well, complex, and this is one of the places many ORMs fall short. Thankfully, you get two powerful methods for free with Eloquent: toArray()
and toJson()
. Collections also have toArray()
and toJson()
, so all of these are valid:
$contactArray
=
Contact
::
first
()
->
toArray
();
$contactJson
=
Contact
::
first
()
->
toJson
();
$contactsArray
=
Contact
::
all
()
->
toArray
();
$contactsJson
=
Contact
::
all
()
->
toJson
();
You can also cast an Eloquent instance or collection to a string ($string = (string) $contact;
), but both models and collections will just run toJson()
and return the result.
Laravel’s router eventually converts everything routes return to a string, so there’s a clever trick you can use. If you return the result of an Eloquent call in a controller, it will be automatically cast to a string, and therefore returned as JSON. That means a JSON-returning route can be as simple as either of the ones in Example 5-41.
// routes/web.php
Route
::
get
(
'api/contacts'
,
function
()
{
return
Contact
::
all
();
});
Route
::
get
(
'api/contacts/{id}'
,
function
(
$id
)
{
return
Contact
::
findOrFail
(
$id
);
});
It’s very common to use JSON returns in APIs, and it’s very common to want to hide certain attributes in these contexts, so Eloquent makes it easy to hide any attributes every time you cast to JSON.
You can either blacklist attributes, hiding the ones you list:
class
Contact
extends
Model
{
public
$hidden
=
[
'password'
,
'remember_token'
];
or whitelist attributes, showing only the ones you list:
class
Contact
extends
Model
{
public
$visible
=
[
'name'
,
'email'
,
'status'
];
This also works for relationships:
class
User
extends
Model
{
public
$hidden
=
[
'contacts'
];
public
function
contacts
()
{
return
$this
->
hasMany
(
Contact
::
class
);
}
By default, the contents of a relationship are not loaded when you get a database record, so it doesn’t matter whether you hide them or not. But, as you’ll learn shortly, it’s possible to get a record with its related items, and in this context, those items will not be included in a serialized copy of that record if you choose to hide that relationship.
In case you’re curious now, you can get a User
with all contacts—assuming you’ve set up the relationship correctly—with the following call:
$user
=
User
::
with
(
'contacts'
)
->
first
();
There might be times when you want to make an attribute visible just for a single call. That’s possible, with the Eloquent method makeVisible()
:
$array
=
$user
->
makeVisible
(
'remember_token'
)
->
toArray
();
If you have created an accessor for a column that doesn’t exist—for example, our full_name
column from Example 5-35—add it to the $appends
array on the model to add it to the array and JSON output:
class
Contact
extends
Model
{
protected
$appends
=
[
'full_name'
];
public
function
getFullNameAttribute
()
{
return
"
{
$this
->
first_name
}
{
$this
->
last_name
}
"
;
}
}
In a relational database model, it’s expected that you will have tables that are related to each other—hence the name. Eloquent provides simple and powerful tools to make the process of relating your database tables easier than ever before.
Many of our examples in this chapter have been centered around a user who has many contacts, a relatively common situation.
In an ORM like Eloquent, you would call this a one-to-many relationship: the one user has many contacts.
If it was a CRM where a contact could be assigned to many users, then this would be a many-to-many relationship: many users can be related to one contact, and each user can be related to many contacts. A user has and belongs to many contacts.
If each contact can have many phone numbers, and a user wanted a database of every phone number for their CRM, you would say the user has many phone numbers through contacts—that is, a user has many contacts, and the contact has many phone numbers, so the contact is sort of an intermediary.
And what if each contact has an address, but you’re only interested in tracking one address? You could have all the address fields on the Contact
, but you might also create an Address
model—meaning the contact has one address.
Finally, what if you want to be able to star (favorite) contacts, but also events? This would be a polymorphic relationship, where a user has many stars, but some may be contacts and some may be events.
So, let’s dig into how to define and access these relationships.
Let’s start simple: a Contact
has one PhoneNumber
. This relationship is defined in Example 5-42.
class
Contact
extends
Model
{
public
function
phoneNumber
()
{
return
$this
->
hasOne
(
PhoneNumber
::
class
);
}
As you can tell, the methods defining relationships are on the Eloquent model itself ($this->hasOne()
) and take, at least in this instance, the fully qualified class name of the class that you’re relating them to.
How should this be defined in your database? Since we’ve defined that the Contact
has one PhoneNumber
, Eloquent expects that the table supporting the PhoneNumber
class (likely phone_numbers
) has a contact_id
column on it. If you named it something different (for instance, owner_id
), you’ll need to change your definition:
return
$this
->
hasOne
(
PhoneNumber
::
class
,
'owner_id'
);
Here’s how we access the PhoneNumber
of a Contact
:
$contact
=
Contact
::
first
();
$contactPhone
=
$contact
->
phoneNumber
;
Notice that we define the method in Example 5-42 with phoneNumber()
, but we access it with ->phoneNumber
. That’s the magic. You could also access it with ->phone_number
. This will return a full Eloquent instance of the related PhoneNumber
record.
But what if we want to access the Contact
from the PhoneNumber
? There’s a method for that, too (see Example 5-43).
class
PhoneNumber
extends
Model
{
public
function
contact
()
{
return
$this
->
belongsTo
(
Contact
::
class
);
}
Then we access it the same way:
$contact
=
$phoneNumber
->
contact
;
Each relationship type has its own quirks for how to relate models, but here’s the core of how it works: pass an instance to save()
, or an array of instances to saveMany()
. You can also pass properties to create()
or createMany()
and they’ll make new instances for you:
$contact
=
Contact
::
first
();
$phoneNumber
=
new
PhoneNumber
;
$phoneNumber
->
number
=
8008675309
;
$contact
->
phoneNumbers
()
->
save
(
$phoneNumber
);
// or
$contact
->
phoneNumbers
()
->
saveMany
([
PhoneNumber
::
find
(
1
),
PhoneNumber
::
find
(
2
),
]);
// or
$contact
->
phoneNumbers
()
->
create
([
'number'
=>
'+13138675309'
,
]);
// or
$contact
->
phoneNumbers
()
->
createMany
([
[
'number'
=>
'+13138675309'
],
[
'number'
=>
'+15556060842'
],
]);
The createMany()
method is only available in Laravel 5.4 and later.
The one-to-many relationship is by far the most common. Let’s take a look at how to define that our User
has many Contact
s (Example 5-44).
class
User
extends
Model
{
public
function
contacts
()
{
return
$this
->
hasMany
(
Contact
::
class
);
}
Once again, this expects that the Contact
model’s backing table (likely contacts
) has a user_id
column on it. If it doesn’t, override it by passing the correct column name as the second parameter of hasMany()
.
We can get a User
’s Contact
s as follows:
$user
=
User
::
first
();
$usersContacts
=
$user
->
contacts
;
Just like with one to one, we use the name of the relationship method and call it as if it were a property instead of a method. However, this method returns a collection instead of a model instance. And this is a normal Eloquent collection, so we can have all sorts of fun with it:
$donors
=
$user
->
contacts
->
filter
(
function
(
$contact
)
{
return
$contact
->
status
==
'donor'
;
});
$lifetimeValue
=
$contact
->
orders
->
reduce
(
function
(
$carry
,
$order
)
{
return
$carry
+
$order
->
amount
;
},
0
);
Just like with one to one, we can also define the inverse (Example 5-45).
class
Contact
extends
Model
{
public
function
user
()
{
return
$this
->
belongsTo
(
User
::
class
);
}
And just like with one to one, we can access the User
from the Contact
:
$userName
=
$contact
->
user
->
name
;
Most of the time we attach an item by running save()
on the parent and passing in the related item, as in $user
->
contacts()
->
save($contact)
. But if you want to perform these behaviors on the attached (“child”) item, you can use associate()
and dissociate()
on the method that returns the belongsTo
relationship:
$contact
=
Contact
::
first
();
$contact
->
user
()
->
associate
(
User
::
first
());
$contact
->
save
();
// and later
$contact
->
user
()
->
dissociate
();
$contact
->
save
();
Until now, we’ve taken the method name (e.g., contacts())
and called it as if were a property (e.g., $user->contacts
). What happens if we call it as a method? Instead of processing the relationship, it will return a pre-scoped query builder.
So if you have User 1
, and you call its contacts()
method, you will now have a query builder prescoped to “all contacts that have a field user_id
with the value of 1
.” You can then build out a functional query from there:
$donors
=
$user
->
contacts
()
->
where
(
'status'
,
'donor'
)
->
get
();
You can choose to select only records that meet particular criteria with regard to their related items using has()
:
$postsWithComments
=
Post
::
has
(
'comments'
)
->
get
();
You can also adjust the criteria further:
$postsWithManyComments
=
Post
::
has
(
'comments'
,
'>='
,
5
)
->
get
();
You can nest the criteria:
$usersWithPhoneBooks
=
User
::
has
(
'contacts.phoneNumbers'
)
->
get
();
And finally, you can write custom queries on the related items:
// Gets all contacts with a phone number containing the string "867-5309"
$jennyIGotYourNumber
=
Contact
::
whereHas
(
'phoneNumbers'
,
function
(
$query
)
{
$query
->
where
(
'number'
,
'like'
,
'%867-5309%'
);
});
hasManyThrough()
is really a convenience method for pulling in relationships of a relationship. Think of the example I gave earlier, where a User
has many Contact
s and each Contact
has many PhoneNumber
s. What if you want to get a user’s list of contact phone numbers? That’s has-many-through relation.
This structure assumes that your contacts
table has a user_id
to relate the contacts to the users and the phone_numbers
table has a contact_id
to relate it to the contacts. Then, we define the relationship on the User
as in Example 5-46.
class
User
extends
Model
{
public
function
phoneNumbers
()
{
return
$this
->
hasManyThrough
(
PhoneNumber
::
class
,
Contact
::
class
);
}
You’d access this relationship using $user->phone_numbers
, and as always you can customize the relationship key on the intermediate model (with the third parameter of hasManyThrough()
) and the relationship key on the distant model (with the fourth parameter).
hasOneThrough()
is just like hasManyThrough()
, but instead of accessing many related items through intermediate items, you’re only accessing a single related item through a single intermediate item.
What if each user belonged to a company, and that company had a single phone number, and you wanted to be able to get a user’s phone number by pulling their company’s phone number? That’s hasOneThrough()
.
class
User
extends
Model
{
public
function
phoneNumber
()
{
return
$this
->
hasOneThrough
(
PhoneNumber
::
class
,
Company
::
class
);
}
This is where things start to get complex. Let’s take our example of a CRM that allows a User
to have many Contact
s, and each Contact
to be related to multiple User
s.
First, we define the relationship on the User
as in Example 5-48.
class
User
extends
Model
{
public
function
contacts
()
{
return
$this
->
belongsToMany
(
Contact
::
class
);
}
}
And since this is many to many, the inverse looks exactly the same (Example 5-49).
class
Contact
extends
Model
{
public
function
users
()
{
return
$this
->
belongsToMany
(
User
::
class
);
}
}
Since a single Contact
can’t have a user_id
column and a single User
can’t have a contact_id
column, many-to-many relationships rely on a pivot table that connects the two. The conventional naming of this table is done by placing the two singular table names together, ordered alphabetically, and separating them by an underscore.
So, since we’re linking users
and contacts
, our pivot table should be named contact_user
(if you’d like to customize the table name, pass it as the second parameter to the belongsToMany()
method). It needs two columns: contact_id
and user_id
.
Just like with hasMany()
, we get access to a collection of the related items, but this time it’s from both sides (Example 5-50).
$user
=
User
::
first
();
$user
->
contacts
->
each
(
function
(
$contact
)
{
// do something
});
$contact
=
Contact
::
first
();
$contact
->
users
->
each
(
function
(
$user
)
{
// do something
});
$donors
=
$user
->
contacts
()
->
where
(
'status'
,
'donor'
)
->
get
();
One thing that’s unique about many to many is that it’s our first relationship that has a pivot table. The less data you have in a pivot table, the better, but there are some cases where it’s valuable to store information in your pivot table—for example, you might want to store a created_at
field to see when this relationship was created.
In order to store these fields, you have to add them to the relationship definition, like in Example 5-51. You can define specific fields using withPivot()
or add created_at
and updated_at
timestamps using withTimestamps()
.
public
function
contacts
()
{
return
$this
->
belongsToMany
(
Contact
::
class
)
->
withTimestamps
()
->
withPivot
(
'status'
,
'preferred_greeting'
);
}
When you get a model instance through a relationship, it will have a pivot
property on it, which will represent its place in the pivot table you just pulled it from. So, you can do something like Example 5-52.
$user
=
User
::
first
();
$user
->
contacts
->
each
(
function
(
$contact
)
{
echo
sprintf
(
'Contact associated with this user at: %s'
,
$contact
->
pivot
->
created_at
);
});
If you’d like, you can customize the pivot
key to have a different name using the as()
method, as shown in Example 5-53.
// User model
public
function
groups
()
{
return
$this
->
belongsToMany
(
Group
::
class
)
->
withTimestamps
()
->
as
(
'membership'
);
}
// Using this relationship:
User
::
first
()
->
groups
->
each
(
function
(
$group
)
{
echo
sprintf
(
'User joined this group at: %s'
,
$group
->
membership
->
created_at
);
});
Remember, our polymorphic relationship is where we have multiple Eloquent classes corresponding to the same relationship. We’re going to use Star
s (like favorites) right now. A user can star both Contact
s and Event
s, and that’s where the name polymorphic comes from: there’s a single interface to objects of multiple types.
So, we’ll need three tables, and three models: Star
, Contact
, and Event
(four of each, technically, because we’ll need users
and User
, but we’ll get there in a second). The contacts
and events
tables will just be as they normally are, and the stars
table will contain id
, starrable_id
, and starrable_type
fields. For each Star
, we’ll be defining which “type” (e.g., Contact
or Event
) and which ID of that type (e.g., 1
) it is.
Let’s create our models, as seen in Example 5-54.
class
Star
extends
Model
{
public
function
starrable
()
{
return
$this
->
morphTo
();
}
}
class
Contact
extends
Model
{
public
function
stars
()
{
return
$this
->
morphMany
(
Star
::
class
,
'starrable'
);
}
}
class
Event
extends
Model
{
public
function
stars
()
{
return
$this
->
morphMany
(
Star
::
class
,
'starrable'
);
}
}
So, how do we create a Star
?
$contact
=
Contact
::
first
();
$contact
->
stars
()
->
create
();
It’s that easy. The Contact
is now starred.
In order to find all of the Star
s on a given Contact
, we call the stars()
method like in Example 5-55.
$contact
=
Contact
::
first
();
$contact
->
stars
->
each
(
function
(
$star
)
{
// Do stuff
});
If we have an instance of Star
, we can get its target by calling the method we used to define its morphTo
relationship, which in this context is starrable()
. Take a look at Example 5-56.
$stars
=
Star
::
all
();
$stars
->
each
(
function
(
$star
)
{
var_dump
(
$star
->
starrable
);
// An instance of Contact or Event
});
Finally, you might be wondering, “What if I want to know who starred this contact?” That’s a great question. It’s as simple as adding user_id
to your stars
table, and then setting up that a User
has many Star
s and a Star
belongs to one User
—a one-to-many relationship (Example 5-57). The stars
table becomes almost a pivot table between your User
and your Contact
s and Event
s.
class
Star
extends
Model
{
public
function
starrable
()
{
return
$this
->
morphsTo
;
}
public
function
user
()
{
return
$this
->
belongsTo
(
User
::
class
);
}
}
class
User
extends
Model
{
public
function
stars
()
{
return
$this
->
hasMany
(
Star
::
class
);
}
}
That’s it! You can now run $star->user
or $user->stars
to find a list of a User
’s Star
s or to find the starring User
from a Star
. Also, when you create a new Star
, you’ll now want to pass the User
:
$user
=
User
::
first
();
$event
=
Event
::
first
();
$event
->
stars
()
->
create
([
'user_id'
=>
$user
->
id
]);
The most complex and least common of the relationship types, many-to-many polymorphic relationships are like polymorphic relationships, except instead of being one to many, they’re many to many.
The most common example for this relationship type is the tag, so I’ll keep it safe and use that as our example. Let’s imagine you want to be able to tag Contact
s and Event
s. The uniqueness of many-to-many polymorphism is that it’s many to many: each tag may be applied to multiple items, and each tagged item might have multiple tags. And to add to that, it’s polymorphic: tags can be related to items of several different types. For the database, we’ll start with the normal structure of the polymorphic relationship but also add a pivot table.
This means we’ll need a contacts
table, an events
table, and a tags
table, all shaped like normal with an ID and whatever properties you want, and a new taggables
table, which will have tag_id
, taggable_id
, and taggable_type
fields. Each entry into the taggables
table will relate a tag with one of the taggable content types.
Now let’s define this relationship on our models, as seen in Example 5-58.
class
Contact
extends
Model
{
public
function
tags
()
{
return
$this
->
morphToMany
(
Tag
::
class
,
'taggable'
);
}
}
class
Event
extends
Model
{
public
function
tags
()
{
return
$this
->
morphToMany
(
Tag
::
class
,
'taggable'
);
}
}
class
Tag
extends
Model
{
public
function
contacts
()
{
return
$this
->
morphedByMany
(
Contact
::
class
,
'taggable'
);
}
public
function
events
()
{
return
$this
->
morphedByMany
(
Event
::
class
,
'taggable'
);
}
}
Here’s how to create your first tag:
$tag
=
Tag
::
firstOrCreate
([
'name'
=>
'likes-cheese'
]);
$contact
=
Contact
::
first
();
$contact
->
tags
()
->
attach
(
$tag
->
id
);
We get the results of this relationship like normal, as seen in Example 5-59.
$contact
=
Contact
::
first
();
$contact
->
tags
->
each
(
function
(
$tag
)
{
// Do stuff
});
$tag
=
Tag
::
first
();
$tag
->
contacts
->
each
(
function
(
$contact
)
{
// Do stuff
});
Remember, any Eloquent models by default will have created_at
and updated_at
timestamps. Eloquent will set the updated_at
timestamp automatically any time you make any changes to a record.
When a related item has a belongsTo
or belongsToMany
relationship with another item, it might be valuable to mark the other item as updated any time the related item is updated. For example, if a PhoneNumber
is updated, maybe the Contact
it’s connected to should be marked as having been updated as well.
We can accomplish this by adding the method name for that relationship to a $touches
array property on the child class, as in Example 5-60.
class
PhoneNumber
extends
Model
{
protected
$touches
=
[
'contact'
];
public
function
contact
()
{
return
$this
->
belongsTo
(
Contact
::
class
);
}
}
By default, Eloquent loads relationships using “lazy loading.” This means when you first load a model instance, its related models will not be loaded along with it. Rather, they’ll only be loaded once you access them on the model; they’re “lazy” and don’t do any work until called upon.
This can become a problem if you’re iterating over a list of model instances and each has a related item (or items) that you’re working on. The problem with lazy loading is that it can introduce significant database load (often the N+1 problem, if you’re familiar with the term; if not, just ignore this parenthetical remark). For instance, every time the loop in Example 5-61 runs, it executes a new database query to look up the phone numbers for that Contact
.
$contacts
=
Contact
::
all
();
foreach
(
$contacts
as
$contact
)
{
foreach
(
$contact
->
phone_numbers
as
$phone_number
)
{
echo
$phone_number
->
number
;
}
}
If you are loading a model instance, and you know you’ll be working with its relationships, you can instead choose to “eager-load” one or many of its sets of related items:
$contacts
=
Contact
::
with
(
'phoneNumbers'
)
->
get
();
Using the with()
method with a retrieval gets all of the items related to the pulled item(s); as you can see in this example, you pass it the name of the method the relationship is defined by.
When we use eager loading, instead of pulling the related items one at a time when they’re requested (e.g., selecting one contact’s phone numbers each time a foreach
loop runs), we have a single query to pull the initial items (selecting all contacts) and a second query to pull all their related items (selecting all phone numbers owned by the contacts we just pulled).
You can eager-load multiple relationships by passing multiple parameters to the with()
call:
$contacts
=
Contact
::
with
(
'phoneNumbers'
,
'addresses'
)
->
get
();
And you can nest eager loading to eager-load the relationships of relationships:
$authors
=
Author
::
with
(
'posts.comments'
)
->
get
();
I know it sounds crazy, because we just defined eager loading as sort of the opposite of lazy loading, but sometimes you don’t know you want to perform an eager-load query until after the initial instances have been pulled. In this context, you’re still able to make a single query to look up all of the related items, avoiding N+1 cost. We call this “lazy eager loading”:
$contacts
=
Contact
::
all
();
if
(
$showPhoneNumbers
)
{
$contacts
->
load
(
'phoneNumbers'
);
}
To load a relationship only when it has not already been loaded, use the loadMissing()
method (available only since Laravel 5.5):
$contacts
=
Contact
::
all
();
if
(
$showPhoneNumbers
)
{
$contacts
->
loadMissing
(
'phoneNumbers'
);
}
If you want to eager-load relationships but only so you can have access to the count of items in each relationship, you can try withCount()
:
$authors
=
Author
::
withCount
(
'posts'
)
->
get
();
// Adds a "posts_count" integer to each Author with a count of that
// author's related posts
Eloquent models fire events out into the void of your application every time certain actions happen, regardless of whether you’re listening. If you’re familiar with pub/sub, it’s this same model (you’ll learn more about Laravel’s entire event system in Chapter 16).
Here’s a quick rundown of binding a listener to when a new Contact
is created. We’re going to bind it in the boot()
method of AppServiceProvider
, and let’s imagine we’re notifying a third-party service every time we create a new Contact
.
class
AppServiceProvider
extends
ServiceProvider
{
public
function
boot
()
{
$thirdPartyService
=
new
SomeThirdPartyService
;
Contact
::
creating
(
function
(
$contact
)
use
(
$thirdPartyService
)
{
try
{
$thirdPartyService
->
addContact
(
$contact
);
}
catch
(
Exception
$e
)
{
Log
::
error
(
'Failed adding contact to ThirdPartyService; canceled.'
);
return
false
;
// Cancels Eloquent create()
}
});
}
We can see a few things in Example 5-62. First, we use Modelname
as the method, and pass it a closure. The closure gets access to the model instance that is being operated on. Second, we’re going to need to define this listener in a service provider somewhere. And third, if we return ::
eventName()false
, the operation will cancel and the save()
or update()
will be canceled.
Here are the events that every Eloquent model fires:
creating
created
updating
updated
saving
saved
deleting
deleted
restoring
restored
retrieved
Most of these should be pretty clear, except possibly restoring
and restored
, which fire when you’re restoring a soft-deleted row. Also, saving
is fired for both creating
and updating
and saved
is fired for both created
and updated
.
retrieved
(available in Laravel 5.5 and later) is fired when an existing model is retrieved from the database.
Laravel’s entire application testing framework makes it easy to test your database—not by writing unit tests against Eloquent, but by just being willing to test your entire application.
Take this scenario. You want to test to ensure that a particular page shows one contact but not another. Some of that logic has to do with the interplay between the URL and the controller and the database, so the best way to test it is an application test. You might be thinking about mocking Eloquent calls and trying to avoid the system hitting the database. Don’t do it. Try Example 5-63 instead.
public
function
test_active_page_shows_active_and_not_inactive_contacts
()
{
$activeContact
=
factory
(
Contact
::
class
)
->
create
();
$inactiveContact
=
factory
(
Contact
::
class
)
->
states
(
'inactive'
)
->
create
();
$this
->
get
(
'active-contacts'
)
->
assertSee
(
$activeContact
->
name
)
->
assertDontSee
(
$inactiveContact
->
name
);
}
As you can see, model factories and Laravel’s application testing features are great for testing database calls.
Alternatively, you can look for that record directly in the database, as in Example 5-64.
public
function
test_contact_creation_works
()
{
$this
->
post
(
'contacts'
,
[
'email'
=>
'[email protected]'
]);
$this
->
assertDatabaseHas
(
'contacts'
,
[
'email'
=>
'[email protected]'
]);
}
Eloquent and Laravel’s database framework are tested extensively. You don’t need to test them. You don’t need to mock them. If you really want to avoid hitting the database, you can use a repository and then return unsaved instances of your Eloquent models. But the most important message is, test the way your application uses your database logic.
If you have custom accessors, mutators, scopes, or whatever else, you can also test them directly, as in Example 5-65.
public
function
test_full_name_accessor_works
()
{
$contact
=
factory
(
Contact
::
class
)
->
make
([
'first_name'
=>
'Alphonse'
,
'last_name'
=>
'Cumberbund'
]);
$this
->
assertEquals
(
'Alphonse Cumberbund'
,
$contact
->
fullName
);
}
public
function
test_vip_scope_filters_out_non_vips
()
{
$vip
=
factory
(
Contact
::
class
)
->
states
(
'vip'
)
->
create
();
$nonVip
=
factory
(
Contact
::
class
)
->
create
();
$vips
=
Contact
::
vips
()
->
get
();
$this
->
assertTrue
(
$vips
->
contains
(
'id'
,
$vip
->
id
));
$this
->
assertFalse
(
$vips
->
contains
(
'id'
,
$nonVip
->
id
));
}
Just avoid writing tests that leave you creating complex “Demeter chains” to assert that a particular fluent stack was called on some database mock. If your testing starts to get overwhelming and complex around the database layer, it’s because you’re allowing preconceived notions to force you into unnecessarily complex systems. Keep it simple.
In projects running versions of Laravel prior to 5.4, assertDatabaseHas()
should be replaced by seeInDatabase()
, get()
should be replaced by visit()
, assertSee()
should be replaced by see()
, and assertDontSee()
should be replaced by dontSee()
.
Laravel comes with a suite of powerful database tools, including migrations, seeding, an elegant query builder, and Eloquent, a powerful ActiveRecord ORM. Laravel’s database tools don’t require you to use Eloquent at all—you can access and manipulate the database with a thin layer of convenience without having to write SQL directly. But adding an ORM, whether it’s Eloquent or Doctrine or whatever else, is easy and can work neatly with Laravel’s core database tools.
Eloquent follows the Active Record pattern, which makes it simple to define a class of database-backed objects, including which table they’re stored in and the shape of their columns, accessors, and mutators. Eloquent can handle every sort of normal SQL action and also complex relationships, up to and including polymorphic many-to-many relationships.
Laravel also has a robust system for testing databases, including model factories.