데이터베이스: 쿼리 빌더

• 소개
• 데이터베이스 쿼리 실행
  • 결과 청크 처리
  • 결과를 느리게 스트리밍
  • 집계
• Select 문
• Raw 표현식
• Join
• Union
• 기본 Where 절
  • Where 절
  • Or Where 절
  • Where Not 절
  • Where Any / All / None 절
  • JSON Where 절
  • 추가 Where 절
  • 논리적 그룹화
• 고급 Where 절
  • Where Exists 절
  • 서브쿼리 Where 절
  • 전체 텍스트 Where 절
• 정렬, 그룹화, 제한 및 오프셋
  • 정렬
  • 그룹화
  • 제한 및 오프셋
• 조건부 절
• Insert 문
  • Upsert
• Update 문
  • JSON 컬럼 업데이트
  • 증가 및 감소
• Delete 문
• 비관적 잠금
• 디버깅

소개

Laravel의 데이터베이스 쿼리 빌더는 데이터베이스 쿼리를 만들고 실행하기 위한 편리하고 유연한 인터페이스를 제공합니다. 응용 프로그램에서 대부분의 데이터베이스 작업을 수행하는 데 사용할 수 있으며 Laravel에서 지원하는 모든 데이터베이스 시스템과 완벽하게 작동합니다.

Laravel 쿼리 빌더는 PDO 파라미터 바인딩을 사용하여 응용 프로그램을 SQL 인젝션 공격으로부터 보호합니다. 쿼리 바인딩으로 쿼리 빌더에 전달되는 문자열을 정리하거나 삭제할 필요가 없습니다.

데이터베이스 쿼리 실행

테이블에서 모든 행 검색

쿼리를 시작하려면 DB 파사드에서 제공하는 table 메소드를 사용할 수 있습니다. table 메소드는 지정된 테이블에 대한 유창한 쿼리 빌더 인스턴스를 반환하므로 쿼리에 더 많은 제약 조건을 연결한 다음 get 메소드를 사용하여 쿼리 결과를 검색할 수 있습니다.

<?php
 
namespace App\Http\Controllers;
 
use Illuminate\Support\Facades\DB;
use Illuminate\View\View;
 
class UserController extends Controller
{
    /**
     * 응용 프로그램의 모든 사용자 목록을 표시합니다.
     */
    public function index(): View
    {
        $users = DB::table('users')->get();
 
        return view('user.index', ['users' => $users]);
    }
}

get 메소드는 쿼리 결과를 포함하는 Illuminate\Support\Collection 인스턴스를 반환하며, 각 결과는 PHP stdClass 객체의 인스턴스입니다. 객체의 속성으로 컬럼에 접근하여 각 컬럼 값을 가져올 수 있습니다.

use Illuminate\Support\Facades\DB;
 
$users = DB::table('users')->get();
 
foreach ($users as $user) {
    echo $user->name;
}

테이블에서 단일 행 / 컬럼 검색

데이터베이스 테이블에서 단일 행만 검색해야 하는 경우 DB 파사드의 first 메소드를 사용할 수 있습니다. 이 메소드는 단일 stdClass 객체를 반환합니다.

$user = DB::table('users')->where('name', 'John')->first();
 
return $user->email;

데이터베이스 테이블에서 단일 행을 검색하지만 일치하는 행이 없는 경우 Illuminate\Database\RecordNotFoundException을 throw하려면 firstOrFail 메소드를 사용할 수 있습니다. RecordNotFoundException이 잡히지 않으면 404 HTTP 응답이 자동으로 클라이언트로 다시 전송됩니다.

$user = DB::table('users')->where('name', 'John')->firstOrFail();

전체 행이 필요하지 않은 경우 value 메소드를 사용하여 레코드에서 단일 값을 추출할 수 있습니다. 이 메소드는 컬럼 값을 직접 반환합니다.

$email = DB::table('users')->where('name', 'John')->value('email');

id 컬럼 값으로 단일 행을 검색하려면 find 메소드를 사용하십시오.

$user = DB::table('users')->find(3);

컬럼 값 목록 검색

단일 컬럼의 값을 포함하는 Illuminate\Support\Collection 인스턴스를 검색하려면 pluck 메소드를 사용할 수 있습니다. 이 예에서는 사용자 제목 컬렉션을 검색합니다.

use Illuminate\Support\Facades\DB;
 
$titles = DB::table('users')->pluck('title');
 
foreach ($titles as $title) {
    echo $title;
}

pluck 메소드에 두 번째 인수를 제공하여 결과 컬렉션이 키로 사용할 컬럼을 지정할 수 있습니다.

$titles = DB::table('users')->pluck('title', 'name');
 
foreach ($titles as $name => $title) {
    echo $title;
}

결과 청크 처리

수천 개의 데이터베이스 레코드를 처리해야 하는 경우 DB 파사드에서 제공하는 chunk 메소드를 사용하는 것을 고려하십시오. 이 메소드는 한 번에 작은 결과 청크를 검색하고 각 청크를 처리하기 위해 클로저로 전달합니다. 예를 들어 users 테이블 전체를 한 번에 100개의 레코드씩 청크로 검색해 보겠습니다.

use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
 
DB::table('users')->orderBy('id')->chunk(100, function (Collection $users) {
    foreach ($users as $user) {
        // ...
    }
});

클로저에서 false를 반환하여 추가 청크 처리를 중지할 수 있습니다.

DB::table('users')->orderBy('id')->chunk(100, function (Collection $users) {
    // 레코드 처리...
 
    return false;
});

결과를 청크하는 동안 데이터베이스 레코드를 업데이트하는 경우 청크 결과가 예상치 못한 방식으로 변경될 수 있습니다. 청크하는 동안 검색된 레코드를 업데이트하려는 경우 항상 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]);
        }
    });

chunkById 및 lazyById 메소드는 실행되는 쿼리에 자체 "where" 조건을 추가하므로 일반적으로 클로저 내에서 자체 조건을 논리적으로 그룹화해야 합니다.

DB::table('users')->where(function ($query) {
    $query->where('credits', 1)->orWhere('credits', 2);
})->chunkById(100, function (Collection $users) {
    foreach ($users as $user) {
        DB::table('users')
          ->where('id', $user->id)
          ->update(['credits' => 3]);
    }
});

결과 스트리밍 지연 처리

lazy 메서드는 쿼리를 청크 단위로 실행한다는 점에서 the chunk 메서드와 유사하게 작동합니다. 그러나 각 청크를 콜백으로 전달하는 대신, lazy() 메서드는 LazyCollection을 반환하여 결과를 단일 스트림으로 상호 작용할 수 있도록 합니다.

use Illuminate\Support\Facades\DB;
 
DB::table('users')->orderBy('id')->lazy()->each(function (object $user) {
    // ...
});

반복하는 동안 검색된 레코드를 업데이트할 계획이라면 lazyById 또는 lazyByIdDesc 메서드를 대신 사용하는 것이 가장 좋습니다. 이러한 메서드는 레코드의 기본 키를 기반으로 결과를 자동으로 페이징합니다.

DB::table('users')->where('active', false)
    ->lazyById()->each(function (object $user) {
        DB::table('users')
            ->where('id', $user->id)
            ->update(['active' => true]);
    });

집계

쿼리 빌더는 count, max, min, avg, sum과 같은 집계 값을 검색하기 위한 다양한 메서드도 제공합니다. 쿼리를 구성한 후 이러한 메서드를 호출할 수 있습니다.

use Illuminate\Support\Facades\DB;
 
$users = DB::table('users')->count();
 
$price = DB::table('orders')->max('price');

물론 이러한 메서드를 다른 절과 결합하여 집계 값이 계산되는 방식을 미세 조정할 수 있습니다.

$price = DB::table('orders')
                ->where('finalized', 1)
                ->avg('price');

레코드 존재 여부 확인

쿼리 제약 조건과 일치하는 레코드가 있는지 확인하기 위해 count 메서드를 사용하는 대신 exists 및 doesntExist 메서드를 사용할 수 있습니다.

if (DB::table('orders')->where('finalized', 1)->exists()) {
    // ...
}
 
if (DB::table('orders')->where('finalized', 1)->doesntExist()) {
    // ...
}

Select 문

Select 절 지정

데이터베이스 테이블에서 모든 열을 선택하지 않을 수도 있습니다. select 메서드를 사용하면 쿼리에 대한 사용자 지정 "select" 절을 지정할 수 있습니다.

use Illuminate\Support\Facades\DB;
 
$users = DB::table('users')
            ->select('name', 'email as user_email')
            ->get();

distinct 메서드를 사용하면 쿼리가 고유한 결과를 반환하도록 강제할 수 있습니다.

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

이미 쿼리 빌더 인스턴스가 있고 기존 select 절에 열을 추가하려는 경우 addSelect 메서드를 사용할 수 있습니다.

$query = DB::table('users')->select('name');
 
$users = $query->addSelect('age')->get();

Raw 표현식

쿼리에 임의의 문자열을 삽입해야 할 때가 있습니다. raw 문자열 표현식을 만들려면 DB facade에서 제공하는 raw 메서드를 사용할 수 있습니다.

$users = DB::table('users')
             ->select(DB::raw('count(*) as user_count, status'))
             ->where('status', '<>', 1)
             ->groupBy('status')
             ->get();

Raw 메서드

DB::raw 메서드를 사용하는 대신 다음 메서드를 사용하여 쿼리의 다양한 부분에 raw 표현식을 삽입할 수도 있습니다. Laravel은 raw 표현식을 사용하는 쿼리가 SQL 인젝션 취약성으로부터 보호된다는 것을 보장할 수 없습니다.

selectRaw

selectRaw 메서드는 addSelect(DB::raw(/* ... */)) 대신 사용할 수 있습니다. 이 메서드는 두 번째 인수로 선택적 바인딩 배열을 허용합니다.

$orders = DB::table('orders')
                ->selectRaw('price * ? as price_with_tax', [1.0825])
                ->get();

whereRaw / orWhereRaw

whereRaw 및 orWhereRaw 메서드를 사용하여 raw "where" 절을 쿼리에 삽입할 수 있습니다. 이러한 메서드는 두 번째 인수로 선택적 바인딩 배열을 허용합니다.

$orders = DB::table('orders')
                ->whereRaw('price > IF(state = "TX", ?, 100)', [200])
                ->get();

havingRaw / orHavingRaw

havingRaw 및 orHavingRaw 메서드를 사용하여 "having" 절의 값으로 raw 문자열을 제공할 수 있습니다. 이러한 메서드는 두 번째 인수로 선택적 바인딩 배열을 허용합니다.

$orders = DB::table('orders')
                ->select('department', DB::raw('SUM(price) as total_sales'))
                ->groupBy('department')
                ->havingRaw('SUM(price) > ?', [2500])
                ->get();

orderByRaw

orderByRaw 메서드를 사용하여 "order by" 절의 값으로 raw 문자열을 제공할 수 있습니다.

$orders = DB::table('orders')
                ->orderByRaw('updated_at - created_at DESC')
                ->get();

groupByRaw

groupByRaw 메서드를 사용하여 group by 절의 값으로 raw 문자열을 제공할 수 있습니다.

$orders = DB::table('orders')
                ->select('city', 'state')
                ->groupByRaw('city, state')
                ->get();

조인

내부 조인 절

쿼리 빌더를 사용하여 쿼리에 조인 절을 추가할 수도 있습니다. 기본 "내부 조인"을 수행하려면 쿼리 빌더 인스턴스에서 join 메서드를 사용할 수 있습니다. join 메서드에 전달되는 첫 번째 인수는 조인해야 하는 테이블의 이름이고, 나머지 인수는 조인에 대한 열 제약 조건을 지정합니다. 단일 쿼리에서 여러 테이블을 조인할 수도 있습니다.

use Illuminate\Support\Facades\DB;
 
$users = DB::table('users')
            ->join('contacts', 'users.id', '=', 'contacts.user_id')
            ->join('orders', 'users.id', '=', 'orders.user_id')
            ->select('users.*', 'contacts.phone', 'orders.price')
            ->get();

왼쪽 조인 / 오른쪽 조인 절

"내부 조인" 대신 "왼쪽 조인" 또는 "오른쪽 조인"을 수행하려면 leftJoin 또는 rightJoin 메서드를 사용합니다. 이러한 메서드는 join 메서드와 동일한 서명을 갖습니다.

$users = DB::table('users')
            ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
            ->get();
 
$users = DB::table('users')
            ->rightJoin('posts', 'users.id', '=', 'posts.user_id')
            ->get();

교차 조인 절

crossJoin 메서드를 사용하여 "교차 조인"을 수행할 수 있습니다. 교차 조인은 첫 번째 테이블과 조인된 테이블 사이에 데카르트 곱을 생성합니다.

$sizes = DB::table('sizes')
            ->crossJoin('colors')
            ->get();

고급 조인 절

보다 고급 조인 절을 지정할 수도 있습니다. 시작하려면 클로저를 join 메서드에 대한 두 번째 인수로 전달합니다. 클로저는 "join" 절에 대한 제약 조건을 지정할 수 있는 Illuminate\Database\Query\JoinClause 인스턴스를 수신합니다.

DB::table('users')
        ->join('contacts', function (JoinClause $join) {
            $join->on('users.id', '=', 'contacts.user_id')->orOn(/* ... */);
        })
        ->get();

조인에 "where" 절을 사용하려면 JoinClause 인스턴스에서 제공하는 where 및 orWhere 메서드를 사용할 수 있습니다. 두 열을 비교하는 대신 이러한 메서드는 열을 값과 비교합니다.

DB::table('users')
        ->join('contacts', function (JoinClause $join) {
            $join->on('users.id', '=', 'contacts.user_id')
                 ->where('contacts.user_id', '>', 5);
        })
        ->get();

서브쿼리 조인

joinSub, leftJoinSub 및 rightJoinSub 메서드를 사용하여 쿼리를 서브쿼리에 조인할 수 있습니다. 이러한 각 메서드는 세 가지 인수를 받습니다. 즉, 서브쿼리, 테이블 별칭 및 관련 열을 정의하는 클로저입니다. 이 예에서는 각 사용자 레코드에 사용자의 가장 최근에 게시된 블로그 게시물의 created_at 타임스탬프도 포함된 사용자 컬렉션을 검색합니다.

$latestPosts = DB::table('posts')
                   ->select('user_id', DB::raw('MAX(created_at) as last_post_created_at'))
                   ->where('is_published', true)
                   ->groupBy('user_id');
 
$users = DB::table('users')
        ->joinSub($latestPosts, 'latest_posts', function (JoinClause $join) {
            $join->on('users.id', '=', 'latest_posts.user_id');
        })->get();

Lateral 조인

joinLateral 및 leftJoinLateral 메서드를 사용하여 서브쿼리와 함께 "lateral 조인"을 수행할 수 있습니다. 이러한 각 메서드는 서브쿼리와 테이블 별칭의 두 가지 인수를 받습니다. 조인 조건은 주어진 서브쿼리의 where 절 내에 지정해야 합니다. Lateral 조인은 각 행에 대해 평가되며 서브쿼리 외부의 열을 참조할 수 있습니다.

이 예에서는 사용자의 세 가지 최신 블로그 게시물과 함께 사용자 컬렉션을 검색합니다. 각 사용자는 결과 집합에서 최대 3개의 행을 생성할 수 있습니다. 가장 최근의 블로그 게시물 각각에 대해 하나씩입니다. 조인 조건은 현재 사용자 행을 참조하는 서브쿼리 내의 whereColumn 절로 지정됩니다.

$latestPosts = DB::table('posts')
                   ->select('id as post_id', 'title as post_title', 'created_at as post_created_at')
                   ->whereColumn('user_id', 'users.id')
                   ->orderBy('created_at', 'desc')
                   ->limit(3);
 
$users = DB::table('users')
            ->joinLateral($latestPosts, 'latest_posts')
            ->get();

유니온

쿼리 빌더는 둘 이상의 쿼리를 "유니온"하는 편리한 메서드도 제공합니다. 예를 들어 초기 쿼리를 만들고 union 메서드를 사용하여 더 많은 쿼리와 유니온할 수 있습니다.

use Illuminate\Support\Facades\DB;
 
$first = DB::table('users')
            ->whereNull('first_name');
 
$users = DB::table('users')
            ->whereNull('last_name')
            ->union($first)
            ->get();

union 메서드 외에도 쿼리 빌더는 unionAll 메서드를 제공합니다. unionAll 메서드를 사용하여 결합된 쿼리는 중복된 결과가 제거되지 않습니다. unionAll 메서드는 union 메서드와 동일한 메서드 서명을 갖습니다.

기본 Where 절

Where 절

쿼리 빌더의 where 메서드를 사용하여 쿼리에 "where" 절을 추가할 수 있습니다. where 메서드에 대한 가장 기본적인 호출에는 세 가지 인수가 필요합니다. 첫 번째 인수는 열의 이름입니다. 두 번째 인수는 데이터베이스에서 지원되는 연산자일 수 있는 연산자입니다. 세 번째 인수는 열의 값과 비교할 값입니다.

예를 들어 다음 쿼리는 votes 열의 값이 100과 같고 age 열의 값이 35보다 큰 사용자를 검색합니다.

$users = DB::table('users')
                ->where('votes', '=', 100)
                ->where('age', '>', 35)
                ->get();

편의를 위해 열이 지정된 값과 =인지 확인하려는 경우 값을 where 메서드의 두 번째 인수로 전달할 수 있습니다. Laravel은 = 연산자를 사용하고 싶다고 가정합니다.

$users = DB::table('users')->where('votes', 100)->get();

앞에서 언급했듯이 데이터베이스 시스템에서 지원하는 연산자를 사용할 수 있습니다.

$users = DB::table('users')
                ->where('votes', '>=', 100)
                ->get();
 
$users = DB::table('users')
                ->where('votes', '<>', 100)
                ->get();
 
$users = DB::table('users')
                ->where('name', 'like', 'T%')
                ->get();

조건 배열을 where 함수에 전달할 수도 있습니다. 배열의 각 요소는 일반적으로 where 메서드에 전달되는 세 개의 인수를 포함하는 배열이어야 합니다.

$users = DB::table('users')->where([
    ['status', '=', '1'],
    ['subscribed', '<>', '1'],
])->get();

Or Where 절

쿼리 빌더의 where 메서드에 대한 호출을 함께 연결할 때 "where" 절은 and 연산자를 사용하여 함께 결합됩니다. 그러나 orWhere 메서드를 사용하여 or 연산자를 사용하여 절을 쿼리에 조인할 수 있습니다. orWhere 메서드는 where 메서드와 동일한 인수를 허용합니다.

$users = DB::table('users')
                    ->where('votes', '>', 100)
                    ->orWhere('name', 'John')
                    ->get();

괄호 안에 "or" 조건을 그룹화해야 하는 경우 클로저를 orWhere 메서드의 첫 번째 인수로 전달할 수 있습니다.

$users = DB::table('users')
            ->where('votes', '>', 100)
            ->orWhere(function (Builder $query) {
                $query->where('name', 'Abigail')
                      ->where('votes', '>', 50);
            })
            ->get();

위의 예는 다음 SQL을 생성합니다.

select * from users where votes > 100 or (name = 'Abigail' and votes > 50)

Where Not 절

whereNot 및 orWhereNot 메서드는 주어진 쿼리 제약 조건 그룹을 부정하는 데 사용할 수 있습니다. 예를 들어, 다음 쿼리는 정리 세일 중이거나 가격이 10 미만인 제품을 제외합니다.

$products = DB::table('products')
                ->whereNot(function (Builder $query) {
                    $query->where('clearance', true)
                          ->orWhere('price', '<', 10);
                })
                ->get();

Where Any / All / None 절

경우에 따라 여러 열에 동일한 쿼리 제약 조건을 적용해야 할 수 있습니다. 예를 들어, 주어진 목록의 모든 열이 주어진 값과 LIKE인 모든 레코드를 검색할 수 있습니다. whereAny 메서드를 사용하여 이를 수행할 수 있습니다.

$users = DB::table('users')
            ->where('active', true)
            ->whereAny([
                'name',
                'email',
                'phone',
            ], 'like', 'Example%')
            ->get();

위의 쿼리는 다음 SQL을 생성합니다.

SELECT *
FROM users
WHERE active = true AND (
    name LIKE 'Example%' OR
    email LIKE 'Example%' OR
    phone LIKE 'Example%'
)

마찬가지로, whereAll 메서드는 주어진 모든 열이 주어진 제약 조건과 일치하는 레코드를 검색하는 데 사용할 수 있습니다.

$posts = DB::table('posts')
            ->where('published', true)
            ->whereAll([
                'title',
                'content',
            ], 'like', '%Laravel%')
            ->get();

위의 쿼리는 다음 SQL을 생성합니다.

SELECT *
FROM posts
WHERE published = true AND (
    title LIKE '%Laravel%' AND
    content LIKE '%Laravel%'
)

whereNone 메서드는 주어진 열 중 어느 것도 주어진 제약 조건과 일치하지 않는 레코드를 검색하는 데 사용할 수 있습니다.

$posts = DB::table('albums')
            ->where('published', true)
            ->whereNone([
                'title',
                'lyrics',
                'tags',
            ], 'like', '%explicit%')
            ->get();

위 쿼리는 다음 SQL을 생성합니다.

SELECT *
FROM albums
WHERE published = true AND NOT (
    title LIKE '%explicit%' OR
    lyrics LIKE '%explicit%' OR
    tags LIKE '%explicit%'
)

JSON Where 절

Laravel은 JSON 컬럼 타입을 지원하는 데이터베이스에서 JSON 컬럼 타입에 대한 쿼리도 지원합니다. 현재는 MariaDB 10.3+, MySQL 8.0+, PostgreSQL 12.0+, SQL Server 2017+, 그리고 SQLite 3.39.0+가 포함됩니다. JSON 컬럼을 쿼리하려면 -> 연산자를 사용하세요.

$users = DB::table('users')
                ->where('preferences->dining->meal', 'salad')
                ->get();

JSON 배열을 쿼리하려면 whereJsonContains를 사용할 수 있습니다.

$users = DB::table('users')
                ->whereJsonContains('options->languages', 'en')
                ->get();

애플리케이션에서 MariaDB, MySQL 또는 PostgreSQL 데이터베이스를 사용하는 경우 whereJsonContains 메서드에 값 배열을 전달할 수 있습니다.

$users = DB::table('users')
                ->whereJsonContains('options->languages', ['en', 'de'])
                ->get();

JSON 배열을 길이로 쿼리하려면 whereJsonLength 메서드를 사용할 수 있습니다.

$users = DB::table('users')
                ->whereJsonLength('options->languages', 0)
                ->get();
 
$users = DB::table('users')
                ->whereJsonLength('options->languages', '>', 1)
                ->get();

추가 Where 절

whereLike / orWhereLike / whereNotLike / orWhereNotLike

whereLike 메서드를 사용하면 패턴 매칭을 위한 "LIKE" 절을 쿼리에 추가할 수 있습니다. 이러한 메서드는 대소문자 구분 설정을 토글할 수 있는 기능을 제공하여 데이터베이스에 구애받지 않고 문자열 매칭 쿼리를 수행할 수 있는 방법을 제공합니다. 기본적으로 문자열 매칭은 대소문자를 구분하지 않습니다.

$users = DB::table('users')
           ->whereLike('name', '%John%')
           ->get();

caseSensitive 인수를 통해 대소문자를 구분하는 검색을 활성화할 수 있습니다.

$users = DB::table('users')
           ->whereLike('name', '%John%', caseSensitive: true)
           ->get();

orWhereLike 메서드를 사용하면 LIKE 조건과 함께 "or" 절을 추가할 수 있습니다.

$users = DB::table('users')
           ->where('votes', '>', 100)
           ->orWhereLike('name', '%John%')
           ->get();

whereNotLike 메서드를 사용하면 쿼리에 "NOT LIKE" 절을 추가할 수 있습니다.

$users = DB::table('users')
           ->whereNotLike('name', '%John%')
           ->get();

마찬가지로 orWhereNotLike를 사용하여 NOT LIKE 조건과 함께 "or" 절을 추가할 수 있습니다.

$users = DB::table('users')
           ->where('votes', '>', 100)
           ->orWhereNotLike('name', '%John%')
           ->get();

whereIn / whereNotIn / orWhereIn / orWhereNotIn

whereIn 메서드는 주어진 컬럼의 값이 주어진 배열 내에 포함되어 있는지 확인합니다.

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

whereNotIn 메서드는 주어진 컬럼의 값이 주어진 배열 내에 포함되어 있지 않은지 확인합니다.

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

whereIn 메서드의 두 번째 인수로 쿼리 객체를 제공할 수도 있습니다.

$activeUsers = DB::table('users')->select('id')->where('is_active', 1);
 
$users = DB::table('comments')
                    ->whereIn('user_id', $activeUsers)
                    ->get();

위 예제는 다음 SQL을 생성합니다.

-- 활성 사용자(is_active = 1)의 ID를 가진 사용자로부터 작성된 모든 댓글을 선택합니다.
select * from comments where user_id in (
    select id
    from users
    where is_active = 1
)

whereBetween / orWhereBetween

whereBetween 메서드는 컬럼 값이 두 값 사이에 있는지 확인합니다.

$users = DB::table('users')
           ->whereBetween('votes', [1, 100])
           ->get();

whereNotBetween / orWhereNotBetween

whereNotBetween 메서드는 컬럼 값이 두 값 밖에 있는지 확인합니다.

$users = DB::table('users')
                    ->whereNotBetween('votes', [1, 100])
                    ->get();

whereBetweenColumns / whereNotBetweenColumns / orWhereBetweenColumns / orWhereNotBetweenColumns

whereBetweenColumns 메서드는 컬럼 값이 동일한 테이블 행에 있는 두 컬럼의 두 값 사이에 있는지 확인합니다.

$patients = DB::table('patients')
                       ->whereBetweenColumns('weight', ['minimum_allowed_weight', 'maximum_allowed_weight'])
                       ->get();

whereNotBetweenColumns 메서드는 컬럼 값이 동일한 테이블 행에 있는 두 컬럼의 두 값 밖에 있는지 확인합니다.

$patients = DB::table('patients')
                       ->whereNotBetweenColumns('weight', ['minimum_allowed_weight', 'maximum_allowed_weight'])
                       ->get();

whereNull / whereNotNull / orWhereNull / orWhereNotNull

whereNull 메서드는 주어진 컬럼의 값이 NULL인지 확인합니다.

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

whereNotNull 메서드는 컬럼 값이 NULL이 아닌지 확인합니다.

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

whereDate / whereMonth / whereDay / whereYear / whereTime

whereDate 메서드는 컬럼 값을 날짜와 비교하는 데 사용할 수 있습니다.

$users = DB::table('users')
                ->whereDate('created_at', '2016-12-31')
                ->get();

whereMonth 메서드는 컬럼 값을 특정 월과 비교하는 데 사용할 수 있습니다.

$users = DB::table('users')
                ->whereMonth('created_at', '12')
                ->get();

whereDay 메서드는 컬럼 값을 특정 월의 일과 비교하는 데 사용할 수 있습니다.

$users = DB::table('users')
                ->whereDay('created_at', '31')
                ->get();

whereYear 메서드는 컬럼 값을 특정 년도와 비교하는 데 사용할 수 있습니다.

$users = DB::table('users')
                ->whereYear('created_at', '2016')
                ->get();

whereTime 메서드는 컬럼 값을 특정 시간과 비교하는 데 사용할 수 있습니다.

$users = DB::table('users')
                ->whereTime('created_at', '=', '11:20:45')
                ->get();

whereColumn / orWhereColumn

whereColumn 메서드는 두 컬럼이 같은지 확인하는 데 사용할 수 있습니다.

$users = DB::table('users')
                ->whereColumn('first_name', 'last_name')
                ->get();

비교 연산자를 whereColumn 메서드에 전달할 수도 있습니다.

$users = DB::table('users')
                ->whereColumn('updated_at', '>', 'created_at')
                ->get();

컬럼 비교 배열을 whereColumn 메서드에 전달할 수도 있습니다. 이러한 조건은 and 연산자를 사용하여 결합됩니다.

$users = DB::table('users')
                ->whereColumn([
                    ['first_name', '=', 'last_name'],
                    ['updated_at', '>', 'created_at'],
                ])->get();

논리적 그룹화

때로는 쿼리에서 원하는 논리적 그룹화를 달성하기 위해 여러 "where" 절을 괄호 안에 그룹화해야 할 수 있습니다. 실제로 예기치 않은 쿼리 동작을 방지하기 위해 orWhere 메서드에 대한 호출은 일반적으로 항상 괄호 안에 그룹화해야 합니다. 이를 위해 where 메서드에 클로저를 전달할 수 있습니다.

$users = DB::table('users')
           ->where('name', '=', 'John')
           ->where(function (Builder $query) {
               $query->where('votes', '>', 100)
                     ->orWhere('title', '=', 'Admin');
           })
           ->get();

보시다시피, 클로저를 where 메서드에 전달하면 쿼리 빌더가 제약 그룹을 시작하도록 지시합니다. 클로저는 괄호 그룹 내에 포함되어야 하는 제약 조건을 설정하는 데 사용할 수 있는 쿼리 빌더 인스턴스를 받습니다. 위의 예제는 다음 SQL을 생성합니다.

select * from users where name = 'John' and (votes > 100 or title = 'Admin')

고급 Where 절

Where Exists 절

whereExists 메서드를 사용하면 "where exists" SQL 절을 작성할 수 있습니다. whereExists 메서드는 클로저를 받으며, 이 클로저는 쿼리 빌더 인스턴스를 받아 "exists" 절 안에 배치해야 하는 쿼리를 정의할 수 있습니다.

$users = DB::table('users')
           ->whereExists(function (Builder $query) {
               $query->select(DB::raw(1))
                     ->from('orders')
                     ->whereColumn('orders.user_id', 'users.id');
           })
           ->get();

또는 클로저 대신 쿼리 객체를 whereExists 메서드에 제공할 수 있습니다.

$orders = DB::table('orders')
                ->select(DB::raw(1))
                ->whereColumn('orders.user_id', 'users.id');
 
$users = DB::table('users')
                    ->whereExists($orders)
                    ->get();

위의 두 예제 모두 다음 SQL을 생성합니다.

select * from users
where exists (
    select 1
    from orders
    where orders.user_id = users.id
)

서브쿼리 Where 절

때로는 서브쿼리의 결과를 주어진 값과 비교하는 "where" 절을 구성해야 할 수 있습니다. where 메서드에 클로저와 값을 전달하여 이를 수행할 수 있습니다. 예를 들어, 다음 쿼리는 주어진 유형의 최신 "멤버십"을 가진 모든 사용자를 검색합니다.

use App\Models\User;
use Illuminate\Database\Query\Builder;
 
$users = User::where(function (Builder $query) {
    $query->select('type')
        ->from('membership')
        ->whereColumn('membership.user_id', 'users.id')
        ->orderByDesc('membership.start_date')
        ->limit(1);
}, 'Pro')->get();

또는, 컬럼을 서브쿼리의 결과와 비교하는 "where" 절을 구성해야 할 수 있습니다. where 메서드에 컬럼, 연산자 및 클로저를 전달하여 이를 수행할 수 있습니다. 예를 들어, 다음 쿼리는 금액이 평균보다 작은 모든 수입 레코드를 검색합니다.

use App\Models\Income;
use Illuminate\Database\Query\Builder;
 
$incomes = Income::where('amount', '<', function (Builder $query) {
    $query->selectRaw('avg(i.amount)')->from('incomes as i');
})->get();

전체 텍스트 Where 절

whereFullText 및 orWhereFullText 메서드는 전체 텍스트 인덱스가 있는 컬럼에 전체 텍스트 "where" 절을 쿼리에 추가하는 데 사용할 수 있습니다. 이러한 메서드는 Laravel에서 기본 데이터베이스 시스템에 적합한 SQL로 변환됩니다. 예를 들어, MariaDB 또는 MySQL을 사용하는 애플리케이션의 경우 MATCH AGAINST 절이 생성됩니다.

$users = DB::table('users')
           ->whereFullText('bio', 'web developer')
           ->get();

정렬, 그룹화, 제한 및 오프셋

정렬

orderBy 메서드

orderBy 메서드를 사용하면 주어진 컬럼을 기준으로 쿼리 결과를 정렬할 수 있습니다. orderBy 메서드에서 허용하는 첫 번째 인수는 정렬하려는 컬럼이어야 하며, 두 번째 인수는 정렬 방향을 결정하며 asc 또는 desc 중 하나일 수 있습니다.

$users = DB::table('users')
                ->orderBy('name', 'desc')
                ->get();

여러 컬럼을 기준으로 정렬하려면 필요한 만큼 orderBy를 호출하면 됩니다.

$users = DB::table('users')
                ->orderBy('name', 'desc')
                ->orderBy('email', 'asc')
                ->get();

latest 및 oldest 메서드

latest 및 oldest 메서드를 사용하면 날짜별로 결과를 쉽게 정렬할 수 있습니다. 기본적으로 결과는 테이블의 created_at 컬럼을 기준으로 정렬됩니다. 또는, 정렬하려는 컬럼 이름을 전달할 수 있습니다.

$user = DB::table('users')
                ->latest()
                ->first();

무작위 정렬

inRandomOrder 메서드를 사용하여 쿼리 결과를 무작위로 정렬할 수 있습니다. 예를 들어, 이 메서드를 사용하여 무작위 사용자를 가져올 수 있습니다.

$randomUser = DB::table('users')
                ->inRandomOrder()
                ->first();

기존 정렬 제거

reorder 메서드는 쿼리에 이전에 적용된 모든 "order by" 절을 제거합니다.

$query = DB::table('users')->orderBy('name');
 
$unorderedUsers = $query->reorder()->get();

reorder 메서드를 호출할 때 컬럼과 방향을 전달하여 기존의 모든 "order by" 절을 제거하고 쿼리에 완전히 새로운 정렬을 적용할 수 있습니다.

$query = DB::table('users')->orderBy('name');
 
$usersOrderedByEmail = $query->reorder('email', 'desc')->get();

그룹화

groupBy 및 having 메서드

예상대로 groupBy 및 having 메서드를 사용하여 쿼리 결과를 그룹화할 수 있습니다. having 메서드의 서명은 where 메서드와 유사합니다.

$users = DB::table('users')
                ->groupBy('account_id')
                ->having('account_id', '>', 100)
                ->get();

havingBetween 메서드를 사용하여 주어진 범위 내에서 결과를 필터링할 수 있습니다.

$report = DB::table('orders')
                ->selectRaw('count(id) as number_of_orders, customer_id')
                ->groupBy('customer_id')
                ->havingBetween('number_of_orders', [5, 15])
                ->get();

여러 컬럼을 기준으로 그룹화하려면 groupBy 메서드에 여러 인수를 전달할 수 있습니다.

$users = DB::table('users')
                ->groupBy('first_name', 'status')
                ->having('account_id', '>', 100)
                ->get();

더 고급 having 문을 빌드하려면 havingRaw 메서드를 참조하십시오.

제한 및 오프셋

skip 및 take 메서드

skip 및 take 메서드를 사용하여 쿼리에서 반환되는 결과 수를 제한하거나 쿼리에서 주어진 수의 결과를 건너뛸 수 있습니다.

$users = DB::table('users')->skip(10)->take(5)->get();

또는 limit 및 offset 메서드를 사용할 수 있습니다. 이러한 메서드는 각각 take 및 skip 메서드와 기능적으로 동일합니다.

$users = DB::table('users')
                ->offset(10)
                ->limit(5)
                ->get();

조건부 절

때로는 다른 조건에 따라 특정 쿼리 절을 쿼리에 적용해야 할 수 있습니다. 예를 들어, 들어오는 HTTP 요청에 주어진 입력 값이 있는 경우에만 where 문을 적용할 수 있습니다. when 메서드를 사용하여 이를 수행할 수 있습니다.

$role = $request->input('role');
 
$users = DB::table('users')
                ->when($role, function (Builder $query, string $role) {
                    $query->where('role_id', $role);
                })
                ->get();

when 메서드는 첫 번째 인수가 true일 때만 주어진 클로저를 실행합니다. 첫 번째 인수가 false이면 클로저가 실행되지 않습니다. 따라서 위의 예에서 when 메서드에 주어진 클로저는 들어오는 요청에 role 필드가 있고 true로 평가되는 경우에만 호출됩니다.

when 메서드에 세 번째 인수로 다른 클로저를 전달할 수 있습니다. 이 클로저는 첫 번째 인수가 false로 평가되는 경우에만 실행됩니다. 이 기능이 어떻게 사용될 수 있는지 설명하기 위해 쿼리의 기본 정렬을 구성하는 데 사용해 보겠습니다.

$sortByVotes = $request->boolean('sort_by_votes');
 
$users = DB::table('users')
                ->when($sortByVotes, function (Builder $query, bool $sortByVotes) {
                    $query->orderBy('votes');
                }, function (Builder $query) {
                    $query->orderBy('name');
                })
                ->get();

삽입 구문

쿼리 빌더는 데이터베이스 테이블에 레코드를 삽입하는 데 사용할 수 있는 insert 메서드도 제공합니다. insert 메서드는 컬럼 이름과 값의 배열을 허용합니다.

DB::table('users')->insert([
    'email' => '[email protected]',
    'votes' => 0
]);

배열의 배열을 전달하여 여러 레코드를 한 번에 삽입할 수 있습니다. 각 배열은 테이블에 삽입해야 하는 레코드를 나타냅니다.

DB::table('users')->insert([
    ['email' => '[email protected]', 'votes' => 0],
    ['email' => '[email protected]', 'votes' => 0],
]);

insertOrIgnore 메서드는 레코드를 데이터베이스에 삽입하는 동안 오류를 무시합니다. 이 메서드를 사용할 때는 중복 레코드 오류가 무시되고 데이터베이스 엔진에 따라 다른 유형의 오류도 무시될 수 있음을 알아야 합니다. 예를 들어, insertOrIgnore는 MySQL의 엄격 모드를 우회합니다.

DB::table('users')->insertOrIgnore([
    ['id' => 1, 'email' => '[email protected]'],
    ['id' => 2, 'email' => '[email protected]'],
]);

insertUsing 메서드는 서브쿼리를 사용하여 삽입해야 하는 데이터를 결정하는 동안 새 레코드를 테이블에 삽입합니다.

DB::table('pruned_users')->insertUsing([
    'id', 'name', 'email', 'email_verified_at'
], DB::table('users')->select(
    'id', 'name', 'email', 'email_verified_at'
)->where('updated_at', '<=', now()->subMonth()));

자동 증가 ID

테이블에 자동 증가 ID가 있는 경우 insertGetId 메서드를 사용하여 레코드를 삽입한 다음 ID를 검색합니다.

$id = DB::table('users')->insertGetId(
    ['email' => '[email protected]', 'votes' => 0]
);

Upserts

upsert 메서드는 존재하지 않는 레코드를 삽입하고 이미 존재하는 레코드를 지정할 수 있는 새 값으로 업데이트합니다. 메서드의 첫 번째 인수는 삽입하거나 업데이트할 값으로 구성되고, 두 번째 인수는 연결된 테이블 내에서 레코드를 고유하게 식별하는 컬럼 목록입니다. 메서드의 세 번째이자 마지막 인수는 일치하는 레코드가 데이터베이스에 이미 존재하는 경우 업데이트해야 하는 컬럼 배열입니다.

DB::table('flights')->upsert(
    [
        ['departure' => 'Oakland', 'destination' => 'San Diego', 'price' => 99],
        ['departure' => 'Chicago', 'destination' => 'New York', 'price' => 150]
    ],
    ['departure', 'destination'],
    ['price']
);

위의 예에서 Laravel은 두 개의 레코드를 삽입하려고 시도합니다. 동일한 출발 및 도착지 컬럼 값을 가진 레코드가 이미 존재하는 경우 Laravel은 해당 레코드의 가격 컬럼을 업데이트합니다.

업데이트 구문

쿼리 빌더는 데이터베이스에 레코드를 삽입하는 것 외에도 update 메서드를 사용하여 기존 레코드를 업데이트할 수도 있습니다. update 메서드는 insert 메서드와 마찬가지로 업데이트할 컬럼을 나타내는 컬럼 및 값 쌍의 배열을 허용합니다. update 메서드는 영향을 받은 행 수를 반환합니다. where 절을 사용하여 update 쿼리를 제한할 수 있습니다.

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

업데이트 또는 삽입

데이터베이스에서 기존 레코드를 업데이트하거나 일치하는 레코드가 없는 경우 생성해야 할 수 있습니다. 이 시나리오에서는 updateOrInsert 메서드를 사용할 수 있습니다. updateOrInsert 메서드는 두 개의 인수를 허용합니다. 레코드를 찾기 위한 조건의 배열과 업데이트할 컬럼을 나타내는 컬럼 및 값 쌍의 배열입니다.

updateOrInsert 메서드는 첫 번째 인수의 컬럼 및 값 쌍을 사용하여 일치하는 데이터베이스 레코드를 찾으려고 시도합니다. 레코드가 있으면 두 번째 인수의 값으로 업데이트됩니다. 레코드를 찾을 수 없으면 두 인수의 병합된 속성으로 새 레코드가 삽입됩니다.

DB::table('users')
    ->updateOrInsert(
        ['email' => '[email protected]', 'name' => 'John'],
        ['votes' => '2']
    );

클로저를 updateOrInsert 메서드에 제공하여 일치하는 레코드의 존재 여부에 따라 데이터베이스에 업데이트되거나 삽입되는 속성을 사용자 지정할 수 있습니다.

DB::table('users')->updateOrInsert(
    ['user_id' => $user_id],
    fn ($exists) => $exists ? [
        'name' => $data['name'],
        'email' => $data['email'],
    ] : [
        'name' => $data['name'],
        'email' => $data['email'],
        'marketable' => true,
    ],
);

번역:

DB::table('users')->updateOrInsert(
    ['user_id' => $user_id], // 'user_id'가 $user_id와 일치하는 레코드를 찾습니다.
    fn ($exists) => $exists ? [ // 해당 레코드가 존재하는지 확인합니다.
        'name' => $data['name'], // 레코드가 존재하면 'name'과 'email'을 업데이트합니다.
        'email' => $data['email'],
    ] : [ // 레코드가 존재하지 않으면 'name', 'email', 'marketable'을 포함하여 새로운 레코드를 삽입합니다.
        'name' => $data['name'],
        'email' => $data['email'],
        'marketable' => true,
    ],
);

설명:

이 PHP 코드는 Laravel 프레임워크의 데이터베이스 기능을 사용하여 users 테이블에서 레코드를 업데이트하거나 삽입합니다. updateOrInsert 메서드는 주어진 조건에 맞는 레코드가 있으면 업데이트하고, 없으면 새로운 레코드를 삽입하는 데 사용됩니다.

• ['user_id' => $user_id] : user_id 컬럼이 $user_id 변수와 일치하는 레코드를 찾습니다.
• fn ($exists) => ... : 익명 함수(클로저)를 사용하여 레코드가 존재하는지 여부에 따라 다른 업데이트 또는 삽입 데이터를 반환합니다. $exists는 레코드가 존재하는 경우 true를, 그렇지 않은 경우 false를 가집니다.
• $exists ? [...] : [...] : 삼항 연산자를 사용하여 레코드가 존재하는 경우와 존재하지 않는 경우에 따라 다른 배열을 반환합니다.
  • 레코드가 존재할 때 (업데이트): name과 email을 $data 배열에서 가져와 업데이트합니다.
  • 레코드가 존재하지 않을 때 (삽입): name, email, marketable을 $data 배열과 함께 새로운 레코드에 삽입합니다. marketable은 true로 설정됩니다.

요약하자면, 이 코드는 주어진 user_id에 해당하는 사용자가 users 테이블에 이미 존재하면 해당 사용자의 이름과 이메일을 업데이트하고, 존재하지 않으면 새로운 사용자를 추가하면서 marketable 플래그를 true로 설정합니다.

JSON 컬럼 업데이트

JSON 컬럼을 업데이트할 때, JSON 객체 내에서 적절한 키를 업데이트하려면 -> 구문을 사용해야 합니다. 이 작업은 MariaDB 10.3+, MySQL 5.7+, PostgreSQL 9.5+에서 지원됩니다.

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

증가 및 감소

쿼리 빌더는 주어진 컬럼의 값을 증가시키거나 감소시키는 편리한 메서드도 제공합니다. 이 두 메서드 모두 최소한 하나의 인수를 받습니다: 수정할 컬럼입니다. 두 번째 인수는 컬럼을 증가시키거나 감소시킬 양을 지정하기 위해 제공될 수 있습니다.

DB::table('users')->increment('votes');
 
DB::table('users')->increment('votes', 5);
 
DB::table('users')->decrement('votes');
 
DB::table('users')->decrement('votes', 5);

필요한 경우, 증가 또는 감소 작업 중에 업데이트할 추가 컬럼을 지정할 수도 있습니다.

DB::table('users')->increment('votes', 1, ['name' => 'John']);

또한, incrementEach 및 decrementEach 메서드를 사용하여 한 번에 여러 컬럼을 증가시키거나 감소시킬 수 있습니다.

DB::table('users')->incrementEach([
    'votes' => 5,
    'balance' => 100,
]);

삭제 구문

쿼리 빌더의 delete 메서드는 테이블에서 레코드를 삭제하는 데 사용할 수 있습니다. delete 메서드는 영향을 받은 행 수를 반환합니다. delete 메서드를 호출하기 전에 "where" 절을 추가하여 delete 구문을 제한할 수 있습니다.

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

테이블 전체를 자르고 싶다면(테이블의 모든 레코드를 제거하고 자동 증가 ID를 0으로 재설정), truncate 메서드를 사용할 수 있습니다.

DB::table('users')->truncate();

테이블 자르기 및 PostgreSQL

PostgreSQL 데이터베이스를 자를 때 CASCADE 동작이 적용됩니다. 이는 다른 테이블의 모든 외래 키 관련 레코드도 삭제됨을 의미합니다.

비관적 잠금

쿼리 빌더에는 select 구문을 실행할 때 "비관적 잠금"을 구현하는 데 도움이 되는 몇 가지 함수도 포함되어 있습니다. "공유 잠금"으로 구문을 실행하려면 sharedLock 메서드를 호출할 수 있습니다. 공유 잠금은 트랜잭션이 커밋될 때까지 선택된 행이 수정되는 것을 방지합니다.

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

또는 lockForUpdate 메서드를 사용할 수도 있습니다. "업데이트 잠금"은 선택된 레코드가 수정되거나 다른 공유 잠금으로 선택되는 것을 방지합니다.

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

디버깅

쿼리를 빌드하는 동안 dd 및 dump 메서드를 사용하여 현재 쿼리 바인딩과 SQL을 덤프할 수 있습니다. dd 메서드는 디버그 정보를 표시한 다음 요청 실행을 중지합니다. dump 메서드는 디버그 정보를 표시하지만 요청 실행을 계속 허용합니다.

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

dumpRawSql 및 ddRawSql 메서드를 쿼리에서 호출하여 모든 매개변수 바인딩이 적절하게 대체된 쿼리의 SQL을 덤프할 수 있습니다.

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