Working with databases
Table des matières
1. Eloquent
1.1. Create a model
With Eloquent, a table is represented by class which is an extension of a model.
To work with a model, there is an artisan command that will generate for us the model. By convention, if the table is called Articles, the model will be Article (no plural form)
php artisan make:model ArticleThis done, a new file will be created: /app/Article.php
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Article extends Model
{
//
}Now, just update and add our fields
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Article extends Model
{
protected $table = 'articles';
public $timestamps = true;
public $title = '';
public $content = '';
}If, when creating the table, we’ve foresee $table->timestamps(); in the up() function, then Laravel has create two fields :
-
created_at -
updated_at
This automatically. To ask to Eloquent to manage these fields, the Model should set the $timestamps variable to true, false otherwise.
1.2. Use the model
Once the model has been created, we then have a class that is, in fact, our table.
To add a new request f.i., we can, in a controller, have something like:
<?php
namespace App\Http\Controllers;
use App\Article; // use our model
use App\Http\Requests\ArticleRequest;
class ArticleController extends Controller
{
public function getForm()
{
return view('article');
}
public function postForm(ArticleRequest $request)
{
$article = new Article;
$article->title = $request->input('title');
$article->content = $request->input('content');
$article->save();
return view('article_ok');
}
}Thanks the model, fields like title and content are now properties of the object. Just assign values to them and call the save() method.
1.3. Laravel N+1 Query Detector
When working with a schema having a foreign key (f.i. a table Todos with a user_id field that is a foreign key to the users table so, then, we can retrieve f.i. the name of the author); Laravel will make a lot of queries:
- One query for retrieving the list of todos (with or without pagination)
- Then one query by author: if our list has 100 entries, Laravel will make 100 queries even if the author_id is always the same.
So, for instance, consider this example, a todos table with a user_id to store the author. That field has foreign key relation with the id field of table users.
Schema::create('todos', function (Blueprint $table) {
// Our primary key
$table->increments('id');
// Allow Eloquent to add two fields and managed them:
// created_at and updated_at
$table->timestamps();
$table->string('title', 100);
$table->boolean('completed')->default(0);
$table->text('description', 1000)->nullable();
// The author of the record
$table->integer('user_id')->unsigned();
$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
});In our Model, we’ll have:
public function user()
{
return $this->belongsTo('App\User', 'user_id', 'id');
}
The image here above has been retrieved thanks to the Laravel Debugbar. We can see that we’ll have a query that will return the first 100 entries (i.e. todos) of the table
select * from `todos` order by `todos`.`created_at` desc limit 100 offset 0Then since our page show the name of the author, we’ve one query by todo:
select * from `users` where `users`.`id` = '3' limit 1Many things are un-optimized but mainly why repeating that last query more than one? It’s always the same user (3).
The N+1 Query Detector will put this in highlight and will suggest the solution.
So, first install the package:
composer require beyondcode/laravel-query-detector --devAnd that is. Nothing else should be done.
Refresh the HTML page and the package will immediately be active:
A javascript has been injected in the page for displaying a message and already give the tip : for the App\Todo Model, there is a relation user (which is the name of our function that contains the Belongsto). The model use the table todos so, in the function used by the current view (probably index or getPaginate), we need to use the eager loading feature i.e. add the :with("user") parameter.
So, concretely, we’ll adapt our repository like below.
If we use a pagination:
public function getPaginate(int $n)
{
return $this->todo::with('user')
->latest('todos.created_at')
->paginate($n);
}and, for the index function,
public function index() : Collection
{
return $this->todo::with('user')->all();
}Previously, we had 102 queries (for displaying 100 items). Now, only 3:
The load duration was 756,94 milliseconds without the ::with() statement and only 263,18 milliseconds then. Nice optimization.
(we can see that the query select * from users where users.id = '3' limit 1 is now select * from users where users.id in ('3'), instead of making one request by author, Laravel will make one for each author (here there is only one))
Read more on Laracasts and take a look on the video tutorial.
Working with models and migrations
Configuration of the database
Edit the /.env file and fill in information’s about the database.
For instance:
DB_CONNECTION=mysql
DB_HOST=localhost
DB_PORT=3306
DB_DATABASE=tuto
DB_USERNAME=root
DB_PASSWORD=Create a database
To create a database, open a command prompt and run:
mysql -u rootwhere root is the default username.
Once successfully connected, to create a database execute:
CREATE DATABASE dbname;Steps
Install a migration plan
php artisan migrate:installA new table will then be created in the database
That table is really important since it’ll contains every actiondone on database’s schema level.
Create a migration
If we need to create a new table; for instance, articles, we’ll run
php artisan make:migration create_articles_table
Artisan will create a file in the /databases/migrations folder.
The file will contains all information’s needed for creating (function up()) or removing the file (function down()). Laravel will assume that we’ll have an autonumber field called id and a timestamp field.
<?php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateArticlesTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('articles', function (Blueprint $table) {
$table->increments('id');
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('articles');
}
}We’ll need to update the up function for adding our fields:
public function up()
{
Schema::create('articles', function (Blueprint $table) {
$table->increments('id');
$table->string('title', 100);
$table->string('content', 100);
$table->timestamps();
});
}Execute a migration
The execution of the migration plan will process each table and really create the tables in the database or removing them.
php artisan migrateIf everything goes fine
c:\Christophe\Repository\app_test>php artisan migrate
Migrating: 2014_10_12_000000_create_users_table
Migrated: 2014_10_12_000000_create_users_table
Migrating: 2014_10_12_100000_create_password_resets_table
Migrated: 2014_10_12_100000_create_password_resets_table
Migrating: 2018_08_01_075115_create_articles_table
Migrated: 2018_08_01_075115_create_articles_tableMigrated means create in that case so, now, our database has an article table with the fields defined in the up() function.
Rollback or Refresh
In case of error (incorrect datatype f.i.), it’s possible to cancel changes and go back by issuing a rollback:
php artisan migrate::rollbackBut we can also update our migration plan, add, remove or change fields’s definition and refresh the structure
For instance, add an author field:
public function up()
{
Schema::create('articles', function (Blueprint $table) {
// some definition
$table->string('author', 40);
});
}Then
php artisan migrate::refresh2. Laravel Schema Designer
Schema Designer is an impressive tool that will generate PHP source code for your database. Just draw tables, define tables, fields and attributes, ... and click on the Export All button to receive PHP files that you just need to put in your Laravel project.
Awesome!