Php/laravel/laravel5/orm

laravelのorm

Eloquent ORMをつかう

モデル自動生成

php artisan make:model Article

vi app/Article.php

<?php namespace App;
use Illuminate\Database\Eloquent\Model;
class Article extends Model
{
    protected $primaryKey = 'id';
    protected $table = 'articles';
    public $timestamps = true;
}

table作成

CREATE TABLE `articles` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `created_at` datetime NOT NULL,
 `updated_at` datetime NOT NULL,
 `title` varchar(63) DEFAULT NULL,
 `body` text NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

timestamps設定について

1を設定するとcreated_atとupdated_atに挿入・更新時、自動で日付が入る

| id | created_at          | updated_at          | title | body |
+----+---------------------+---------------------+-------+------+
|  1 | 2016-02-18 22:40:14 | 2016-02-18 22:40:14 | net   |      |

全取得

vi app/Http/Controllers/ArticleController.php

$articles = \App\Article::all();
foreach ($articles as $article) {
    echo $article->id."\n";
    echo $article->title."\n";
}

1カラム取得

column1を取得したい場合

\App\Article::first()->column1;

取得

$article = \App\Article::find(2); // primary keyを引数に入れる // App\Article Objectが返ってくる
echo $article->title;

条件をつけて1行取得

$article = \App\Article::where('active', 1)->first();

条件をつけて複数行取得

$articles = \App\Article::where('active', 1)->get();

limitを指定して取得

$articles = \App\Article::where('active', 1)->take(10)->get();

順位

$articles = \App\Article::where('active', 1)->orderBy('updated_at')->get();

不等号

User::where('votes', '>', 100)

like

User::where('name', 'like', "%".$q."%")

条件をphpで追加する

$query = User::query();
$query->where('name', 'taro')
$query->where('age', 10)
$users = $query->get();

onWhereで(a or b) and cを作る

$query = User::query();
$query->where('age', 10)
$query->where(function($query) use($word) {
    $query->where('introduction', 'like', '%'.$word.'%')
   orWhere('hobbies', 'like', '%'.$word.'%')
});

sql表示

$query = User::query();
echo $query->toSql();

挿入

$test = \App\Article::create(
    ['user_id' => 6]
);

vi app/Article.php

class Article extends Model
{
   // guardedがないと(Illuminate\Database\Eloquent\MassAssignmentException)エラーになる
   protected $guarded = array('id');
}

挿入、その2

ただこれだとテストしずらくなる

$article = new \App\Article();
$article->title = 'net';
$article->save();

挿入して更新

こうするとprimary_keyなどが自動で入り後はデフォルトで挿入された後、updateが走る

$article = \App\Article::create();
$article->title = 'net';
$article->save();

更新

$article = \App\Article::find(2);
$article->title = 'it';
$article->save();

削除

$article = \App\Article::find(2);
$article->delete();

件数

\App\Article::where('id', $id)->count();

in

Article::whereIn('id', [10,11,12,13])

A and (B or C)

User::where('sex' , 'male')
　　->where(function($query){
　　　　$query->where('age', '<', 10)
　　　　　　->orWhere('score', '>', 70);
　　})
　　->get();

参考：http://mask.hatenadiary.com/entry/2014/11/22/151730

User:::where('match_id', $match_id)
                ->whereRaw('(user_id = ? OR to_user_id = ?)', [$user_id, $to_user_id])
                ->get();

WhereRawを使ってエスケープ

// 30日経過したものだけを1行取り出す
$articles = \App\Article::whereRaw('updated_at < now() - INTERVAL 30 DAY')->orderBy('updated_at')->first()

スコープ

// 挿入
public static function insertName($name, $url)
{
    $class = get_class();
    $row = new $class;
    $row->url = $url;
    $row->name = $name;
    $row->save();
}
\App\Test::insertName('name1', 'url1');

// 取得
public static function findRowByName($name)
{
    return self::where('name', $name)->first();
}
\App\Test::findRowByName('name1');

公式

https://laravel.com/docs/4.2/eloquent