Laravel's Query Builder provides a smooth way to build database queries without writing native SQL. 1. Use the DB facade to directly query table data, such as DB::table('users')->get() to get all users; 2. Common methods include get(), first(), pluck(), take() for data retrieval, where(), orWhere(), whereIn(), whereNull() add conditions, orderBy(), latest() sort, join() association table, count(), max() and other aggregate functions; 3. Support insert() insert() to insert data, update() update() to update data, delete() delete data, and truncate() clear table; 4. Advanced usage includes DB::raw() to execute native expressions, when() conditional query, and subquery nesting; 5. It can be used in combination with the Eloquent model, such as User::where()->get() to return model instances; Query Builder is suitable for complex queries and has better performance than Eloquent. During debugging, you can view SQL statements toSql() to ultimately achieve concise and efficient database operations.

Laravel's Query Builder is a powerful and fluent way to construct and run database queries without writing raw SQL. It's part of Laravel's Eloquent ORM but can be used independently with database tables. Here's how to use it effectively.

1. Using the DB Facade

To start using the Query Builder, import and use Laravel's DB facade:

 use Illuminate\Support\Facades\DB;

Then you can query any table like this:

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

This retrieves all rows from the users table.

2. Common Query Builder Methods

Here are the most commonly used methods with examples:

? Retrieve Data

• Get all rows:

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

• Get first row:

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

• Get specific columns:

   $names = DB::table('users')->pluck('name');

• Limit results:

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

? Add WHERE Clauses

• Simple where:

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

• Chained conditions (AND):

   $user = DB::table('users')
      ->where('status', 'active')
      ->where('age', '>', 18)
      ->get();

• OR conditions:

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

• Where in:

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

• Null checks:

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

? Sorting & Ordering

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

You can also use latest() and oldest() for timestamp columns:

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

? Joins

 $users = DB::table('users')
    ->join('contacts', 'users.id', '=', 'contacts.user_id')
    ->select('users.name', 'contacts.phone')
    ->get();

Supports leftJoin , rightJoin , etc.

? Aggregates

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

3. Insert, Update, Delete

? Insert Data

 DB::table('users')->insert([
    'name' => 'John Doe',
    'email' => 'john@example.com',
    'created_at' => now(),
    'updated_at' => now(),
]);

For getting the inserted ID (if the table has an auto-incrementing ID):

 $id = DB::table('users')->insertGetId([
    'name' => 'Jane Doe',
    'email' => 'jane@example.com',
    'created_at' => now(),
    'updated_at' => now(),
]);

? Update Data

 $affected = DB::table('users')
    ->where('id', 1)
    ->update([
        'email' => 'newemail@example.com',
        'updated_at' => now(),
    ]);

Returns the number of affected rows.

? Delete Data

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

Or delete all:

 DB::table('users')->truncate(); // Removes all rows

4. Advanced Usage

? Raw Expressions

Use DB::raw() to inject raw SQL:

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

Be cautious with raw expressions to avoid SQL injection.

? Conditional Clauses

Use when() to apply conditions only when needed:

 $role = 'admin';
$users = DB::table('users')
    ->when($role, function ($query, $role) {
        return $query->where('role', $role);
    })
    ->get();

This only applies the where clause if $role is truthy.

? Subqueries (Laravel 6)

 $latestLogin = DB::table('logins')
    ->select('user_id')
    ->whereColumn('user_id', 'users.id')
    ->orderBy('login_at', 'desc')
    ->limit(1);

$users = DB::table('users')
    ->select('users.*')
    ->where('id', 'in', $latestLogin)
    ->get();

5. Using with Models (Eloquent)

Even though Eloquent models use the Query Builder under the hood, you can still use it directly on models:

 $users = User::where('active', 1)->orderBy('name')->get();

This returns Eloquent models, not plain objects.

Final Notes

• The Query Builder is great for complex queries or when you don't need full Eloquent features.
• It's faster than Eloquent for simple operations because it doesn't instantiate model objects.
• Always sanitize input when using raw expressions.
• Use dd() or toSql() dd() for debugging:

 $query = DB::table('users')->where('active', 1);
echo $query->toSql(); // SELECT * FROM users WHERE active = ?

Basically, the Query Builder gives you a clean, expressive way to interact with your database — no raw SQL needed in most cases.

The above is the detailed content of How to use the query builder in Laravel?. For more information, please follow other related articles on the PHP Chinese website!