プãƒã‚°ãƒ©ãƒ 言語 データベースæ“作 ORM
目次
データå–å¾—
データ型
$tests = $this–>Tests–>find()–>all();
モデルクラスã®ã‚¤ãƒ³ã‚¹ã‚¿ãƒ³ã‚¹ã‚’è¦ç´ ã¨ã™ã‚‹ã‚³ãƒ¬ã‚¯ã‚·ãƒ§ãƒ³ã‚’å–å¾—
$tests = Test::all();
モデルクラスã®ã‚¤ãƒ³ã‚¹ã‚¿ãƒ³ã‚¹ã‚’è¦ç´ ã¨ã™ã‚‹ã‚³ãƒ¬ã‚¯ã‚·ãƒ§ãƒ³ã‚’å–å¾—
é…列をå–å¾—
FROM
$tests = Test::all();
別å
$tests = Test::from('tests as t')–>all();
別å
$tests = DB::table('テーブル物ç†å')–>get();
全件
$tests = $this–>Tests–>find()–>all();
$testsTable = $this–>getTableLocator()–>get('Tests');
$tests = $testsTable–>find()–>all();
$tests = Test::all();
å…ˆé
 –>first();
末尾
 –>last();
COUNT
 –>count();
 çµåˆå…ˆãƒ†ãƒ¼ãƒ–ルã®ãƒ¬ã‚³ãƒ¼ãƒ‰æ•°
 –>withCount('モデルクラスå');
SUM
 –>find()
 –>where([
  〜
 ]);
$test = $query–>select([
 '定義å' => $query–>func()–>sum('列å'),
])
–>first();
値ã®ã¿
 –>select(['列å'])
 –>enableHydration(false)
 –>all()
 –>extract('列å')
 –>toList();
çµæžœ $tests = [1, 2, 3, 4, 5];
çµæžœ $tests = [1, 2, 3, 4, 5];
SELECT
 –>addSelect(〜);
WHERE
=
 –>where([
  '列å' => 100,
  '列å' => XXX, Andæ¡ä»¶
 ]);
 –>andWhere([
  '列å' => 100,
 ]);
 –>orWhere([
  '列å' => 100,
 ]);
 –>all();
 –>where('列å', XXX) Andæ¡ä»¶
 –>orWhere('列å', XXX) Oræ¡ä»¶
 –>get();
$tests = Test::whereRaw('列å = 100')–>get();
 –>where('列å', 100)
 –>where('列å', XXX) Andæ¡ä»¶
 –>orWhere('列å', XXX) Oræ¡ä»¶
 –>get();
$tests = DB::table('テーブル物ç†å')
 –>whereRaw('列å = 100')
 –>get();
IN
 –>where([
  '列å IN' => [100, 200, 300],
 ]);
 –>all();
 –>get();
 –>whereIn('列å', [100, 200, 300])
 –>get();
BETWEEN
 –>where(function ($exp) {
  $timestamp = FrozenTime::now()->subDay(1);
  return $exp->between(
   'Tests.created',
   $timestamp->startOfDay()->toDateTimeString(),
   $timestamp->endOfDay()->toDateTimeString()
  );
 })
 –>all();
 –>get();
 –>whereBetween('列å', [100, 200])
 –>get();
NULL判定
->orWhereNull(列å); ORæ¡ä»¶
->whereNotNull(列å); ANDæ¡ä»¶
->orWhereNotNull(列å); ORæ¡ä»¶
->get();
A AND (B OR C)
 return $query
  ->where(〜)
  ->orWhere(〜);
})
->get();
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();
$tests = Test::with('çµåˆå…ˆãƒ†ãƒ¼ãƒ–ルクラスå')–>all();
$tests = Test::with(['çµåˆå…ˆãƒ†ãƒ¼ãƒ–ルクラスå' =;> function($query){
 $query->where('çµåˆå…ˆãƒ†ãƒ¼ãƒ–ル列å', ‘LIKE’, ‘%〜%’);
}])->get(),
↓ã¯å¯èƒ½ã ãŒEager Loadingを使用ã—ãªã„為ã«ãƒ¡ãƒªãƒƒãƒˆç„¡ã—
$tests = Test::join(
 'çµåˆå…ˆãƒ†ãƒ¼ãƒ–ル物ç†å',
 'çµåˆå…ˆãƒ†ãƒ¼ãƒ–ル物ç†å.列å', '=', 'テーブル物ç†å.列å'
)
–>get();
çµæžœ
foreach($tests as $t){
 $t–>çµåˆå…ˆãƒ†ãƒ¼ãƒ–ルクラスå–>〜;
}
 –>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['çµåˆå…ˆãƒ†ãƒ¼ãƒ–ルクラスå']–>〜;
}
$tests = Test::leftJoin(
  'çµåˆå…ˆãƒ†ãƒ¼ãƒ–ル物ç†å',
  'çµåˆå…ˆãƒ†ãƒ¼ãƒ–ル物ç†å.列å', '=', 'テーブル物ç†å.列å'
 )
 –>get();
çµæžœ
foreach($tests as $t){
 $t–>çµåˆå…ˆãƒ†ãƒ¼ãƒ–ルクラスå–>〜;
}
 –>leftJoin(
  'çµåˆå…ˆãƒ†ãƒ¼ãƒ–ル物ç†å',
  'çµåˆå…ˆãƒ†ãƒ¼ãƒ–ル物ç†å.列å', '=', 'テーブル物ç†å.列å'
 )
 –>get();
çµæžœ
foreach($tests as $t){
 $t–>çµåˆå…ˆãƒ†ãƒ¼ãƒ–ルクラスå–>〜;
}
has/doesnt
$tests = Test::has('çµåˆå…ˆãƒ†ãƒ¼ãƒ–ル物ç†å')
 –>orHas('çµåˆå…ˆãƒ†ãƒ¼ãƒ–ル物ç†å')
 –>whereHas(function($query){
  return $query–>where(〜);
 })
 –>get();
$tests = Test::doesntHave('çµåˆå…ˆãƒ†ãƒ¼ãƒ–ル物ç†å')
 –>orDoesntHave('çµåˆå…ˆãƒ†ãƒ¼ãƒ–ル物ç†å')
 –>whereDoesntHave(function($query){
  return $query–>where(〜);
 })
 –>get();
ORDER BY
 ->orderBy(列å, 'DESC')
 ->get();
サブクエリ
SELECTå¥
$tasks = Test::leftJoin(〜)
 ->leftJoin(〜)
 ->select()
 ->selectSub(
   use App\Models\TestMany;
  TestMany::selectRaw('count(*)')
   ->whereRaw('副å•ã„åˆã‚ã›å…ˆãƒ†ãƒ¼ãƒ–ルå.列å = 〜.id')
  , 'SubCount')
 ->get();
※with(EargerLoading)を用ã„ã¦ã®ã‚µãƒ–クエリã¯èª¿æŸ»ä¸
JOIN
$subQuery = TestMany::〜;
use App\Models\Test;
$tasks = Test::leftJoin(
  DB::raw("(${subQuery}) AS SubTable", 'SubTable.列å', '=', 'tests.id')
 )
 ->get();