Laravel development: How to use Laravel Query Builder for complex queries?

Laravel Query Builder is a popular ORM (Object Relational Mapping) tool for simplifying database operations and queries. It provides a convenient, object-oriented syntax that allows developers to easily write complex SQL queries without the need to manually write SQL code.

In this article, we will introduce how to use Laravel Query Builder to perform complex query operations. Including basic queries, aggregation queries, join table queries, grouping and sorting, etc.

Basic query

First of all, basic query refers to operating directly on a single table. The most basic query operation is select. By searching for documents, you can query data in the following ways:

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

Among them, the first parameter specifies the table name to be queried, and the parameters in the select function specify the query. The result column, the get function obtains the query results.

Aggregation query

Aggregation query is a query that summarizes data, such as COUNT, SUM, AVG, MAX, MIN, etc. We can use the aggregate function of Laravel Query Builder to complete these operations:

$usersCount = DB::table('users')->count();
$usersMaxId = DB::table('users')->max('id');
$usersMinId = DB::table('users')->min('id');
$usersSumSalary = DB::table('users')->sum('salary');
$usersAvgSalary = DB::table('users')->avg('salary');

In the above example, the count, maximum value, minimum value, sum and average operations were performed on the users table.

Joined table query

Joined table query is a common operation used to retrieve data from multiple related tables. We can use the join function to join two or more tables:

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

In the above example, the first parameter in the join function is the name of the other table to be joined, and the second parameter is two The connection condition between tables, the third parameter is the selected result column.

Grouping and sorting

Grouping and sorting are operations of filtering and sorting query results. For example, we can sort the results by the value of a specific column, or group by a certain column. Laravel Query Builder provides the orderBy and groupBy functions to complete these operations:

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

In the above example, the orderBy function is used to sort the results in ascending order by name, and the groupBy function is used to group the results by the gender field.

Summary

Laravel Query Builder is a very powerful tool that can help us complete complex query operations easily and efficiently. We use Laravel Query Builder in our projects to avoid handwriting complex SQL codes and improve development efficiency. Mastering these basic operations can greatly improve our development efficiency and code quality.

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