TP5 Access database specific implementation---query finder

We do it using Query Finder. Using the Query Builder encapsulates operations for different databases, which provide us with a unified operation for different databases. No need to care about different database differences. Using native SQL to operate, the entire TP5 database access layer loses its meaning. We've been saying that query is an inquiry builder. But this query is generally refers to the word, it is not only a database of read operations, as well as write operations. Query construction is just a syntax, and eventually there are builder to translate into SQL statements to query.

All operations databases are DB classes, and the Query Builder is also a DB class

$result = Db::table(' table_name ')->where ('id ',' = ', $id);

Returned an object found connection and builder are MySQL databases, returning the query object

If you want to have a result that must be

$result = Db::table(' table_name ')->where ('id ',' = ', $id)->find ();

is the query result, but the Find () method returns only one piece of data and returns all the attributes.

$result = Db::table(' table_name ')->where ('id ',' = ', $id)->select ();

Returns a two-dimensional array, which is a number of records

Db::table ()->where () returns the query object (chained operation)

Only two methods such as Find () and select () will eventually query the results, and the number of chained methods in front will not execute the SQL statement, only select and find.

DB is to hide the details of the database operation, we will use the DB class as long as we manipulate the database. Table->where This is called an auxiliary method, or a chained method.

Select and find are the true query methods.

In the preceding helper method, SQL statements are not executed, regardless of how much is added, and SQL statements are executed only with select and find. (not only select and find, and Update,delete,insert) 5 Select Find Update delete Insert five execution methods

The helper method also becomes the chain method, there are only two, actually write will have 4, 5

Why the chained method does not execute SQL statements. Why he called like a chain.

Helper methods, chained methods do not actually execute SQL statements because they return the query object after each method call. It is because each returns the query object that you can add as many helper methods as you want in the Query builder. No matter how many chained operation methods are added, the end result is always the query object, in the query object, only select,find generates SQL statements. The different chain methods are not sequential. Because each step returns a query object, the order between them is not related. The position relationship between the same chained method (which is emphasized here is different) is likely to have an impact on the final result. For example, there are two order their relationships. If a query builder appears with two identical chained methods, and your resulting SQL results are not the same as expected, it is necessary to troubleshoot whether a sequential relationship is responsible. may have influence also not necessarily has the influence, this is according to the SQL statement to look. The state is purged once the DB call select. But Db::table (' a '); Db::where (' Condi ');D b::where () .... This is related. This type of non chained call is written until the Db::select (), before the state clears. It is obvious that the chain invocation method is more ' elegant ', but it can be done in one sentence, but we are more graceful people to do more elegant things ...

Chain method is a lot of, the most common use is also more complex is where,where (' field name ', ' expression ', ' query condition '); Look at the document lookup expression, eq and = is a meaning, another expression default is ' = ', but the default is also bad, easy to confuse.

TP5 to where there are two ways, we call this expression method, there are array method, and closure, and three methods can be mixed together.

Array method is not flexible enough, there are security problems, generally we do not use.

Closures are good, and closures are the most flexible. The closure refers to a where the inside is not an expression but an anonymous function is passed

$result = db::table (' Custom ')

->where (function ($query) {

$query->where (' id ', ' = ', $id);

})

->select ();

First, the anonymous function passes the $query, which is the query object of the explorer. The interior can be spliced into different chain methods in fact can also stitching where what, but note that you do not call the SELECT statement after query, this is wrong. This is the way to build query queries without being able to invoke statements that generate SQL. The ID is unreadable inside the closure function, and he cannot reference the ID, and the solution is

$result = db::table (' Custom ')

->where (function ($query) use ($id) {

$query->where (' id ', ' = ', $id);

})

->select ();

Need to use ($id)

$result = db::table (' Custom ')

->fetchsql ()

->where (function ($query) use ($id) {

$query->where (' id ', ' = ', $id);

})

->select ();

We have 2 ways to see the generated SQL statements, which contain a more specific chain method Fetchsql (), which, when added, does not actually execute even with a SELECT, but returns the results of the SQL statement generation. This approach has limitations. We usually debug SQL is need to get more detailed information, such as SQL execution time, as well as state parameters, at this time Fetchsql powerless. Another simple query Builder is OK, but this is not part of our code itself, this is extra when debugging, so it's not too good. Join a lot of need to check, can not be every one of Fetchsql bar.

The other is that TP5 automatically writes our SQL to the log. To turn on automatic logging we need to do some parameter configuration. Debug by opening the database is already turned on by default, and config (requiring debug to TRUE) adds ' SQL ' to level at level and changes the type from test to file (if the default logging function is turned off for test, The related log object must be initialized manually)

Log initialization: We can then index.php to initialize the log configuration. Because all requests are going through index.php.

\think\log::init ([
   ' File ',
    log_path,
    => [' SQL '] ]
]);

We need to turn on this SQL record and compare the ORM and the corresponding SQL statements for better results. It's also very helpful to find out if our SQL query statements are good. Because recording each time, this is very helpful to our performance tuning and looking for SQL errors. So open SQL in debug and development mode. Production environment do not open, can save a bit of performance to save performance, write server also consumes performance. Deal with the problem, found that the logic is not to find the wrong reason, you can temporarily open, easy to find errors. The main is to learn and troubleshoot errors. Performance analysis, we generally use the cloud database, such as Aliyun RDS has a very detailed statistical SQL situation, you can very clearly see which SQL statement is time-consuming. Performance tuning is not very meaningful in the actual project.

But with all that said, we're still choosing an ORM object.