Category: Laravel Database

$ php artisan make:model Role -m

Schema::create('roles', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->timestamps();
        });

$ php artisan make:migration create_users_roles_table –create=role_user

public function up()
    {
        Schema::create('role_user', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('user_id');
            $table->integer('role_id');
            $table->timestamps();
        });
    }

$ php artisan migrate

mysql> describe roles;
+————+——————+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+————+——————+——+—–+———+—————-+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
+————+——————+——+—–+———+—————-+
4 rows in set (0.00 sec)

mysql> insert into roles (name) values (‘administrator’);
mysql> insert into roles (name) values (‘subscriber’);
Query OK, 1 row affected (0.00 sec)

mysql> select * from roles;
+—-+—————+————+————+
| id | name | created_at | updated_at |
+—-+—————+————+————+
| 1 | administrator | NULL | NULL |
| 2 | subscriber | NULL | NULL |
+—-+—————+————+————+
2 rows in set (0.00 sec)

mysql> select * from users;
+—-+———-+——————–+———-+—————-+———————+———————+
| id | name | email | password | remember_token | created_at | updated_at |
+—-+———-+——————–+———-+—————-+———————+———————+
| 1 | hpscript | hpscript@gmail.com | password | NULL | 2019-12-06 13:23:40 | 2019-12-06 13:23:40 |
| 2 | peter | peter@gmail.com | password | NULL | 2019-12-06 18:03:20 | 2019-12-06 18:03:20 |
+—-+———-+——————–+———-+—————-+———————+———————+
2 rows in set (0.00 sec)

> insert into role_user (user_id, role_id) values (1, 1);
> insert into role_user (user_id, role_id) values (2, 2);

mysql> select * from role_user;
+—-+———+———+————+————+
| id | user_id | role_id | created_at | updated_at |
+—-+———+———+————+————+
| 1 | 1 | 1 | NULL | NULL |
| 2 | 2 | 2 | NULL | NULL |
+—-+———+———+————+————+
2 rows in set (0.00 sec)

then user table

public function roles(){
            return $this->belongsToMany('App\Role');
    }

Route::get('/user/{id}/role', function($id){
		$user = User::find($id);

		foreach($user->roles as $role){
			return $role->name;
		}
});

http://192.168.33.10:8000/user/1/role
administrator

あれ、user->idからrolesテーブルに行って、rolesテーブルのnameをroleテーブルから呼び出す
モデルでは、belongsToManyでrolesを指定する
pivot tableであるuser_rolesは直接は書かない

many to manyはpivot　 tableの概念だな

many to manyはuser tableにロールカラムを追加すれば良さそうだが、ロール自体もテーブルで管理したい時は、pivot tableを使ったmany to manyの方が効率的ということだ
若干複雑な概念だが、分かってきた

add hasMany property

public function posts(){
            return $this->hasMany('App\Post');
    }

route

public function posts(){
            return $this->hasMany('App\Post');
    }

hasOne, hasManyいずれにしてもモデルでプロパティを定義する必要があります。

mysql> select * from posts;
+—-+———+————–+————————–+———————+———————+———-+————+
| id | user_id | title | content | created_at | updated_at | is_admin | deleted_at |
+—-+———+————–+————————–+———————+———————+———-+————+
| 1 | 1 | php has many | learning has many method | 2019-12-06 13:19:29 | 2019-12-06 13:19:29 | 0 | NULL |
| 2 | 1 | php has many | learning has many method | 2019-12-06 13:20:01 | 2019-12-06 13:20:01 | 0 | NULL |
+—-+———+————–+————————–+———————+———————+———-+————+

Route::get('/posts', function(){
		$user = User::find(1);

		foreach($user->posts as $post){
			echo	$post->title . "<br>";
		}
});

hasManyの場合は、レコードが複数の為、returnではなく、foreachを使う

one to oneのinverseはモデルでhas oneではなく、belongToになる

class Post extends Model
{
    //
    use SoftDeletes;

    protected $dates = ['deleted_at'];
    // protected $table = 'posts'; 
	protected $fillable = [
		'title',
		'content'
	];

	public function user(){
			return $this->belongTo('App\User');
	}
}

Route::get('/post/{id}/user', function($id){

	return Post::find($id)->user->name;
});

mysql> select * from posts;
+—-+———+———-+—————————————————+———————+———————+———-+————+
| id | user_id | title | content | created_at | updated_at | is_admin | deleted_at |
+—-+———+———-+—————————————————+———————+———————+———-+————+
| 2 | 1 | PHP post | wow eloquent is really cool, look at this content | 2019-12-06 07:04:25 | 2019-12-06 07:04:25 | 0 | NULL |
+—-+———+———-+—————————————————+———————+———————+———-+————+
1 row in set (0.00 sec)

大体のケースはUser側から下にぶら下がるから、ユーザを呼び出す時は、belongsToを使うイメージですな。

public function up()
    {
        Schema::create('posts', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('user_id')->unsigned();
            $table->string('title');
            $table->text('content');
            $table->timestamps();
        });
    }

$ php artisan migrate:refresh

Route::get('/basicinsert', function(){

	$post = new Post();

	$post->title = 'PHP post';
	$post->user_id = 1;
	$post->content = 'wow eloquent is really cool, look at this content';
	$post->save();
});

mysql> select * from posts;
+—-+———+———-+—————————————————+———————+———————+———-+————+
| id | user_id | title | content | created_at | updated_at | is_admin | deleted_at |
+—-+———+———-+—————————————————+———————+———————+———-+————+
| 2 | 1 | PHP post | wow eloquent is really cool, look at this content | 2019-12-06 07:04:25 | 2019-12-06 07:04:25 | 0 | NULL |
+—-+———+———-+—————————————————+———————+———————+———-+————+
1 row in set (0.00 sec)

User.php

namespace App;

use Illuminate\Foundation\Auth\User as Authenticatable;

class User extends Authenticatable
{
    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'name', 'email', 'password',
    ];

    /**
     * The attributes that should be hidden for arrays.
     *
     * @var array
     */
    protected $hidden = [
        'password', 'remember_token',
    ];

 public function post(){
        return $this->hasOne('App\Post');
    }
}

Route::get('/user/{id}/post', function($id){

	return	User::find(1)->post;
});

{“id”:2,”user_id”:1,”title”:”PHP post”,”content”:”wow eloquent is really cool, look at this content”,”created_at”:”2019-12-06 07:04:25″,”updated_at”:”2019-12-06 07:04:25″,”is_admin”:0,”deleted_at”:null}

userのモデル側にhasOneとしてるから、Post::find(4)->userだと表示されないのか？？
あれ、これって、いわゆるエンティティリレーションモデルのこと？？

mysql> select * from posts;
+—-+——————-+—————————————————+———————+———————+———-+———————+
| id | title | content | created_at | updated_at | is_admin | deleted_at |
+—-+——————-+—————————————————+———————+———————+———-+———————+
| 1 | Update tile | Laravel is the best thing that happen to PHP | NULL | 2019-12-06 06:07:16 | 0 | NULL |
| 4 | new ORM title 2 | wow eloquent is really cool, look at this content | 2019-12-05 18:23:33 | 2019-12-06 06:09:28 | 0 | 2019-12-06 06:09:28 |
| 6 | php create method | Wow I’m learning a lot | 2019-12-06 05:09:21 | 2019-12-06 06:07:16 | 0 | NULL |
| 7 | new ORM title | wow eloquent is really cool, look at this content | 2019-12-06 05:45:03 | 2019-12-06 06:07:16 | 0 | NULL |
+—-+——————-+—————————————————+———————+———————+———-+———————+
4 rows in set (0.00 sec)

Route::get('/forcedelete', function(){

		Post::withTrashed()->where('is_admin', 0)->forceDelete();
});

mysql> select * from posts;
Empty set (0.00 sec)

あれ？　全部削除？？　あ、withTrashedはrecordとsoftdeleteが混じっているので、softdeleteのみの場合は、onlyTrashedですな。失敬

Route::get('/restore', function(){
		Post::withTrashed()->where('is_admin', 0)->restore();
});

just add restore function, easy way!
mysql> select * from posts;
+—-+——————-+—————————————————+———————+———————+———-+————+
| id | title | content | created_at | updated_at | is_admin | deleted_at |
+—-+——————-+—————————————————+———————+———————+———-+————+
| 1 | Update tile | Laravel is the best thing that happen to PHP | NULL | 2019-12-06 06:07:16 | 0 | NULL |
| 4 | new ORM title 2 | wow eloquent is really cool, look at this content | 2019-12-05 18:23:33 | 2019-12-06 06:07:16 | 0 | NULL |
| 6 | php create method | Wow I’m learning a lot | 2019-12-06 05:09:21 | 2019-12-06 06:07:16 | 0 | NULL |
| 7 | new ORM title | wow eloquent is really cool, look at this content | 2019-12-06 05:45:03 | 2019-12-06 06:07:16 | 0 | NULL |
+—-+——————-+—————————————————+———————+———————+———-+————+
4 rows in set (0.00 sec)

softdeleteとは？
聞きなれない言葉ですが、論理削除の意味です。
論理削除とは、表面上は削除されるが、データはテーブルに残っている状態

migrationで、softdeletesメソッドを呼び出し、use softdetesとします。

namespace App;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;

class Post extends Model
{
    //
    use SoftDeletes;

    protected $dates = ['deleted_at'];
    // protected $table = 'posts'; 
	protected $fillable = [
		'title',
		'content'
	];
}

さらにmigrationファイルを追加

public function up()
    {
        Schema::table('posts', function (Blueprint $table) {
            //
            $table->softDeletes();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('posts', function (Blueprint $table) {
            //'
                        $table->dropColumn('deleted_at');
        });
    }

route

Route::get('/softdelete', function(){
		Post::find(1)->delete();
});

mysql> select * from posts;
+—-+——————-+—————————————————+———————+———————+———-+———————+
| id | title | content | created_at | updated_at | is_admin | deleted_at |
+—-+——————-+—————————————————+———————+———————+———-+———————+
| 1 | Update tile | Laravel is the best thing that happen to PHP | NULL | 2019-12-06 05:35:12 | 0 | 2019-12-06 05:35:12 |
| 4 | new ORM title 2 | wow eloquent is really cool, look at this content | 2019-12-05 18:23:33 | 2019-12-05 18:33:31 | 0 | NULL |
| 6 | php create method | Wow I’m learning a lot | 2019-12-06 05:09:21 | 2019-12-06 05:09:21 | 0 | NULL |
+—-+——————-+—————————————————+———————+———————+———-+———————+
3 rows in set (0.00 sec)

アプリケーションでユーザがアカウント削除するが、論理データはテーブルに残す時などに使えそうです。

Route::get('/readofsoftdelete', function(){
		$post = Post::find(1);
		return $post;
});

soft deleteのレコードは、eloquentでfindしようとしても、blank扱いとなります^^
beautiful than expected

Route::get('/readofsoftdelete', function(){
		// $post = Post::find(1);
		// return $post;
		$post = Post::withTrashed()->where('id', 1)->get();
		return $post;
});

[{“id”:1,”title”:”Update tile”,”content”:”Laravel is the best thing that happen to PHP”,”created_at”:null,”updated_at”:”2019-12-06 05:35:12″,”is_admin”:0,”deleted_at”:”2019-12-06 05:35:12″}]

onlyTrashedでsoftdeleted itemを呼び出すこともできる

Route::get('/readofsoftdelete', function(){
		// $post = Post::find(1);
		// return $post;
		// $post = Post::withTrashed()->where('id', 1)->get();
		// return $post;
		$post = Post::onlyTrashed()->where('is_admin', 0)->get();
		return $post;

});

Route::get('/update', function(){

	Post::where('id', 3)->update(['title'=>'new php title','content'=>'I found it']);
});

mysql> select * from posts;
+—-+——————-+——————————————————+———————+———————+———-+
| id | title | content | created_at | updated_at | is_admin |
+—-+——————-+——————————————————+———————+———————+———-+
| 1 | Update tile | Laravel is the best thing that happen to PHP | NULL | NULL | 0 |
| 3 | laravel awesome | Laravel is the best thing that happen to PHP, period | NULL | NULL | 0 |
| 4 | new ORM title 2 | wow eloquent is really cool, look at this content | 2019-12-05 18:23:33 | 2019-12-05 18:33:31 | 0 |
| 5 | php create method | Wow I’m learning a lot | 2019-12-05 18:47:53 | 2019-12-05 18:47:53 | 0 |
+—-+——————-+——————————————————+———————+———————+———-+
4 rows in set (0.00 sec)

Route::get('/delete', function(){

	$post = Post::find(5);

	$post->delete();
});

Route::get('/delete2', function(){
	Post::destroy(3);
});

あ、何故deleteではなくdestroyの名称が使われているかわかりました。

Route::get('/basicinsert', function(){

	$post = new Post();

	$post->title = 'new ORM title';
	$post->content = 'wow eloquent is really cool, look at this content';
	$post->save();
});

mysql> select * from posts;
+—-+—————–+——————————————————+———————+———————+———-+
| id | title | content | created_at | updated_at | is_admin |
+—-+—————–+——————————————————+———————+———————+———-+
| 1 | Update tile | Laravel is the best thing that happen to PHP | NULL | NULL | 0 |
| 3 | laravel awesome | Laravel is the best thing that happen to PHP, period | NULL | NULL | 0 |
| 4 | new ORM title | wow eloquent is really cool, look at this content | 2019-12-05 18:23:33 | 2019-12-05 18:23:33 | 0 |
+—-+—————–+——————————————————+———————+———————+———-+
3 rows in set (0.00 sec)

Route::get('/basicinsert2', function(){

	$post = Post::find(4);

	$post->title = 'new ORM title 2';
	$post->content = 'wow eloquent is really cool, look at this content';
	$post->save();
});

mysql> select * from posts;
+—-+—————–+——————————————————+———————+———————+———-+
| id | title | content | created_at | updated_at | is_admin |
+—-+—————–+——————————————————+———————+———————+———-+
| 1 | Update tile | Laravel is the best thing that happen to PHP | NULL | NULL | 0 |
| 3 | laravel awesome | Laravel is the best thing that happen to PHP, period | NULL | NULL | 0 |
| 4 | new ORM title 2 | wow eloquent is really cool, look at this content | 2019-12-05 18:23:33 | 2019-12-05 18:33:31 | 0 |
+—-+—————–+——————————————————+———————+———————+———-+
3 rows in set (0.00 sec)

update methodとは異なる概念のようです。