02_3_database - hpscript/laravel GitHub Wiki

Raw SQL Query

insert

use Illuminate\Support\Facades\DB;
Route::get('/insert', function(){

	DB::insert('insert into posts(title, content) values(?, ?)', ['php with laravel', 'Laravel is the best thing that happen to PHP']);
});

select

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

	$results = DB::select('select * from posts where id = ?', [1]);

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

});

update

Route::get('update', function(){
	$update = DB::update('update posts set title ="Update tile" where id = ?',[1]);

	return $update;
});

delete

Route::get('delete', function(){
	$delete = DB::delete('delete from posts where id = ?', [2]);
	return $delete;
});

Eloquent

get()

use App\Post;
Route::get('/findwhere', function(){
	$posts = Post::where('id', 1)->orderBy('id', 'desc')->take(1)->get();
	return  $posts;

});

FindOrFail()

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

	$posts = Post::findOrFail(4);
	return $posts;

});

insert

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();
});

update

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();
});

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

MassAssignmentException

modelでfillableに配列としてカラム名を書くと、create methodも書ける

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

	Post::create(['title'=>'php create method', 'content'=>'Wow I\'m learning a lot']);
	
});

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

delete

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

	$post = Post::find(5);

	$post->delete();
});

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

論理削除

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)

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;

});

softdeleteを元に戻す

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

softdeleteの削除:forceDelete

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

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

polymorphic

$ 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;

	 }
});