How to build complex query conditions in laravel

In Laravel, it is a very common requirement to build complex query conditions. Sometimes we need to query data that meets multiple conditions at the same time, sometimes we need to query the maximum or minimum value of a certain field, sometimes we need to query data that does not meet certain conditions, etc. This article will introduce how to use Laravel's query builder to achieve these requirements.

1. Basic Query

First, let’s take a look at Laravel’s basic query syntax. In Laravel, we can use the table() method of the DB facade to specify the table we want to query, and then use the select() method to specify the fields we need to query.

For example, the following code will query all records in the users table and return their id and name fields:

DB::table('users')->select('id', 'name')->get();

We can also use the where() method to specify query conditions. For example, the following code will query the records in the users table that are older than 18:

DB::table('users')->where('age', '>', 18)->get();

2. Query condition combination

When we need to satisfy multiple conditions at the same time, we can use where () method to combine query conditions.

For example, the following code will query the users table for records that are both older than 18 and male:

DB::table('users')->where('age', '>', 18)->where('gender', 'male')->get();

We can also use the orWhere() method to specify that one of the conditions is met. .

For example, the following code will query the records in the users table that are older than 18 or have a male gender:

DB::table('users')->where('age', '>', 18)->orWhere('gender', 'male')->get();

If we need a combination of multiple conditions, we can use closures to achieve it.

For example, the following code will query the users table for records that meet the requirements of age greater than 18 and gender as male or age equal to 18 and gender as female:

DB::table('users')->where(function($query) {
    $query->where('age', '>', 18)
          ->where('gender', 'male');
})->orWhere(function($query) {
    $query->where('age', '=', 18)
          ->where('gender', 'female');
})->get();

3. Fuzzy query

Sometimes we need to query based on part of a field. We can use the like() method and wildcards to implement fuzzy queries. The following code will query the records in the users table whose names begin with "J":

DB::table('users')->where('name', 'like', 'J%')->get();

4. Maximum and minimum value query

Sometimes we need to query a certain field Maximum or minimum value. We can do this using the max() and min() methods. The following code will query the maximum and minimum values ​​of the age field in the users table:

$maxAge = DB::table('users')->max('age');
$minAge = DB::table('users')->min('age');

5. Not equal to query

Sometimes we need to query that a certain field is not equal to a certain value record. We can use the where() method and <> symbol to achieve this. The following code will query the records in the users table whose gender is not male:

DB::table('users')->where('gender', '<>', 'male')->get();

6. in and not in query

Sometimes we need to query the value of a certain field records in the list. We can do this using the whereIn() method and a list of values. The following code will query the records in the users table whose age field value is 18, 19 or 20:

DB::table('users')->whereIn('age', [18, 19, 20])->get();

We can also use the whereNotIn() method to query records that are not in the value list.

7. exists and not exists query

Sometimes we need to query the records for which a certain condition exists or does not exist. We can use whereExists() and whereNotExists() methods to achieve this. The following code will query the orders table for records corresponding to the user:

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

We can also use the whereNotExists() method to query the records that do not exist for the corresponding user.

So far, we have introduced the basic method of constructing complex query conditions in Laravel. Laravel's query builder makes it easier and more intuitive for us to write query statements without having to think too much about the details of SQL syntax. Of course, the above is just the tip of the iceberg of query statements. There are many advanced uses of Laravel's query builder waiting for us to discover.

The above is the detailed content of How to build complex query conditions in laravel. For more information, please follow other related articles on the PHP Chinese website!