Query Builder (Конструктор запросов)

Query Builder Laravel предоставляет удобный, fluent-интерфейс для создания и выполнения SQL-запросов. Он использует PDO parameter binding для защиты от SQL-инъекций. Работает со всеми поддерживаемыми СУБД: MySQL, PostgreSQL, SQLite, SQL Server.

Получение результатов (Select)

use Illuminate\Support\Facades\DB;

// Get all rows from a table
$users = DB::table('users')->get();
// Returns: Illuminate\Support\Collection

// Get a single row
$user = DB::table('users')->where('name', 'John')->first();
// Returns: stdClass|null

// Get a single row or throw exception
$user = DB::table('users')->where('name', 'John')->firstOrFail();
// Throws: Illuminate\Database\RecordNotFoundException

// Get a single column value
$email = DB::table('users')->where('name', 'John')->value('email');
// Returns: string|null

// Get a single row by ID
$user = DB::table('users')->find(3);
// Returns: stdClass|null

// Get a collection of single column values
$emails = DB::table('users')->pluck('email');
// Returns: Collection of strings

// Pluck with custom keys
$roles = DB::table('users')->pluck('name', 'email');
// Returns: Collection ['[email protected]' => 'John', '[email protected]' => 'Jane']

Выборка колонок

// Select specific columns
$users = DB::table('users')
    ->select('name', 'email as user_email')
    ->get();

// Add more columns to existing query
$query = DB::table('users')->select('name');
$users = $query->addSelect('email')->get();

// Distinct values
$titles = DB::table('posts')
    ->select('title')
    ->distinct()
    ->get();

Raw Expressions

use Illuminate\Support\Facades\DB;

// selectRaw
$orders = DB::table('orders')
    ->selectRaw('price * quantity as total_price')
    ->get();

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

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

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

// groupByRaw
$orders = DB::table('orders')
    ->selectRaw('city, state, count(*) as total')
    ->groupByRaw('city, state')
    ->get();

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

Joins

use Illuminate\Support\Facades\DB;

// Inner Join
$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();

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

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

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

// Advanced Join (multiple conditions)
$users = DB::table('users')
    ->join('contacts', function ($join) {
        $join->on('users.id', '=', 'contacts.user_id')
             ->where('contacts.is_primary', true);
    })
    ->get();

// Lateral Join (PostgreSQL)
$latestPosts = DB::table('users')
    ->joinLateral(
        DB::table('posts')
            ->whereColumn('posts.user_id', 'users.id')
            ->orderByDesc('created_at')
            ->limit(3),
        'latest_posts'
    )
    ->get();

// Subquery Join
$latestPrices = DB::table('products')
    ->joinSub(
        DB::table('prices')
            ->select('product_id', DB::raw('MAX(created_at) as latest_date'))
            ->groupBy('product_id'),
        'latest_prices',
        'products.id',
        '=',
        'latest_prices.product_id'
    )
    ->get();

Where-условия

use Illuminate\Support\Facades\DB;

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

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

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

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

// Or Where with closure (grouped conditions)
$users = DB::table('users')
    ->where('votes', '>', 100)
    ->orWhere(function ($query) {
        $query->where('name', 'Abigail')
              ->where('votes', '>', 50);
    })
    ->get();
// SQL: SELECT * FROM users WHERE votes > 100 OR (name = 'Abigail' AND votes > 50)

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

// Where Any / All (Laravel 11)
$users = DB::table('users')
    ->whereAny(['name', 'email', 'phone'], 'like', '%search%')
    ->get();
// SQL: WHERE (name LIKE '%search%' OR email LIKE '%search%' OR phone LIKE '%search%')

$users = DB::table('users')
    ->whereAll(['name', 'email'], 'like', '%john%')
    ->get();
// SQL: WHERE (name LIKE '%john%' AND email LIKE '%john%')

Специализированные Where

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

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

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

// whereDate / whereMonth / whereDay / whereYear / whereTime
$users = DB::table('users')
    ->whereDate('created_at', '2024-12-31')
    ->get();

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

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

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

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

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

// JSON Where (for JSON columns)
$users = DB::table('users')
    ->where('preferences->dining->meal', 'salad')
    ->get();

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

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

// Full Text Where (MySQL/PostgreSQL)
$users = DB::table('users')
    ->whereFullText('bio', 'web developer')
    ->get();

Subquery Where

// Where with subquery
$users = DB::table('users')
    ->where(function ($query) {
        $query->select('type')
              ->from('memberships')
              ->whereColumn('memberships.user_id', 'users.id')
              ->orderByDesc('memberships.start_date')
              ->limit(1);
    }, 'Pro')
    ->get();

// Select with subquery
$users = DB::table('users')
    ->addSelect([
        'last_login' => DB::table('logins')
            ->select('created_at')
            ->whereColumn('user_id', 'users.id')
            ->orderByDesc('created_at')
            ->limit(1),
    ])
    ->get();

Ordering, Grouping, Limit & Offset

use Illuminate\Support\Facades\DB;

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

// Latest / Oldest (shorthand for orderBy created_at)
$users = DB::table('users')->latest()->get();
$users = DB::table('users')->oldest()->get();
$users = DB::table('users')->latest('updated_at')->get();

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

// Reorder (remove all existing orders)
$query = DB::table('users')->orderBy('name');
$users = $query->reorder()->orderBy('email')->get();

// Group By / Having
$users = DB::table('users')
    ->select('account_id', DB::raw('count(*) as total'))
    ->groupBy('account_id')
    ->having('total', '>', 5)
    ->get();

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

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

Aggregates

use Illuminate\Support\Facades\DB;

$count = DB::table('users')->count();
$max   = DB::table('orders')->max('price');
$min   = DB::table('orders')->min('price');
$avg   = DB::table('orders')->avg('price');
$sum   = DB::table('orders')->sum('price');

// Aggregate with conditions
$price = DB::table('orders')
    ->where('finalized', true)
    ->avg('price');

// Exists
$exists = DB::table('orders')
    ->where('finalized', true)
    ->exists();

$doesntExist = DB::table('orders')
    ->where('finalized', false)
    ->doesntExist();

Insert

use Illuminate\Support\Facades\DB;

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

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

// Insert and get auto-incrementing ID
$id = DB::table('users')->insertGetId([
    'email' => '[email protected]',
    'votes' => 0,
]);

// Insert or Ignore (skip duplicates)
DB::table('users')->insertOrIgnore([
    ['id' => 1, 'email' => '[email protected]'],
    ['id' => 2, 'email' => '[email protected]'],
]);

// Upsert (insert or update on conflict)
DB::table('flights')->upsert(
    [
        ['departure' => 'Oakland', 'destination' => 'San Diego', 'price' => 99],
        ['departure' => 'Chicago', 'destination' => 'New York', 'price' => 150],
    ],
    uniqueBy: ['departure', 'destination'],  // Conflict columns
    update: ['price'],                       // Columns to update on conflict
);

Update

use Illuminate\Support\Facades\DB;

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

// Update or Insert (updateOrInsert)
DB::table('users')->updateOrInsert(
    ['email' => '[email protected]'],   // Search conditions
    ['name' => 'John', 'votes' => 2],  // Values to update/insert
);

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

// Increment with additional update
DB::table('users')->increment('votes', 1, ['name' => 'John']);

// Increment or Decrement multiple columns
DB::table('users')->incrementEach([
    'votes' => 5,
    'balance' => 100,
]);

Delete

use Illuminate\Support\Facades\DB;

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

// Truncate (empty table, reset auto-increment)
DB::table('users')->truncate();

Chunking (Обработка большого объёма данных)

use Illuminate\Support\Facades\DB;

// chunk() - process in batches
DB::table('users')->orderBy('id')->chunk(100, function ($users) {
    foreach ($users as $user) {
        // Process each user
    }

    // Return false to stop chunking
    // return false;
});

// chunkById() - safer for updates (uses id-based pagination)
DB::table('users')->chunkById(100, function ($users) {
    foreach ($users as $user) {
        DB::table('users')
            ->where('id', $user->id)
            ->update(['active' => true]);
    }
});

Lazy Collections

use Illuminate\Support\Facades\DB;

// lazy() - returns LazyCollection (memory efficient)
DB::table('users')->orderBy('id')->lazy()->each(function ($user) {
    // Process one user at a time, keeping memory low
});

// lazyById() - uses id-based cursor (safer for updates)
DB::table('users')->lazyById()->each(function ($user) {
    // Process each user
});

// lazyByIdDesc() - reverse order
DB::table('users')->lazyByIdDesc()->each(function ($user) {
    // Process in descending order
});

// Lazy with collection methods
$count = DB::table('users')
    ->orderBy('id')
    ->lazy()
    ->filter(fn ($user) => $user->active)
    ->count();

Cursor (наиболее эффективный по памяти)

// cursor() - uses PHP generator, keeps ONE row in memory
foreach (DB::table('users')->cursor() as $user) {
    // Only one row in memory at a time
}

Сравнение методов:

Pessimistic Locking

use Illuminate\Support\Facades\DB;

// Shared lock (for read)
$users = DB::table('users')
    ->where('votes', '>', 100)
    ->sharedLock()
    ->get();
// SQL: SELECT * FROM users WHERE votes > 100 LOCK IN SHARE MODE

// Exclusive lock (for update)
$users = DB::table('users')
    ->where('votes', '>', 100)
    ->lockForUpdate()
    ->get();
// SQL: SELECT * FROM users WHERE votes > 100 FOR UPDATE

Debugging

// Get the SQL and bindings
$query = DB::table('users')->where('id', 1);
echo $query->toSql();       // SELECT * FROM "users" WHERE "id" = ?
dump($query->getBindings()); // [1]

// toRawSql() - with bindings substituted
echo $query->toRawSql();    // SELECT * FROM "users" WHERE "id" = 1

// dd() / dump() - dump and die
DB::table('users')->where('id', 1)->dd();
DB::table('users')->where('id', 1)->dump();

// Explain
DB::table('users')
    ->where('votes', '>', 100)
    ->explain()
    ->dd();