Laravel development: How to build complex queries using Laravel Query Builder?

Laravel is an open source PHP framework with elegant, concise syntax and powerful functions. Among them, Query Builder is a powerful database query builder in Laravel that can help us build complex query statements. Below, we'll detail how to build complex queries using Laravel Query Builder.

1. Connect to the database

Before using Laravel Query Builder, we first need to configure and connect to the database. Open the configuration file config/database.php and specify the correct database type, host name, database name, user name and password. As shown in the example below, we are connecting to the MySQL database:

'connections' => [
    'mysql' => [
        'driver' => 'mysql',
        'host' => 'localhost',
        'database' => 'database_name',
        'username' => 'username',
        'password' => 'password',

2. Query all data

Using Laravel Query Builder, querying all data is very simple. In the controller, we can use the following code:

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

Here, we need to replace table_name with the name of the data table that needs to be queried. get()The method will return all data.

3. Query the data of the specified column

If we only need to query the data of the specified column, we can use the select() method. For example:

$data = DB::table('table_name')->select('column_name1', 'column_name2')->get();

Here, we need to specify the column names to be queried, and multiple column names are separated by commas.

4. Conditional query

Laravel Query Builder supports a variety of conditional query methods, including equal to, not equal to, greater than, less than, contains, does not contain, in, etc. For example, we can query the information of users older than 25 years old through the following code:

$data = DB::table('users')->where('age', '>', 25)->get();

Here, we use the where() method to achieve various conditions such as equal to, greater than, less than, etc. Inquire. At the same time, we can also use the orWhere() method to implement the OR relationship between multiple conditions.

5. Fuzzy query

By using the like operator, we can implement fuzzy query in the where() method. For example, we can query information containing "Zhang" in the user's name through the following code:

$data = DB::table('users')->where('name', 'like', '%张%')->get();

6. Sort query

We can use orderBy()Method to sort query results. For example, we can use the following code to implement a query in ascending order of user age:

$data = DB::table('users')->orderBy('age', 'asc')->get();

Here, we need to specify the column name to be sorted and the sorting method (ascending or descending order).

7. Aggregation Query

Laravel Query Builder also supports various aggregate query operations, such as count, sum, max, min, etc. For example, we can implement a query to find the average age of users through the following code:

$data = DB::table('users')->avg('age');

8. Group query

By using the groupBy() method , we can group the query results. For example, we can implement a query grouped by user age through the following code:

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

Here, we need to specify the column name that needs to be grouped.

Summary

Laravel Query Builder provides a rich set of query operations that can help us easily build various complex query statements. This article introduces the basic usage of Laravel Query Builder, including connecting to the database, querying all data, querying specified column data, conditional query, fuzzy query, sorting query, aggregation query and grouping query, etc. I hope this content will help you better use Laravel Query Builder to build complex queries.

The above is the detailed content of Laravel development: How to build complex queries using Laravel Query Builder?. For more information, please follow other related articles on the PHP Chinese website!