プログラム言語 データベースæ“作 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!