[TIL] PHP Laravel - Database #3 쿼리 빌더 (Query Builder)

쿼리 빌더

 

쿼리 빌더는 라라벨의 데이터베이스 기능으로 다양한 유형의 데이터베이스와 연결할 수 있음

간결한 플루언트 인터페이스 제공

// 플루언트 인터페이스란 주로 메서드 체이닝을 사용해 필요한 메서드를 호출하는 인터페이스

// 플루언트 인터페이스가 아닌 형테
$users = DB::select(['table' => 'users', 'where' => ['type' => 'donor']]);

// 플루언트 인터페이스
$users = DB::table('users')->where('type','donor')->get();

 

DB 퍼사드 기본 사용법

DB 퍼사드는 '원시' 쿼리를 실행하기 위한 방법, 메서드 체이닝 방법 두 가지를 제공

// 쿼리를 직접 전달하는 방식
DB::statement('drop table users');

// SELECT 쿼리를 직접 전달하고 파라미터를 바인딩 하는 호출 방식
DB::select('select * from contacts where validated = ?',[true]);

// 체이닝 방법을 사용해 데이터 조회
$users = DB::table('users')->get();

// 다른 테이블과 join 구문으로 체이닝 호출
DB::table('users')-> join('contacts',function($join) {
    $join->on('users.id', '=', 'contacts.user_id')->where('contacts.type','donor');
})->get();

 

기본적인 SQL 호출

// **SELECT**
// 기본
$users = DB::select('select * from users');

// 파라미터 지정
$users = DB::select('select * from users where type = ?',[$type]);

// 바인딩 이름 지정
$users = DB::select('select * from users where type = :type',['type' => $type]);

// **INSERT**
DB::insert('insert into contacts (name, email) values(?,?)',[$name,$email]);

// **UPDATE**
DB::update('update contacts set status = ? where id = ?',[$status,$id]);

// **DELETE**
DB::delete('delete from contacts where id = ? ',[$id]);

 

쿼리 빌더 체이닝

쿼리 빌더 기능을 사용하면 메서드를 체이닝 형태로 호출하여 쿼리를 실행할 수 있음

쿼리 결과 제약 메서드

$user = DB::table('users')->where('type', $type)->get();

// 조회결과에 포함될 칼럼 선택
$emails = DB::table('contacts')->select('email','email2 as second_email')->get();
$emails = DB::table('contacts')->select('email')->addSelect('email2 as second_email')->get();

// where() 조건 ( '=' 는 생략가능)
$newContacts = DB::table('contacts')->where('created_at', '>', now()->subDay())->get();

//where 절 구문 여러개
$newVips = DB::table('contacts')->where('vip',true)
    ->where('created_at', '>', now()->subDay());
//또는
$newVips = DB::table('contacts')->where([
        ['vip',true],
        ['created_at', '>', now()->subDay()],
    ]);

// orWhere() or 조건절
$priorityContacts = DB:table('contacts')
    ->where('vip',true)
    ->orWhere('created_at','>',now()->subDay())
    ->get();

// 좀 더 복잡한 or where 절을 만들려면 클로저를 전달
$priorityContacts = DB:table('contacts')
    ->where('vip',true)
    ->orWhere(function ($uqery) {
        $query->where('created_at','>',now()->subDay())->where('trial',false);
    })->get();

// ** where()과 orWhere() 혼합해서 사용할 때 조심해야한다 (쿼리의 and or 중첩등등)

// whereBetween() [whereNotBetween() 은 두 값 사이에 있지 않은 레코드를 반환]
$users = DB::table('users')->whereBetween('id',[6,10])->get();

// whereIn [whereNotIn() 은 포함되어 있지 않은 레코드 반환]
$closeBy = DB::table('contacts')->whereIn('state',['FL','GA','AL'])-get();

// whereNull(), whereNotNull()은 주어진 컬럼이 null인거나 null이 아닌 레코드 반환
$notModifyContacts= DB::table('contacts')->whereNull('update_at')-get();

// whereRaw() 문자열을 이스케이프 처리하지 않고 그대로 where 구문뒤에 추가한다. (인젝션 공격조심)
$goofs = DB::table('contacts')->whereRaw('id = 1234')->get();

// whereExists() 서브 쿼리를 전달해서 하나 이상의 레코드를 반환하는 경우
$commenters = DB::table('users')
    ->whereExists(function($query){
        $query->select('id')->from('comments')->whereRaw('comments.user_id = users.id');
    })->get();

// distinct() 선택한 데이터가 고유할 때만 반환되도록 제한
$lastNames = DB::table('contacts')->select('city')->distinct()->get();

쿼리 결과 변경 메서드

//orderBy(colName, direction) 정렬
$contacts = DB::table('contacts')->orderBy('last_name','asc')->get();

//groupBy(), having(), havingRaw() 결과 그룹화 때 사용 
$populousCities = DB::table('contacts')->groupBy('city')->havingRaw('count(contact_id) > 30')->get();

// skip(), tabke() 페이지 처리시 사용 
// 31~40번째 레코드 반환
$page4 = DB::table('contacts')->skip(30)->take(10)->get();

//latest(colName), oldest(colName) 칼럼명 기준으로 오름차순 내림차순

//inRandomOrder() 임의의 순서로 정렬

조건에 따라 쿼리를 추가하는 메서드

이 메서드는 조건 값에 따라서 쿼리에 제약을 추가할 때 사용

// when() 첫번째 파라미터가 true이면 두 번째 파라미터로 전달된 클로저를 실행, flase이면 3번째 클로저 실행
// $status 가 true면 where 추가
$posts = DB::table('posts')
    ->when($status, function($query) use ($status) {
        return $query->where('status',$status);
    })->get();

//unless() when() 메서드의 반대, 첫번째 조건이 거짓이면 두 번째 파라미터로 전달된 클로저 실행

쿼리를 실행하고 결과를 반환하는 메서드

// get() 쿼리를 실행하고 결과를 반환

// first() firstOrFail() : limit 1 한 것과 같다. first() 결과 없으면 False 반환, firstOrFail() 메서드는 예외 발생
$newestContact = DB::table('contacts')->orderBy('created_at', 'desc')->first();

// find() findOrFail() : 찾고자 하는 데이터의 ID 인자 (False 반환, 예외발생)
$contact = DB::table('contacts')->find(5);

// value(colName) 지정된 컬럼 값만 반환
$newestContactEmail = DB::table('contacts')->orderBy('created_at','desc')->value('email');

// count() 조건에 일치하는 레코드 수 반환
$countVips = DB::table('contacts')->where('vip',true)->count();

// min(colName) max(colName) 주어진 칼럼 값의 최솟값, 최댓값 반환
$highestCost = DB::table('orders')->max('amount');

// sum() avg() 합계, 평균 값 반환
$totalCost = DB::table('orders')->sum('amount');

// dd() dump() : 디버깅시 사용, 생성되는 쿼리와 바인딩 되는 변수 값을 출력한다. dd() 를 사용하는 경우 스크립트를 종료 
DB::table('users')->where('name','SR')->dd();
// | "select * from 'users' where 'name' = ?"
// | array:1 [0 => 'SR']

조인 쿼리

// join() Inner Join
// leftJoin() 메서드를 사용해 left join 구성
$users = DB::table('users')
            ->join('contacts', 'users.id', '=', 'contacts.user_id')            -
            ->select('users.*', 'contacts.phone')
            ->get();

// 복잡한 조인문은 클로저를 전달하는 방식으로 구현
DB::table('users')
        ->join('contacts', function ($join) {
            $join->on('users.id', '=', 'contacts.user_id')
                                    ->orOn('users.id','=','contacts.proxy_user_id');
        })->get();

유니온 쿼리

// union() 메서드나 unionAll 메서드를 사용
$first = DB::table('users')
            ->whereNull('first_name');

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

인서트 쿼리

DB::table('users')->insert(
    ['email' => 'john@example.com', 'votes' => 0]
);

//insertOrIgnore 메소드는 데이터베이스에 레코드를 삽입하는 동안 중복 레코드 오류를 무시
DB::table('users')->insertOrIgnore([
    ['id' => 1, 'email' => 'taylor@example.com'],
    ['id' => 2, 'email' => 'dayle@example.com'],
]);

// insertGetId() 메서드는 자동 생성된 기본 키ID를 반환
$id = DB::table('users')->insertGetId(
    ['email' => 'john@example.com', 'votes' => 0]
);

업데이트 쿼리

// 
$affected = DB::table('users')
              ->where('id', 1)
              ->update(['vip' => true]);

// upsert 문법
DB::table('users')
    ->updateOrInsert(
        ['email' => 'john@example.com', 'name' => 'John'], // 검색해서 값이 없으면 인서트
        ['vip' => true] // 값이 있어서 해당 칼럼만 업데이트
    );

// increment() decrement() 간단하게 값을 증가/감소 
DB::table('contacts')->increment('tokens',5);
DB::table('contacts')->decrement('tokens');

삭제 쿼리

DB::table('users')->delete();
DB::table('users')->where('votes', '>', 100)->delete();

// truncate() 모든 레코드 삭제 후 increment 값을 0으로 변경
DB::table('users')->truncate();

JSON 연산

json 칼럼을 갖는다면 화살표 문법(→)을 사용해 json 구조에 맞게 데이터를 조회하고 수정할 수 있음

// options JSON 칼럼의 isAdmin 속성이 true인 모든 레코드 조회
DB::table('users')->where('options->isAdmin', true)->get();

// options JSON 칼럼의 verified 속성을 true로 변경
DB::table('users')->update(['options->isVerified',true]);

 

트랜잭션

//간단한 트랜잭션
DB::transaction(function() use ($userId, $numVotes) {
    // 실패할 가능성이 있는 쿼리
    DB::table('users')->where('id',$userId)->update(['votes'=>$numVotes]);

    // 실패하면 아래 쿼리는 실행되지 않는다.
    DB::table('votes')->where('user_id', $user_id)->delete();
});

// 더 명시적인 방법
DB::beginTransaction();

// 데이터 베이스 작업 실행
if($badThingsHappend) {
    DB::rollBack();
}

//  다른 데이터 베이스 작업 실행

DB::commit();