Category: Laravel Database

The last of laravel database relation.

$ php artisan make:model Post -m
$ php artisan make:model Video -m
$ php artisan make:model Tags -m

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

$ php artisan make:model Taggable -m

Schema::create('taggables', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('tag_id');
            $table->integer('taggable_id');
            $table->string('taggable_type');
            $table->timestamps();
        });

$ php artisan migrate

Post.php, Video.php

    protected $fillable = ['name'];
    public function tags(){
    	return $this->norphToMany('App\Tags', 'taggable');
    }

Tags.php

class Tags extends Model
{
    protected $fillable = ['name'];
}

insert into tags (name) values (‘php’);
insert into tags (name) values (‘ruby’);
model name間違えた。。
alter table taggables change column tag_id tags_id int;

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

	$post = Post::create(['name'=>'my frist post']);
	$tag1 = Tags::findOrFail(1);
	$post->tags()->save($tag1);

	$video = Video::create(['name'=>'video.now']);
	$tag2 = Tags::findOrFail(2);
	$video->tags()->save($tag2);

});

read

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

	$post = Post::findOrFail(9);
	foreach($post->tags as $tag){
		echo $tag;
	}
});

update

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

	$post = Post::findOrFail(9);
	foreach($post->tags as $tag){
		$tag->whereId(1)->update(['name'=>'google']);
	}
});

delete

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

	$post = Post::find(1);

	foreach($post->tags as $tag){
		$tag->whereId(1)->delete();
	}
});

Polymorphicの例は、staffの写真と商品の写真をphotosというテーブルで管理し、staffと商品を連携させることです。

$ php artisan make:model Staff -m

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

$ php artisan make:model Product -m

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

$ php artisan make:model Photo -m

Schema::create('photos', function (Blueprint $table) {
            $table->increments('id');
            $table->string('path');
            $table->integer('imageable_id');
            $table->string('imageable_type');
            $table->timestamps();
        });

$ php artisan migrate

Photo.php

public function imageable(){
    	return $this->morphTo();
    }

Staff.php, Product.php

public function photos(){
    	return $this->morphMany('App\Photo', 'imageable');
    }

staffsとproductsのテーブルにレコード挿入
mysql> insert into staff (name) values (‘peter’);
Query OK, 1 row affected (0.06 sec)

mysql> insert into staff (name) values (‘john’);
Query OK, 1 row affected (0.00 sec)

mysql> insert into product (name) values (‘laravel’);
ERROR 1146 (42S02): Table ‘polymorphic.product’ doesn’t exist
mysql> insert into products (name) values (‘laravel’);
Query OK, 1 row affected (0.01 sec)

mysql> insert into products (name) values (‘cake’);
Query OK, 1 row affected (0.00 sec)

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

	$staff = Staff::find(1);
	$staff->photos()->create(['path'=>'example.jpg']);

});

read

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

	$staff = Staff::findOrFail(1);

	foreach($staff->photos as $photo){
		return $photo->path;
	}

});

update

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

	$staff = Staff::findOrFail(1);

	$photo = $staff->photos(1)->whereId(2)->first();

	$photo->path = "update exampl.jpg";
	$photo->save();
});

delete

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

	$staff = Staff::findOrFail(1);
	$staff->photos()->delete();
});

connect

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

	$staff = Staff::findOrFail(1);

	$photo = Pohoto::findOrFail(3);

	$staff->photos()->save($photo);

});

manytomanyはpivotテーブルを使って、子供のレコードを管理する

$ php artisan make:model Role -m
$ php artisan make:migration create_role_user_table –create=role_user

role_user

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

role

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

$ php artisan migrate

User.php

public function roles(){

        return $this->belongsToMany('App\Role');
    }

Role.php

class Role extends Model
{
    //
    protected $fillable = ['name'];
}

insert into users (name, email, password) values (‘peter’, ‘peter@gmail’, ‘1234’);

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

	$role = new Role(['name'=>'adminstrator']);

	$user->roles()->save($role);

});

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

read

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

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

update
has()で確認する

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

	$user = User::findOrFail(1);

	if($user->has('roles')){

		foreach($user->roles as $role){
			if($role->name == 'adminstrator'){
				$role->name = 'Adminstrator';
				$role->save();
			}
		}
	}
});

delete

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

	$user = User::findOrFail(1);

	$user->roles()->delete();

});

attach & delete

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

	$user = User::findOrFail(1);

	$user->roles()->attach(2);

});

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

	$user = User::findOrFail(1);

	$user->roles()->detach(2);

});

createだけでなく、attachとdeleteがあるってのは、理にかなってます。

$ php artisan make:model Post -m

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

$ php artisan migrate

User.php

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

Post.php

protected $fillable = [
    	'title',
    	'body'
    ];

$ php artisan tinker
Psy Shell v0.7.2 (PHP 7.1.7 — cli) by Justin Hileman
>>> App\User::create([‘name’=>’Peter’, ’email’=>’peter@gmail.com’, ‘password’=>’1234’]);
=> App\User {#634
name: “Peter”,
email: “peter@gmail.com”,
updated_at: “2019-12-08 18:25:17”,
created_at: “2019-12-08 18:25:17”,
id: 1,
}

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

	$user = User::findOrFail(1);

	$post = new Post(['title'=>'My first post', 'body'=>'I love laravel']);
	$user->posts()->save($post);
});

read

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

	$user = User::findOrFail(1);

	return $user->posts;
});

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

	$user = User::findOrFail(1);

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

update

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

	$user = User::find(1);
	$user->posts()->whereId(1)->update(['title'=>'I love', 'body'=>'this is awsome, thank you']);
});

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

	$user = User::find(1);
	$user->posts()->whereId(1)->delete();
});

insertはsave(), updateはupdate(), deleteはdelete()メソッドです。
大分つかめてきました。
keep going

$ php artisan make:model Address -m

migration file

{
        Schema::create('addresses', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('user_id')->unsigned()->nullable();
            $table->string('name');
            $table->timestamps();
        });
    }

$ php artisan migrate

User.php

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

//data挿入
mysql> use onetoone;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+——————–+
| Tables_in_onetoone |
+——————–+
| addresses |
| migrations |
| password_resets |
| users |
+——————–+
4 rows in set (0.00 sec)

mysql> insert into users (name, email, password) values (‘peter’, ‘peter@gmail’, ‘1234’);
Query OK, 1 row affected (0.00 sec)

Address.php

class Address extends Model
{
    //
    protected $fillable = [
    	'name'
    ];
}

route.php

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

	$user = User::findOrFail(1);
	$address = New Address(['name'=>'1234 Huston av NY NY 11218']);
	$user->address()->save($address);
});

mysql> select * from addresses;
+—-+———+—————————-+———————+———————+
| id | user_id | name | created_at | updated_at |
+—-+———+—————————-+———————+———————+
| 1 | 1 | 1234 Huston av NY NY 11218 | 2019-12-08 04:19:29 | 2019-12-08 04:19:29 |
+—-+———+—————————-+———————+———————+
1 row in set (0.00 sec)

$user->save();のところを、hasOneでは$user->address()->save($address);って書くんだ。I aint know what’s going on inside.

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

	$address = Address::where('user_id', 1)->first();

	$address->name = "4532 Updated new address";
	$address->save();
});

mysql> select * from addresses;
+—-+———+—————————+———————+———————+
| id | user_id | name | created_at | updated_at |
+—-+———+—————————+———————+———————+
| 1 | 1 | 4532 Updated new address | 2019-12-08 04:19:29 | 2019-12-08 04:39:56 |
| 2 | 1 | 1234 Tokyo av NY NY 11218 | 2019-12-08 04:28:09 | 2019-12-08 04:28:09 |
+—-+———+—————————+———————+———————+
2 rows in set (0.00 sec)

updateは、対象のレコードを選択してsave();

read

Route::get('/read', function(){
	$user = User::findOrFail(1);
	return $user->address->name;	
});

delete

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

	$user = User::findOrFail(1);

	$user->address()->delete();
	
});

they share single id of unique record.

$ php artisan make:model Video -m

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

$ php artisan make:model Tag -m

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

$ php artisan make:model Taggable -m

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

class Tag extends Model
{
    //

    public function posts(){

    	return $this->norphByMany('App\Post', 'taggables');
    }

    public function videos(){

    	return $this->norphByMany('App\Video', 'taggables');
    }
}

$php artisan migrate

mysql> insert into videos (name) values (‘the mule’);
Query OK, 1 row affected (0.01 sec)

mysql> insert into videos (name) values (‘coding’);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tags (name) values (‘javascript’);
Query OK, 1 row affected (0.04 sec)

mysql> insert into tags (name) values (‘php’);
Query OK, 1 row affected (0.01 sec)

mysql> insert into taggables (tag_id, taggable_id, taggable_type) values (1, 1,’App\Video’);
Query OK, 1 row affected (0.00 sec)

mysql> insert into taggables (tag_id, taggable_id, taggable_type) values (2, 1,’App\Post’);
Query OK, 1 row affected (0.01 sec)

Route::get('/post/tags', function(){

	$post = Post::find(1);
	foreach($post->tags as $tag){
			echo $tag->name;
	}
});

Route::get('/tag/post', function(){

	$tag = Tag::find(1);
	foreach($tag->posts as $post){
		$echo $post->title;
	}

});

polymorphic は、テーブル関係が複雑なので復習が必要だ

親子関係が複数ある関係
-> 親子関係って？ hasOne, hasManyなどが複数あるって意味？？

$ php artisan make:model Photo -m

Schema::create('photos', function (Blueprint $table) {
            $table->increments('id');
            $table->string('path');
            $table->integer('imageable_id');
            $table->string('imageable_type');
            $table->timestamps();
        });

$ php artisan migrate

class Photo extends Model
{
    //
    public function imageable(){

    	return $this->norphTo();
    }
}

public function photos(){
		return $this->norpMany('App\Photo', 'imageable');
	}

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

	 foreach($user->photos as $photo){

	 	return $photo;

	 }
});

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

	$photo = Photo::findOrFail($id);
	return $photo->imageable;
});

$ php artisan make:model Country -m
$ php artisan make:migration add_country_id_column_to_users –tables=users

migration file: add column country to the users

public function up()
    {
        Schema::table('users', function (Blueprint $table) {
            //
            $table->integer('country_id');
        });
    }

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

*_create_country_table.php

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

insert into countries (name) values (‘canada’);
insert into countries (name) values (‘india’);
insert into countries (name) values (‘german’);

update users set country_id=1 where id=1;
update users set country_id=3 where id=2;

class Country extends Model
{
    //
    public function posts(){

    	return $this->hasManyThrough('App\Post', 'App\User');
    }
}

Route::get('/user/country', function(){

	$country = Country::find(1);

	foreach($country->posts as $post){
		return $post->title;
	} 
});

一つhasManyで連結していれば、hasManyThroughで表示できる

// Accessing to the intermidiate table/pivot

Roleモデル

class Role extends Model
{
    //
    public function users(){
    	return $this->belongsToMany('App\User');
    }
}

Route::get('user/pivot', function(){

		$user = User::find(1);

		foreach($user->roles as $role){
			echo $role->pivot->created_at;
		}
});