laravel 数据库 1 -- 查询(下)
20 March 2017

model curd

class Flight extends Model {}    // table 是 flights

class Flight extends Model {
    protected $table = 'flight'; // 指定 table
    $primaryKey = xxx;           // 默认 id
    $incrementing = false;       // 默认 true

    $connection = 'connection_name';
}

get

$flights = Flight::get();

$flight = App\Flight::find(1);
$flight = App\Flight::where('active', 1)->first();


$model = App\Flight::findOrFail(1);
$model = App\Flight::where('legs', '>', 100)->firstOrFail();

insert

$flight = new Flight;
$flight->name = $request->name;
$flight->save();

update

$flight = App\Flight::find(1);
$flight->name = 'New Flight Name';
$flight->save();

setAttribute, getAttribute

class User extends Model {
    // get
    public function getNameAttribute($name) {
        $this->attributes['name'] = 'the name is ' . $name;
    }

    // set
    public function setAgeAttribute($age) {
        $this->attributes['age'] = $age + 1;
    }
    public function setUserNameAttribute($name) {
        $this->attributes['user_name'] = $name . 'test';
    }
}
// App\User->create(['user_name'=> 'one', 'age' => 12]);

create

protected $guarded  = ['age'];             // 黑名单
# protected $fillable = ['xx', 'xxx'];     // 白名单, 默认全是白名单
$data = [
    'username' => 'abc',
    'age' => 20
];

$user1 = new User();
$user1->fill($data);        // age 被过滤
$user1->save();


$anotherUser = User::create($data);


$user2 = new User();
$user2->username = 'test1';
$user2->age = 21;
$user2->save();             // 不会过滤


$new_user = $user->create(['username'=>'abc']);        // 直接创建, 并且保存到数据库
$new_user = $user->firstOrCreate(['username'=>'abc']); // 先查找, 找不到就创建并且保存到数据库
$new_user = $user->firstOrNew(['username'=>'abc']);    // 先查找, 找到不到创建

hidden (不出现的字段) / visible

protected $hidden = ['password', 'remember_token'];

// visible
protected $visible = ['first_name', 'last_name'];

delete

$flight = App\Flight::find(1);
$flight->delete();

$deletedRows = App\Flight::where('active', 0)->delete();

App\Flight::destroy(1);
App\Flight::destroy([1, 2, 3]);
App\Flight::destroy(1, 2, 3);


// 软删除
use Illuminate\Database\Eloquent\SoftDeletes;  // 1
class User extends Model {
    use SoftDeletes;                           // 2
    protected $dates = ['deleted_at'];         // 3
}

// 默认创建数据库是没有 deleted_at 字段的,通过下面的代码可以创建
Schema::table('users', function ($table) {
    $table->softDeletes();
});


// 判断是否被软删除
if ($user->trashed()) {
    ...
}

// 在查询使用软删除功能的模型时,被 "删除" 的模型数据不会出现在查询结果里,要让已被软删除的模型数据出现在查询结果里,需要使用
$users = DB::table('users')->withTrashed()->where('user_id', 1)->get();

// 只查询被软删除的模型数据
$users = DB::table('users')->onlyTrashed()->where('user_id', 1)->get();


// 恢复被软删除的模型数据
DB::table('users')->restore();
DB::table('users')->withTrashed()->where('id', 1)->restore();


// 如果想要真的从模型数据库删除,使用 forceDelete 方法
DB::table('users')->forceDelete();

分块 chunk

$user->where('user_id', '>', 1)->chunk(2, function ($users) {
    foreach ($users as $user) {
        echo $user->username, ' ', $user->age, '<br />';
    }
});

// 停止 chunk
DB::table('users')->chunk(100, function ($users) {
    ...
    return false;                 # false 表示不再继续分组
});

where

// where
DB::table('users')->where('votes', '>', 100)->get();
DB::table('users')->where('votes', '=', 100)->get();
DB::table('users')->where('votes', 100)->get();
DB::table('users')->where('name', 'like', 'T%')->get();

DB::table('users')->where([
    'con1', 'xxx',
    'con2', 'yyy',
])->get();


// whereRaw
#+BEGIN_SRC php -r
DB::table('user')->whereRaw('user_id > 1 and username = "d"')->get();
DB::table('user')->whereRaw('user_id > ? and username = ?', [1, 'd'])->get();


// where/orWhere
DB::table('users')->where('votes', '>', 100)->where('votes', '<', 300)->get();
DB::table('users')->where('votes', '>', 100)->orWhere('name', 'John')->get();


// wherebetween
DB::table('users')->whereBetween('votes', [1, 100])->get();


// whereNotBetween
DB::table('users')->whereNotBetween('votes', [1, 100])->get();


// whereIn/whereNotIn
DB::table('users')->whereIn('id', [1, 2, 3])->get();
DB::table('users')->whereNotIn('id', [1, 2, 3])->get();


// whereNull/whereNotNull
DB::table('users')->whereNull('updated_at')->get();
DB::table('users')->whereNotNull('updated_at')->get();


// whereDate / whereMonth / whereDay / whereYear
$users = DB::table('users')->whereDate('created_at', '2016-12-31')->get();
$users = DB::table('users')->whereMonth('created_at', '12')->get();
$users = DB::table('users')->whereYear('created_at', '2016')->get();


// whereColumn
$users = DB::table('users')->whereColumn('first_name', 'last_name')->get();
$users = DB::table('users')->whereColumn('updated_at', '>', 'created_at')->get();
$users = DB::table('users')->whereColumn([
    ['first_name', '=', 'last_name'],
    ['updated_at', '>', 'created_at']
])->get();


// where 闭包
DB::table('users')->where('name', '=', 'John')
    ->orWhere(function ($query) {
        $query->where('votes', '>', 100)
            ->where('title', '!=', 'Admin');
    })->get();


// whereExists
DB::table('users')
    ->whereExists(function ($query) {
        $query->select(DB::raw(1))
            ->from('orders')
            ->whereRaw('orders.user_id = users.id');
    })->get();

// select * from users where exists (
//   select 1 from orders where orders.user_id = users.id
// )

join

// join
$users = DB::table('users')
       ->join('contacts', 'users.id', '=', 'contacts.user_id')
       ->join('orders', 'users.id', '=', 'orders.user_id')
       ->select('users.*', 'contacts.phone', 'orders.price')
       ->get();


// left join
$users = DB::table('users')
       ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
       ->get();


// cross join
$users = DB::table('sizes')
       ->crossJoin('colours')
       ->get();


// join 闭包
DB::table('users')
    ->join('contacts', function ($join) {
        $join->on('users.id', '=', 'contacts.user_id')
            ->orOn(..)
            ->where('contacts.user_id', '>', 5);
    })->get();

// 上面的语句先对 contacts 表进行 where 语句过滤之后再和 users 进行 join 操作

union

unionAll 和 union 有同样的使用方法

$first = DB::table('users')->whereNull('first_name');

$users = DB::table('users')
->whereNull('last_name')
->union($first)
->get();

orderBy

DB::table('users')
->orderBy('name1', 'desc')
->orderBy('name2', 'desc')
->get();

skit/take

DB::table('users')->skip(10)->take(5)->get();
DB::table('users')->offset(10)->limit(5)->get();

when

你希望某个值为 true 时才执行查询。例如,如果在传入请求中存在指定的输入值的时候才执行这个 where 语句。你可以使用 when 方法实现:

$role = $request->input('role');

$users = DB::table('users')->when($role, function ($query) use ($role) {
    return $query->where('role_id', $role);
})->get();

也可以传入第三个参数,这个参数也是一个闭包。如果第一个参数的值为 false 时,这个闭包将执行。为了说明如何使用此功能,我们将使用它配置默认排序的查询:

$sortBy = null;

$users = DB::table('users')->when($sortBy, function ($query) use ($sortBy) {
    return $query->orderBy($sortBy);
}, function ($query) {
    return $query->orderBy('name');
})->get();