쿼리 빌더 - Knowledge of Laravel

쿼리 빌더란

Laravel의 쿼리 빌더는 데이터베이스 쿼리를 유창한 인터페이스로 구축·실행하는 구조입니다. DB 파사드의 table() 메서드로부터 시작해, 메서드 체인으로 쿼리를 조립합니다.

use Illuminate\Support\Facades\DB;

$users = DB::table('users')->get();

내부에서는 PDO 파라미터 바인딩을 사용하고 있기 때문에, SQL 인젝션 대책이 자동으로 이루어집니다.

쿼리 빌더는 Laravel이 지원하는 모든 데이터베이스(MySQL, MariaDB, PostgreSQL, SQLite, SQL Server)에서 동작합니다. 데이터베이스를 전환해도 같은 코드를 사용할 수 있습니다.

Eloquent와의 구분 사용

상황	권장
모델과 릴레이션이 필요	Eloquent
복잡한 집계나 리포트	쿼리 빌더
성능이 중요한 대량 데이터 처리	쿼리 빌더
기존 테이블에 대한 단순한 조작	쿼리 빌더
마이그레이션이나 시더 내의 처리	쿼리 빌더

데이터 취득

전건 취득

$users = DB::table('users')->get();

foreach ($users as $user) {
    echo $user->name;
}

get()은 Illuminate\Support\Collection을 반환합니다. 각 레코드는 PHP의 stdClass 오브젝트입니다.

1건 취득

// 최초 1건을 취득 (찾지 못하면 null)
$user = DB::table('users')->where('name', '홍길동')->first();

// 찾지 못하면 예외를 던진다 (404 응답을 자동 반환)
$user = DB::table('users')->where('name', '홍길동')->firstOrFail();

// 특정 컬럼의 값만 취득
$email = DB::table('users')->where('name', '홍길동')->value('email');

// ID로 취득
$user = DB::table('users')->find(3);

특정 컬럼의 값을 리스트로 취득

// email 컬럼 값의 컬렉션
$emails = DB::table('users')->pluck('email');

// name을 키, email을 값으로 하는 연관 컬렉션
$emailByName = DB::table('users')->pluck('email', 'name');

대량 데이터의 분할 처리

use Illuminate\Support\Collection;

// 100건씩 처리
DB::table('users')->orderBy('id')->chunk(100, function (Collection $users) {
    foreach ($users as $user) {
        // 처리...
    }
});

// 업데이트하면서 청크 처리하는 경우는 chunkById를 사용
DB::table('users')->where('active', false)
    ->chunkById(100, function (Collection $users) {
        foreach ($users as $user) {
            DB::table('users')
                ->where('id', $user->id)
                ->update(['active' => true]);
        }
    });

청크 처리 중에 레코드를 업데이트·삭제하는 경우, chunk()가 아닌 chunkById()를 사용해 주세요. chunk()에서는 레코드의 어긋남이 생길 수 있습니다.

스트리밍 (LazyCollection)

DB::table('users')->orderBy('id')->lazy()->each(function (object $user) {
    // 1건씩 처리
});

집계

$count   = DB::table('users')->count();
$maxAge  = DB::table('users')->max('age');
$minAge  = DB::table('users')->min('age');
$avgAge  = DB::table('users')->avg('age');
$total   = DB::table('orders')->sum('amount');

// 조건부 집계
$avgPremium = DB::table('orders')
    ->where('plan', 'premium')
    ->avg('amount');

레코드의 존재 확인

if (DB::table('orders')->where('finalized', 1)->exists()) {
    // 레코드가 존재
}

if (DB::table('orders')->where('finalized', 1)->doesntExist()) {
    // 레코드가 존재하지 않음
}

SELECT 절

// 취득할 컬럼을 지정
$users = DB::table('users')
    ->select('name', 'email as user_email')
    ->get();

// 중복을 제외
$users = DB::table('users')->distinct()->get();

// 나중에 컬럼을 추가
$query = DB::table('users')->select('name');
$users = $query->addSelect('age')->get();

WHERE 절

기본적인 조건

// 등가 조건 (=는 생략 가능)
$users = DB::table('users')->where('votes', 100)->get();

// 비교 연산자를 지정
$users = DB::table('users')->where('votes', '>=', 100)->get();
$users = DB::table('users')->where('name', 'like', '홍%')->get();

// 여러 조건 (AND)
$users = DB::table('users')
    ->where('status', 'active')
    ->where('age', '>', 20)
    ->get();

// OR 조건
$users = DB::table('users')
    ->where('votes', '>', 100)
    ->orWhere('name', '홍길동')
    ->get();

조건의 그룹화

use Illuminate\Database\Query\Builder;

// OR 조건을 그룹화하여 AND와 조합
$users = DB::table('users')
    ->where('active', true)
    ->where(function (Builder $query) {
        $query->where('role', 'admin')
              ->orWhere('role', 'moderator');
    })
    ->get();
// WHERE active = 1 AND (role = 'admin' OR role = 'moderator')

whereIn / whereBetween / whereNull

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

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

// BETWEEN 절
$users = DB::table('users')
    ->whereBetween('age', [20, 40])
    ->get();

// NULL 판정
$users = DB::table('users')->whereNull('deleted_at')->get();
$users = DB::table('users')->whereNotNull('email_verified_at')->get();

whereLike (패턴 매치)

// 기본적으로 대문자 소문자를 구별하지 않음
$users = DB::table('users')
    ->whereLike('name', '%홍%')
    ->get();

// 대문자 소문자를 구별
$users = DB::table('users')
    ->whereLike('name', '%Hong%', caseSensitive: true)
    ->get();

whereAny / whereAll (여러 컬럼에 대한 동일 조건)

// 어느 하나의 컬럼이 LIKE 조건에 매치
$users = DB::table('users')
    ->where('active', true)
    ->whereAny(['name', 'email', 'bio'], 'like', '%Laravel%')
    ->get();

// 모든 컬럼이 LIKE 조건에 매치
$posts = DB::table('posts')
    ->whereAll(['title', 'content'], 'like', '%Laravel%')
    ->get();

whereNullSafeEquals (NULL 안전한 등가 비교)

whereNullSafeEquals와 orWhereNullSafeEquals는, 컬럼의 값을 지정한 값과 비교할 때 두 개의 NULL 값을 동등한 것으로 간주하는 비교를 수행합니다. 통상의 = 연산자로는 NULL = NULL은 false가 되지만, whereNullSafeEquals에서는 NULL끼리를 동등하다고 판정합니다. MySQL의 <=> 연산자, PostgreSQL의 IS NOT DISTINCT FROM에 대응합니다.

$lastLoginIp = $request->input('last_login_ip');

// $lastLoginIp가 null인 경우에도 last_login_ip IS NULL의 사용자를 취득할 수 있음
$users = DB::table('users')
    ->whereNullSafeEquals('last_login_ip', $lastLoginIp)
    ->get();

통상의 where('column', null)은 WHERE column IS NULL로 변환되지만, whereNullSafeEquals('column', $value)는 바인딩된 값이 null이든 null이 아니든 일관되게 동작합니다. 사용자 입력값이 null이 될 수 있는 경우에 특히 편리합니다.

JOIN

// INNER JOIN
$users = DB::table('users')
    ->join('orders', 'users.id', '=', 'orders.user_id')
    ->select('users.name', 'orders.amount')
    ->get();

// LEFT JOIN
$users = DB::table('users')
    ->leftJoin('orders', 'users.id', '=', 'orders.user_id')
    ->get();

// 여러 테이블의 JOIN
$users = DB::table('users')
    ->join('contacts', 'users.id', '=', 'contacts.user_id')
    ->join('orders', 'users.id', '=', 'orders.user_id')
    ->select('users.*', 'contacts.phone', 'orders.amount')
    ->get();

서브 쿼리 JOIN

// 서브 쿼리를 사용한 JOIN
$latestOrders = DB::table('orders')
    ->select('user_id', DB::raw('MAX(created_at) as last_order_at'))
    ->groupBy('user_id');

$users = DB::table('users')
    ->joinSub($latestOrders, 'latest_orders', function ($join) {
        $join->on('users.id', '=', 'latest_orders.user_id');
    })
    ->get();

정렬·그룹화·리미트

// 정렬
$users = DB::table('users')
    ->orderBy('name', 'asc')
    ->get();

// 여러 컬럼으로 정렬
$users = DB::table('users')
    ->orderBy('last_name')
    ->orderBy('first_name', 'desc')
    ->get();

// 랜덤 순
$users = DB::table('users')->inRandomOrder()->get();

// 그룹화
$orders = DB::table('orders')
    ->select('status', DB::raw('COUNT(*) as count'))
    ->groupBy('status')
    ->get();

// HAVING 절
$orders = DB::table('orders')
    ->select('user_id', DB::raw('SUM(amount) as total'))
    ->groupBy('user_id')
    ->having('total', '>', 10000)
    ->get();

// 리미트와 오프셋
$users = DB::table('users')
    ->skip(10)   // OFFSET
    ->take(5)    // LIMIT
    ->get();

서브 쿼리

// WHERE 절의 서브 쿼리
$activeUsers = DB::table('users')->select('id')->where('is_active', 1);

$comments = DB::table('comments')
    ->whereIn('user_id', $activeUsers)
    ->get();

// SELECT 절의 서브 쿼리
$users = DB::table('users')
    ->select('name')
    ->selectSub(function ($query) {
        $query->from('orders')
              ->selectRaw('COUNT(*)')
              ->whereColumn('orders.user_id', 'users.id');
    }, 'order_count')
    ->get();

벡터 유사도 검색

임베딩 벡터를 저장한 컬럼에 대해 whereVectorSimilarTo를 사용하면 지정한 벡터에 가까운 레코드만 검색할 수 있습니다. 벡터 컬럼은 마이그레이션에서 vector 형으로 정의합니다(마이그레이션의 벡터 컬럼).

use Illuminate\Support\Facades\DB;

$embedding = [0.12, -0.34, 0.56];

$documents = DB::table('documents')
    ->whereVectorSimilarTo('embedding', $embedding, minSimilarity: 0.4)
    ->limit(10)
    ->get();

whereVectorSimilarTo는 유사도가 높은 순으로 정렬합니다. 최소 유사도 지정이 필요 없고 거리 기반 정렬 순서만 명시하고 싶다면 orderByVectorDistance를 사용할 수 있습니다. 반환하는 컬럼에 거리 값을 포함하고 싶을 때는 selectVectorDistance로 별칭을 지정할 수 있습니다.

$documents = DB::table('documents')
    ->select('id', 'title')
    ->selectVectorDistance('embedding', $embedding, as: 'distance')
    ->orderByVectorDistance('embedding', $embedding)
    ->limit(10)
    ->get();

이 메서드들은 데이터베이스가 제공하는 벡터 연산자로 변환됩니다. 사용하는 데이터베이스가 벡터 타입과 연산을 지원하는지 확인하고, PostgreSQL에서는 pgvector 확장을 활성화해 주세요.

Raw 식

Raw 식은 SQL 문자열로 직접 쿼리에 삽입됩니다. 사용자 입력을 직접 전달하면 SQL 인젝션의 위험이 있습니다. 반드시 바인딩을 사용해 안전하게 기술해 주세요.

// DB::raw() — 임의의 SQL 식을 삽입
$users = DB::table('users')
    ->select(DB::raw('count(*) as user_count, status'))
    ->groupBy('status')
    ->get();

// selectRaw — SELECT 절에 Raw 식을 추가
$orders = DB::table('orders')
    ->selectRaw('price * ? as price_with_tax', [1.10])
    ->get();

// whereRaw — WHERE 절에 Raw 식을 추가
$orders = DB::table('orders')
    ->whereRaw('price > IF(state = "JP", ?, 100)', [500])
    ->get();

// havingRaw — HAVING 절에 Raw 식을 추가
$orders = DB::table('orders')
    ->select('department', DB::raw('SUM(amount) as total'))
    ->groupBy('department')
    ->havingRaw('SUM(amount) > ?', [100000])
    ->get();

// orderByRaw — ORDER BY 절에 Raw 식을 추가
$orders = DB::table('orders')
    ->orderByRaw('updated_at - created_at DESC')
    ->get();

INSERT / UPDATE / DELETE

INSERT

// 1건 삽입
DB::table('users')->insert([
    'email' => '[email protected]',
    'name'  => '홍길동',
]);

// 여러 건 삽입
DB::table('users')->insert([
    ['email' => '[email protected]', 'name' => '홍길동'],
    ['email' => '[email protected]', 'name' => '김철수'],
]);

// 삽입 후에 AUTO_INCREMENT의 ID를 취득
$id = DB::table('users')->insertGetId([
    'email' => '[email protected]',
    'name'  => '이영희',
]);

UPSERT (INSERT OR UPDATE)

// 존재하면 업데이트, 없으면 삽입
DB::table('users')->upsert(
    [
        ['email' => '[email protected]', 'name' => '홍길동', 'votes' => 5],
        ['email' => '[email protected]', 'name' => '김철수', 'votes' => 10],
    ],
    uniqueBy: ['email'],    // 중복 체크의 컬럼
    update: ['name', 'votes'] // 업데이트할 컬럼
);

UPDATE

// 조건부 업데이트
$affected = DB::table('users')
    ->where('id', 1)
    ->update(['name' => '홍길순', 'updated_at' => now()]);

// 인크리먼트·디크리먼트
DB::table('users')->where('id', 1)->increment('votes');       // +1
DB::table('users')->where('id', 1)->increment('votes', 5);    // +5
DB::table('users')->where('id', 1)->decrement('votes');       // -1
DB::table('users')->where('id', 1)->decrement('balance', 100); // -100

DELETE

// 조건부 삭제
$deleted = DB::table('users')->where('status', 'inactive')->delete();

// 테이블 전건 삭제 (AUTO_INCREMENT를 리셋)
DB::table('users')->truncate();

조건부 쿼리 (when)

쿼리 조건을 동적으로 적용하고 싶을 때는 when()을 사용하면 조건 분기를 깔끔하게 작성할 수 있습니다.

$status = request('status');
$sortBy = request('sort', 'name');

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

디버그

// 생성되는 SQL을 확인
$sql = DB::table('users')->where('active', true)->toSql();
// "select * from `users` where `active` = ?"

// SQL과 바인딩을 양쪽 확인
$bindings = DB::table('users')->where('active', true)->getBindings();

// 쿼리를 실행하여 덤프 (실행은 계속)
DB::table('users')->where('active', true)->dump();

// 쿼리를 실행하여 덤프하고 종료
DB::table('users')->where('active', true)->dd();

dd()는 디버그 시에 편리하지만, 프로덕션 환경에서는 절대로 사용하지 말아 주세요. toSql()과 getBindings()로 SQL과 바인딩을 확인하는 것이 안전합니다.

정리

자주 사용하는 메서드 일람

메서드	설명
`get()`	전건 취득 (Collection)
`first()`	최초의 1건 취득
`find($id)`	ID로 1건 취득
`value($column)`	컬럼의 값을 1건 취득
`pluck($column)`	컬럼 값의 리스트를 취득
`count()`	건수
`sum($col)`	합계
`avg($col)`	평균
`max($col)`	최댓값
`min($col)`	최솟값
`exists()`	존재 확인
`insert([...])`	삽입
`update([...])`	업데이트
`delete()`	삭제
`chunk($n, fn)`	분할하여 처리
`when($cond, fn)`	조건부 쿼리
`toSql()`	생성 SQL을 확인

쿼리 빌더 vs Eloquent

쿼리 빌더는 Eloquent보다도 저레벨이며, 모델의 인스턴스가 아닌 stdClass를 반환합니다. 릴레이션이나 모델 이벤트(옵저버)가 필요 없는 경우, 쿼리 빌더 쪽이 심플하고 고속입니다.

// Eloquent: User 모델의 인스턴스를 반환
$users = User::where('active', true)->get();
echo $users[0]->name; // User 오브젝트

// 쿼리 빌더: stdClass를 반환
$users = DB::table('users')->where('active', true)->get();
echo $users[0]->name; // stdClass 오브젝트

​쿼리 빌더란

​Eloquent와의 구분 사용

​데이터 취득

​전건 취득

​1건 취득

​특정 컬럼의 값을 리스트로 취득

​대량 데이터의 분할 처리

​스트리밍 (LazyCollection)

​집계

​레코드의 존재 확인

​SELECT 절

​WHERE 절

​기본적인 조건

​조건의 그룹화

​whereIn / whereBetween / whereNull

​whereLike (패턴 매치)

​whereAny / whereAll (여러 컬럼에 대한 동일 조건)

​whereNullSafeEquals (NULL 안전한 등가 비교)

​JOIN

​서브 쿼리 JOIN

​정렬·그룹화·리미트

​서브 쿼리

​벡터 유사도 검색

​Raw 식

​INSERT / UPDATE / DELETE

​INSERT

​UPSERT (INSERT OR UPDATE)

​UPDATE

​DELETE

​조건부 쿼리 (when)

​디버그

​정리

쿼리 빌더란

Eloquent와의 구분 사용

데이터 취득

전건 취득

1건 취득

특정 컬럼의 값을 리스트로 취득

대량 데이터의 분할 처리

스트리밍 (LazyCollection)

집계

레코드의 존재 확인

SELECT 절

WHERE 절

기본적인 조건

조건의 그룹화

whereIn / whereBetween / whereNull

whereLike (패턴 매치)

whereAny / whereAll (여러 컬럼에 대한 동일 조건)

whereNullSafeEquals (NULL 안전한 등가 비교)

JOIN

서브 쿼리 JOIN

정렬·그룹화·리미트

서브 쿼리

벡터 유사도 검색

Raw 식

INSERT / UPDATE / DELETE

INSERT

UPSERT (INSERT OR UPDATE)

UPDATE

DELETE

조건부 쿼리 (when)

디버그

정리