How to get sql in laravel

Laravel is a very popular PHP framework that provides us with a convenient way to manage database access and queries. However, in some business scenarios that require customization, we need to manually view and debug the generated SQL statements in order to better understand the operation process of the program and optimize the performance of SQL. This article will introduce in detail how to obtain SQL statements in the Laravel framework.

1. Laravel’s query builder

In Laravel, we can use the query builder to get data from the database. The query builder is the main abstraction layer of the database in Laravel. It provides a series of APIs to build and execute query statements, which is very convenient to use. For example, you can use the following code to get records from a data table:

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

Use the query builder to get large amounts of data quickly and easily. However, this method does not provide the SQL statement we finally execute. At this time, we need to use some additional tools to obtain the generated SQL statement.

2. Enable query log

Laravel provides a very convenient way to obtain executed SQL statements, which is to enable query log. Laravel's database query log is a mechanism that records all executed query statements, including information such as execution time and execution results. During development, enabling query logs can facilitate us to debug programs and optimize SQL performance.

The method to enable query log is very simple, you only need to set it in the Laravel configuration file config/database.php. In Laravel's default configuration file, the query log is turned off by default. You can modify the configuration file by changing 'log' => 'false' to 'log' => 'true ', you can turn on the query log. The modified configuration file is as follows:

'connections' => [
    'mysql' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => null,
        'log' => true, //开启查询日志
    ],
],

After enabling query log, Laravel will save the query log in the storage/logs/laravel.log file. We can obtain the executed SQL statements by viewing this file. We can obtain the query log through the following code in the Laravel console or debugging tool:

DB::connection()->enableQueryLog();
// 在这里书写需要查询的代码
dd(DB::getQueryLog());

The above code will enable the query log and output the query log. During the execution of the program, call the above code to obtain the query log, and you can see the SQL statements executed by the program in real time.

It should be noted that enabling query logs will have a certain impact on system performance, so query logs should not be enabled all the time in a production environment. In addition, Laravel query log is only enabled in the development environment by default, so you need to manually set it in the configuration file to enable query log.

3. Using the DB facade

Laravel also provides another way to obtain the executed SQL statement-using the DB facade. The DB facade provides a series of static methods that can easily create and execute query statements and obtain executed SQL statements. For example, the same is to obtain data in the users table:

$users = DB::select('select * from users');

After executing the above code, the DB facade will generate a select statement and execute it to obtain the records in the data table. However, this method also does not provide an interface for directly viewing the executed SQL statements. However, the DB facade provides a simpler method to obtain the generated SQL statement. The method is as follows:

$sql = DB::getQueryLog();

This method will return an array containing all query logs. These queries are executed through the DB facade. . After obtaining the array, you can read the query attribute of each log to view the SQL statement executed each time.

4. Use the ORM model

In Laravel, the ORM model is an object-oriented data access method. It maps the records of the data table into objects to facilitate our database operations. operate. The ORM model is Laravel's high-level encapsulation of the query builder. Using the ORM model makes it easier to access the database, and when querying data tables through model association, the generated SQL query statements are more intuitive and easier to debug.

In the process of using the ORM model, we can also obtain the generated SQL statements. Laravel provides a method called toSql(), which can output the query statement of the model. For example, we need to use the ORM model to obtain data in the users table:

$users = AppModelsUser::all();

Among them, User is the ORM model we defined, just use the all() method Get all records in the table corresponding to this model. We can obtain the SQL statement generated by the model by calling the toSql() method. As shown below:

$sql = AppModelsUser::all()->toSql();

The above code will return the query statement processed by the ORM model. It should be noted that the toSql() method can only replace the placeholder in the model in the generated SQL statement and cannot execute the SQL statement. Therefore, this method can only be used to view the generated SQL statement and cannot be used as a query method. alternatives.

Summarize

This article introduces how to easily obtain executed SQL statements in the Laravel framework. By enabling query logs, using DB facade and ORM models and other methods, you can obtain or view the SQL executed by the program, which facilitates us to debug and optimize program performance. Of course, different methods have their applicable scenarios, and you need to choose the most suitable method according to the specific scenario. For example, in the development environment, you can enable query logs to view the execution of the program; when using the ORM model for data access, the toSql() method can easily view the generated SQL statements. In actual projects, developers can flexibly use these methods to improve SQL query efficiency and program performance.

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