2020/12/26 / 最終更新日時 : 2021/01/28 米良太 共通

プログラム言語 データベースæ“作 ORM

データå–å¾—

データ型
protected $modelClass = ‘Tests’;
$tests = $this–>Tests–>find()–>all();

モデルクラスã®ã‚¤ãƒ³ã‚¹ã‚¿ãƒ³ã‚¹ã‚’è¦ç´ ã¨ã™ã‚‹ã‚³ãƒ¬ã‚¯ã‚·ãƒ§ãƒ³ã‚’å–å¾—

use App\Models\Test;
$tests = Test::all();

モデルクラスã®ã‚¤ãƒ³ã‚¹ã‚¿ãƒ³ã‚¹ã‚’è¦ç´ ã¨ã™ã‚‹ã‚³ãƒ¬ã‚¯ã‚·ãƒ§ãƒ³ã‚’å–å¾—

$tests = DB::table('テーブル物ç†å')–>get();

é…列をå–å¾—

FROM
use App\Models\Test;
$tests = Test::all();

別å
$tests = Test::from('tests as t')–>all();

$tests = DB::table('テーブル物ç†å as t')–>get();

別å
$tests = DB::table('テーブル物ç†å')–>get();

全件
protected $modelClass = ‘Tests’;
$tests = $this–>Tests–>find()–>all();

$testsTable = $this–>getTableLocator()–>get('Tests');
$tests = $testsTable–>find()–>all();

use App\Models\Test;
$tests = Test::all();
$tests = DB::table('テーブル物ç†å')–>get();
先頭
$tests = $this–>Tests–>find()
　–>first();
$tests = Test::first();
Eloquentã¨åŒã˜
末尾
$tests = $this–>Tests–>find()
　–>last();
$tests = Test::last();
Eloquentã¨åŒã˜
COUNT
$tests = $this–>Tests–>find()
　–>count();
$tests = Test::count();

　çµåˆå…ˆãƒ†ãƒ¼ãƒ–ルã®ãƒ¬ã‚³ãƒ¼ãƒ‰æ•°
　–>withCount('モデルクラスå');

$tests = Test::table(〜)–>count();
SUM
$query = $this–>Tests
　–>find()
　–>where([
　　〜
　]);

$test = $query–>select([
　'定義å' => $query–>func()–>sum('列å'),
])
–>first();

$tests = Test::get->sum(列å);
値ã®ã¿
$tests = $this–>Tests–>find()
　–>select(['列å'])
　–>enableHydration(false)
　–>all()
　–>extract('列å')
　–>toList();

çµæžœ $tests = [1, 2, 3, 4, 5];

$tests = Test::pluck('列å');

çµæžœ $tests = [1, 2, 3, 4, 5];

Eloquentã¨åŒã˜
SELECT
$tests = Test::select('列å')
　–>addSelect(〜);
Eloquentã¨åŒã˜

WHERE

=
$tests = $this–>Tests–>find()
　–>where([
　　'列å' => 100,
　　'列å' => XXX, Andæ¡ä»¶
　]);
　–>andWhere([
　　'列å' => 100,
　]);
　–>orWhere([
　　'列å' => 100,
　]);
　–>all();
$tests = Test::where('列å', 100)
　–>where('列å', XXX) Andæ¡ä»¶
　–>orWhere('列å', XXX) Oræ¡ä»¶
　–>get();

$tests = Test::whereRaw('列å = 100')–>get();

$tests = DB::table('テーブル物ç†å')
　–>where('列å', 100)
　–>where('列å', XXX) Andæ¡ä»¶
　–>orWhere('列å', XXX) Oræ¡ä»¶
　–>get();

$tests = DB::table('テーブル物ç†å')
　–>whereRaw('列å = 100')
　–>get();

IN
$tests = $this–>Tests–>find()
　–>where([
　　'列å IN' => [100, 200, 300],
　]);
　–>all();
$tests = Test::whereIn('列å', [100, 200, 300])
　–>get();
$tests = DB::table('テーブル物ç†å')
　–>whereIn('列å', [100, 200, 300])
　–>get();
BETWEEN
$tests = $this–>Tests–>find()
　–>where(function ($exp) {
　　$timestamp = FrozenTime::now()->subDay(1);

　　return $exp->between(
　　　'Tests.created',
　　　$timestamp->startOfDay()->toDateTimeString(),
　　　$timestamp->endOfDay()->toDateTimeString()
　　);
　})
　–>all();

$tests = Test::whereBetween('列å', [100, 200])
　–>get();
$tests = DB::table('テーブル物ç†å')
　–>whereBetween('列å', [100, 200])
　–>get();
NULL判定
$tests = Test::whereNull(列å)
->orWhereNull(列å); ORæ¡ä»¶
->whereNotNull(列å); ANDæ¡ä»¶
->orWhereNotNull(列å); ORæ¡ä»¶
->get();
Eloquentã¨åŒã˜
A AND (B OR C)
$tests = Test::where(function($query){
　return $query
　　->where(〜)
　　->orWhere(〜);
})
->get();
Eloquentã¨åŒã˜

JOIN

INNER JOIN
リレーション定義済ã®å ´åˆ
$tests = $this–>Tests
　–>contain([
　　'çµåˆå…ˆãƒ†ãƒ¼ãƒ–ルクラスå1',
　　'çµåˆå…ˆãƒ†ãƒ¼ãƒ–ルクラスå2',
　　çµåˆå…ˆã¨æ›´ã«çµåˆ
　　'çµåˆå…ˆãƒ†ãƒ¼ãƒ–ルクラスå3' =;> [
　　　'çµåˆå…ˆãƒ†ãƒ¼ãƒ–ルクラスå4',
　　],
　　çµåˆæ¡ä»¶æŒ‡å®š
　　'çµåˆå…ˆãƒ†ãƒ¼ãƒ–ルクラスå5' =;> function (Query $query) use ($test) {
　　　return $query->where(['çµåˆå…ˆãƒ†ãƒ¼ãƒ–ルクラスå5.列å' =;> $test->id]);
　　},
　])
　–>find()–>all();

çµæžœ
foreach($tests as $t){
　$t–>çµåˆå…ˆãƒ†ãƒ¼ãƒ–ルå–>〜;
}

çµåˆå…ˆãƒ†ãƒ¼ãƒ–ルã®ãƒ‡ãƒ¼ã‚¿ã¯å–å¾—ã—ãªã„å ´åˆ1
$tests = $this–>Tests
　–>innerJoinWith('çµåˆå…ˆãƒ†ãƒ¼ãƒ–ルクラスå6')
　–>innerJoinWith('çµåˆå…ˆãƒ†ãƒ¼ãƒ–ルクラスå7', function ($q) use ($test) {
　　return $q
　　　–>where(['çµåˆå…ˆãƒ†ãƒ¼ãƒ–ルクラスå7.列å' =;> $test->id]);
　})
　–>find()–>all();

çµåˆå…ˆãƒ†ãƒ¼ãƒ–ルã®ãƒ‡ãƒ¼ã‚¿ã¯å–å¾—ã—ãªã„å ´åˆ2
$tests = $this–>Tests
　–>join()
　　'table' => 'テーブル物ç†å',
　　'alias' => 'テーブル別å',
　　'type' => 'INNER',
　　'conditions' => 'テーブル別å.列å = çµåˆå…ƒãƒ†ãƒ¼ãƒ–ルå.列å',
　–>find()–>all();

use App\Models\Test;
$tests = Test::with('çµåˆå…ˆãƒ†ãƒ¼ãƒ–ルクラスå')–>all();

$tests = Test::with(['çµåˆå…ˆãƒ†ãƒ¼ãƒ–ルクラスå' =;> function($query){
　$query->where('çµåˆå…ˆãƒ†ãƒ¼ãƒ–ル列å', ‘LIKE’, ‘%〜%’);
}])->get(),

↓ã¯å¯èƒ½ã ãŒEager Loadingを使用ã—ãªã„為ã«ãƒ¡ãƒªãƒƒãƒˆç„¡ã—
$tests = Test::join(
　'çµåˆå…ˆãƒ†ãƒ¼ãƒ–ル物ç†å',
　'çµåˆå…ˆãƒ†ãƒ¼ãƒ–ル物ç†å.列å', '=', 'テーブル物ç†å.列å'
)
–>get();

çµæžœ
foreach($tests as $t){
　$t–>çµåˆå…ˆãƒ†ãƒ¼ãƒ–ルクラスå–>〜;
}

$tests = DB::table('テーブル物ç†å')
　–>join(
　　'çµåˆå…ˆãƒ†ãƒ¼ãƒ–ル物ç†å',
　　'çµåˆå…ˆãƒ†ãƒ¼ãƒ–ル物ç†å.列å', '=', 'テーブル物ç†å.列å'
　)
　–>get();

çµæžœ
foreach($tests as $t){
　$t–>çµåˆå…ˆãƒ†ãƒ¼ãƒ–ルクラスå–>〜;
}

LEFT JOIN
リレーション定義済ã®å ´åˆ
$tests = $this–>Tests
　–>contain([
　　〜
　])
　–>find()–>all();

çµåˆå…ˆãƒ†ãƒ¼ãƒ–ルã®ãƒ‡ãƒ¼ã‚¿ã¯å–å¾—ã—ãªã„å ´åˆ1
$tests = $this–>Tests
　–>leftJoinWith('çµåˆå…ˆãƒ†ãƒ¼ãƒ–ルクラスå6')
　–>leftJoinWith('çµåˆå…ˆãƒ†ãƒ¼ãƒ–ルクラスå7', function ($q) use ($test) {
　　return $q
　　　–>where(['çµåˆå…ˆãƒ†ãƒ¼ãƒ–ルクラスå7.列å' =;> $test->id]);
　})
　–>find()–>all();

çµåˆå…ˆãƒ†ãƒ¼ãƒ–ルã®ãƒ‡ãƒ¼ã‚¿ã¯å–å¾—ã—ãªã„å ´åˆ2
$tests = $this–>Tests
　–>join()
　　'table' => 'テーブル物ç†å',
　　'alias' => 'テーブル別å',
　　'type' => 'LEFT',
　　'conditions' => 'テーブル別å.列å = çµåˆå…ƒãƒ†ãƒ¼ãƒ–ルå.列å',
　–>find()–>all();

çµåˆå…ˆãƒ†ãƒ¼ãƒ–ルã®ãƒ‡ãƒ¼ã‚¿ã‚‚å–å¾—ã™ã‚‹å ´åˆ
$tests = $this–>Tests
　–>matching('çµåˆå…ˆãƒ†ãƒ¼ãƒ–ルクラスå8')
　–>matching('çµåˆå…ˆãƒ†ãƒ¼ãƒ–ルクラスå8', function ($q) use ($test) {
　　return $q
　　　–>where(['çµåˆå…ˆãƒ†ãƒ¼ãƒ–ルクラスå8.列å' =;> $test->id]);
　})
　–>find()–>all();

çµæžœ
foreach($tests as $t){
　$t–>_matchingData['çµåˆå…ˆãƒ†ãƒ¼ãƒ–ルクラスå']–>〜;
}

use App\Models\Test;
$tests = Test::leftJoin(
　　'çµåˆå…ˆãƒ†ãƒ¼ãƒ–ル物ç†å',
　　'çµåˆå…ˆãƒ†ãƒ¼ãƒ–ル物ç†å.列å', '=', 'テーブル物ç†å.列å'
　)
　–>get();

çµæžœ
foreach($tests as $t){
　$t–>çµåˆå…ˆãƒ†ãƒ¼ãƒ–ルクラスå–>〜;
}

$tests = DB::table('テーブル物ç†å')
　–>leftJoin(
　　'çµåˆå…ˆãƒ†ãƒ¼ãƒ–ル物ç†å',
　　'çµåˆå…ˆãƒ†ãƒ¼ãƒ–ル物ç†å.列å', '=', 'テーブル物ç†å.列å'
　)
　–>get();

çµæžœ
foreach($tests as $t){
　$t–>çµåˆå…ˆãƒ†ãƒ¼ãƒ–ルクラスå–>〜;
}

has/doesnt
use App\Models\Test;
$tests = Test::has('çµåˆå…ˆãƒ†ãƒ¼ãƒ–ル物ç†å')
　–>orHas('çµåˆå…ˆãƒ†ãƒ¼ãƒ–ル物ç†å')
　–>whereHas(function($query){
　　return $query–>where(〜);
　})
　–>get();

$tests = Test::doesntHave('çµåˆå…ˆãƒ†ãƒ¼ãƒ–ル物ç†å')
　–>orDoesntHave('çµåˆå…ˆãƒ†ãƒ¼ãƒ–ル物ç†å')
　–>whereDoesntHave(function($query){
　　return $query–>where(〜);
　})
　–>get();

ORDER BY

$tests = Test::orderBy(列å, 'ASC')
　->orderBy(列å, 'DESC')
　->get();
Eloquentã¨åŒã˜

サブクエリ

SELECTå¥
use App\Models\Test;
$tasks = Test::leftJoin(〜)
　->leftJoin(〜)
　->select()
　->selectSub(
　　 use App\Models\TestMany;
　　TestMany::selectRaw('count(*)')
　　　->whereRaw('副å•ã„åˆã‚ã›å…ˆãƒ†ãƒ¼ãƒ–ルå.列å = 〜.id')
　　, 'SubCount')
　->get();

※with(EargerLoading)を用ã„ã¦ã®ã‚µãƒ–クエリã¯èª¿æŸ»ä¸­

JOIN
use App\Models\TestMany;
$subQuery = TestMany::〜;

use App\Models\Test;
$tasks = Test::leftJoin(
　　DB::raw("(${subQuery}) AS SubTable", 'SubTable.列å', '=', 'tests.id')
　)
　->get();

Follow me!

カテゴリー
共通
ã‚¿ã‚°
共通

å‰ã®è¨˜äº‹

プログラム言語 データベースæ“作
2020/12/24
Laravel

次ã®è¨˜äº‹

Laravel サービス
2020/12/31