39th Chapter Thinkphp--sql Coherent operation

In this lesson, we will learn the coherent operations provided by the Thinkphp model base class, which effectively provides code clarity and development efficiency for data access through coherent operations, and supports all curd operations.

A It is very simple to use coherent entry-and-1,2,3,4 operations, such as finding the top two in reverse order of creation time in ID.

Getting Started with coherent operation $user = M (' user '); Var_dump ($user->where (' ID in (1,2,3,4) ')->order (' Date DESC ')->limit (2)->select ());

PS: The Where, order, and limit methods here are coherent operations, so they all return to the $user itself and can be swapped for positions.

The Select method is not a coherent method and needs to be placed at the end to display the data set.

Array operation $user = M (' user '); Var_dump ($user->select (' where ' \ = ' id in (1,2,3,4) ', ' limit ' = ' 2 ', ' order ' = ' date DESC '));

Curd treatment, curd will be explained in a special section

$user = M (' user '); Var_dump ($user->where (' id=1 ')->find ()); Var_dump ($user->where (' id=7 ')->delete ());

Coherent methods supported by the system coherent operation function supported parameter types

where* a defined string, array, and object used to query or update a condition

Table defines the string and array of data table names to manipulate

Alias is used to define the aliases string for the current data table

Data object assignment arrays and objects before data is added or updated

field defines the fields to query (support field exclusions) strings and arrays

Order is used to sort the results by string and array

Limit is used to restrict the number of query result strings and numbers

Page is used to query paging (internally converted to limit) strings and numbers

Group support string for the query

Having a having support string for the query

Join* for join support strings and arrays for queries

Union* used to support strings, arrays, and objects for the Union of Queries

Distinct distinct support for querying Boolean values

Lock mechanism Boolean value for the database

Cache supports multiple parameters for query caching

Relation for associative queries (requires association model support) strings

Result is used to return a data conversion string

Validate for data auto-validation arrays

Auto for data auto-complete array

Filter is used for data filtering strings scope* for named range strings, arrays bind* for data-bound operations, or multiple parameter tokens for token validation boolean comment for SQL comment strings

PS: can be called multiple times with an * number.

The coherent approach that is not covered in this lesson is discussed in other chapters.

Two Coherence approach

The 1.where where method supports string conditions, array conditions (recommended usage), and multiple invocations.

String Mode $user = M (' user '); Var_dump ($user->where (' id=1 ')->select ());

Indexed array Mode $user = M (' user '); $map [' id '] = 1;

Use an expression array (' EQ ', 1); Var_dump ($user->where ($map)->select ());

Multiple call mode $user = M (' user '); $map [' id '] = array (' eq ', 1); Var_dump ($user->where ($map)->where (' user= ' crayon small New ')->select ());

The 2.order order is used to sort the result set.

Reverse $user = M (' user '); $map [' id '] = array (' eq ', 1); Var_dump ($user->order (' id desc ')->select ());

Positive order Default or ASC//second sort var_dump ($user->order (' ID desc,email desc ')->select ());

PS: First by ID reverse, and then by email reverse//array form to prevent the field and MySQL keyword conflict

$user = M (' user ');

$map [' id '] = array (' eq ', 1);

Var_dump ($user->order (array (' id ' = ' DESC ') ')->select ());

The 3.feild Feild method can return or manipulate fields that can be used for query and write operations.

Only display ID and user two fields $user = M (' user '); Var_dump ($user->field (' id, user ')->select ());

Use SQL functions and aliases $user = M (' user '); Var_dump ($user->field (' SUM (ID) as count, user ')->select ());

Use array parameters with SQL function $user = M (' user '); Var_dump ($user->field (array (' ID ', ' Left (user,3) ' = ' left_user '))->sele CT ());

Get all fields $user = M (' user '); Var_dump ($user->field ()->select ());

You can pass in the * number, or omit the method//for writing $user = M (' user '); $user->field (' User,email ')->create ();

Curd will learn in special chapters.

The 3.limit limit method is primarily used to specify the number of queries and operations.

Limit the number of result sets $user = M (' user '); Var_dump ($user->limit (2)->select ());

Paged Query $user = M (' user '); Var_dump ($user->limit (0,2)->select ());

2, 2, 4,2

The 4.page page method is fully used for paging queries.

Page Paging $user = M (' user ');

Var_dump ($user->page ()->select ());

2, 2, 3,2

The 5.table table method is used for data table operations, mainly switching data tables or multi-table operations.

Switch data Table $user = M (' user '); Var_dump ($user->table (' Think_info ')->select ());

Gets the simplified table name $user = M (' user '); Var_dump ($user->table (' __user__ ')->select ());

__INFO__ Fair//Multi-table Query $user = M (' user '); Var_dump ($user->field (' a.id,b.id ')->table (' __user__ a,__info__ B ')->select ());

Multiple table queries, using array form to avoid keyword collisions $user = M (' user '); Var_dump ($user->field (' a.id,b.id ')->table (Array (' think_user ' = ' a ', ' think_info ' = ' B '))->select () );

6.alias alias for setting data table aliases

Set alias $user = M (' user '); Var_dump ($user->alias (' a ')->select ());

The 7.group group method is typically used to group result sets that combine function statistics.

Grouping statistics $user = M (' user '); Var_dump ($user->field (' User,max (ID) ')->group (' id ')->select ());

Ps:group will be explored separately in the MySQL section.

8.having Having methods are generally used in conjunction with the group method to complete the filtering of data from the grouped results. Grouping statistics combined with having $user = M (' user '); Var_dump ($user->field (' User,max (ID) ')->group (' id ')->having (' id>2 '), select ());

Ps:having will be explored separately in the MySQL section. The 9.comment comment method is used to annotate an SQL statement//sql comment $user = M (' user '); Var_dump ($user->comment (' All Users ')->select ());

The 10.join join method is used for connection queries of multiple tables. Join multiple Table Association, default is inner JOIN $user = M (' user '); Var_dump ($user->join (' think_user on think_info.id = Think_user.id ')->select ()); __user__ and __info__ instead of//right, left, and full Var_dump ($user->join (' think_user on think_info.id = Think_user.id ', ' right ')->select ());

Ps:join will be explored separately in the MySQL section.

The 11.union union method merges the result set of multiple Select//merges multiple select result sets $user = M (' user '); Var_dump ($user->union ("SELECT * from Think_info")->select ());

Ps:union will be explored separately in the MySQL section.

The 12.distinct distinct method is used to return only different values

Returns the non-repeating column $user = M (' user '); Var_dump ($user->distinct (True)->field (' user ')->select ());

13.cache cache for Query cache operation//query cache, second read cache content $user = M (' user ');

Var_dump ($user->cache (True)->select ());

PS: The first query database, the second query the same content directly call the cache, no longer query the database.

For more information on how to use cache and caching, we'll discuss it in detail in the cache section.

Three A named range naming range is essentially wrapping SQL statements in a model definition class, not in a controller.

This layered operation facilitates the readability of the code and avoids problems with developers writing curd operations.

Architects can plan properly within a named range, similar to architects architecting interfaces, so developers are oriented towards interface development.

To use a named range, the first step is to define the attribute:

Class Usermodel extends Model {protected $_scope = Array (//attribute name must be _scope ' SQL1 ' =>array (' where ' =>array (' id ' =>1) ,), ' Sql2 ' =>array (' order ' = ' date DESC ', ' limit ' =>2,), ' Default ' =>array (' where ' =>array (' id ' =>2 ') , ); The attributes supported by the naming range are: Where, field, order, table, limit, page, have, group, lock, DISTINCT, cache.

Call named range $user = D (' user '); Var_dump ($user->scope (' sql2 ')->select ());

Support for invoking multiple scope methods $user = D (' user '); Var_dump ($user->scope (' SQL1 ')->scope (' Sql2 ')->select ());

Default $user = D (' user '); Var_dump ($user->scope ()->select ());

Pass default also line PS: If a named range that does not exist is passed, it is ignored.

Adjust the SQL for the named range $user = D (' user '); Var_dump ($user->scope (' sql2 ', Array (' limit ' =>4))->select ());

Directly overwrite the named range $user = D (' user '); Var_dump ($user->scope (Array (' WHERE ' =>1, ' order ' = ' date DESC ', ' limit ' =>2))->select ());

Call $user = D (' user ') directly with the named range name; Var_dump ($user->sql2 ()->select ());

39th Chapter Thinkphp--sql Coherent operation