laravel query syntax

Laravel is one of the most popular PHP frameworks today. Its query syntax is elegant, simple and easy to use. In this article, I will delve into Laravel's query syntax, introduce some common query methods, and how to perform complex queries.

Basic syntax of Laravel query
In Laravel, query statements are built using Eloquent ORM. ORM is Object Relational Mapping, which allows us to operate the database in an object-oriented manner, greatly simplifying the interaction process with the database.

The following is an example of using Laravel query syntax:

//Get a single user
$user = User::find(1);

//Get a user List
$users = User::all();

//Conditional filtering to obtain a list of users under specified conditions
$users = User::where('age', '> ', 18)->get();

//Update data
$user->name = 'new name';
$user->save();

//Delete data
$user->delete();

As shown above, using Laravel query syntax, you can easily implement basic CURD operations. Below we will introduce some commonly used query methods.

Commonly used query methods

1. where()
   where() method is a very common conditional filtering method. Filter out records that meet the requirements based on given conditions. For example:

$users = User::where('age', '>', 18)->where('gender', 'male')->get();

The where() method can also receive a third parameter to specify an operator, for example:

$users = User::where('age', '>=', 18)->get();

2. orderBy()
   orderBy() method is used for sorting. For example, we can sort by user's age:

$users = User::orderBy('age')->get();

By default, orderBy () method is arranged in ascending order. If you want to sort in descending order, you can pass a DESC parameter:

$users = User::orderBy('age', 'desc')->get();

Also, The orderBy() method can receive multiple parameters for specifying multiple sorting conditions:

$users = User::orderBy('age', 'desc')->orderBy('name')- >get();

3. select()
   select() method is used to query the specified column. For example:

$users = User::select('name', 'email')->get();

You can also use * to query all columns:

$users = User::select('*')->get();

4. take() and skip()
   take() methods are used for restrictions The number of records returned, skip() is used to skip the specified number of records. For example, we can query the top 10 users:

$users = User::take(10)->get();

You can also use the skip() method Implement paging:

$users = User::skip(10)->take(10)->get();

5. whereIn() and whereNotIn()
   whereIn(), whereNotIn() methods can be used to use IN and NOT IN in queries. For example, to get users under 18 years old, you can write:

$users = User::whereIn('age', [16, 17, 18])->get();

6. whereNull() and whereNotNull()
   The whereNull() method can filter out records with a specified column being empty, and whereNotNull() can filter out records that are not empty. For example:

$users = User::whereNull('email')->get();

7. groupBy() and having()
   groupBy The () method is used to group the result set, and the having() method is used to filter the grouped results. For example, we can group by gender and filter out male users who are over 20 years old:

$users = User::groupBy('gender')->having('age ', '>', 20)->get();

Complex query

The above introduces some basic usage of Laravel query syntax. However, in real development, the data we usually need to query may be very complex, and more query methods need to be used to achieve it. In Laravel, you can use Query Builder to write more complex query statements.

For example, in order to query the users whose cumulative consumption exceeds 500 yuan, you can write like this:

$users = DB::table('users')

        ->join('orders', 'users.id', '=', 'orders.user_id')
        ->select('users.id', 'users.name', 'users.email', DB::raw('SUM(orders.total) as total'))
        ->groupBy('users.id')
        ->having('total', '>', 500)
        ->get();

In the above code, we use the join() method to connect the user table and the order table, use the groupBy() method to group users, and use the having() method to filter out users whose cumulative consumption exceeds 500 yuan. .

Summary

In Laravel, basic CURD operations can be easily implemented using Eloquent ORM. At the same time, more complex queries can be implemented using Query Builder. I hope this article will help you understand Laravel's query syntax.

The above is the detailed content of laravel query syntax. For more information, please follow other related articles on the PHP Chinese website!