SQL coherent operation [2]

1. Aliasfor setting data table aliases

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

In this case, the query statement in SQL is

It is important to note that if you add spaces to the another and name in Anothername, this is problematic. You cannot connect to the database.

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

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

At this point is grouped by the ID of the group, the same ID will be together, if the group (' email '), it will be the same according to the email in a group, then may not be the same email all will be the same

But by email the first match is divided into one piece. Here the field (' User,max (ID) ') is only the user and ID in the database, the others do not display

SQL statement for SELECT 'user',Max(ID) from ' think_user ' GROUP by ID

3, having the method is generally used in conjunction with the group method to complete the filter data from the grouping results.

$user = M (' user '); Var_dump ($user->field (' User,max (ID) ')->group (' id ')->having (' id>2 '),Select ());

After the group is divided, the display of more than 2 in the filter group starts.

SQL statement for SELECT 'user',Max(ID) from ' think_user ' GROUP by ID Having ID>2

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

$user = M (' user '); Var_dump ($usertrue)->field (' user ')->select ());

At this point, if it's the same value, it won't show up again.

5.cache for query caching operations

$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.

Iii. naming ranges

The naming range is actually wrapping the SQL statements in the model definition class, not in the controller. Such a layered exercise
Facilitates readability of the code, preventing developers from having problems writing curd operations. As long as the architect is in a named fan
Reasonable planning within the framework, similar to the architecture of the architect interface, let developers face interface development.

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

In Home/model/usermodel. Insert the following code in class. PHP:

<?phpnamespace Home\model; UseThink\model;classUsermodelextendsModel {protected $_scope=Array(//The $_scope is fixed and cannot be changed' SQL1 ' =Array(         ' WHERE ' =Array(' id ' =>1),        ), ' sql2 ' =Array(        ' Order ' =Array(' Date ' = ' DESC '), ' limit ' = 2,        ), ' Default ' =Array(        ' WHERE ' =Array(' ID ' =>2)        ),    ); }

And then in Home/controller/usercontroller. In class. PHP , it is important to note that the scope cannot be changed when the call is called.

1.

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

The resulting SQL query statement is: SELECT * from ' think_user ' WHERE (' id ' = 1)

2.

Var_dump ($user->scope (' sql2 ')->select ());

The resulting SQL query statement is: SELECT * from ' think_user ' ORDER by ' date ' DESC LIMIT 2

3.

Var_dump ($user->scope (' sql2 ')->scope (' SQL1 ')->select ());

The resulting SQL statement is:

SELECT *  from WHERE = 1 ORDER  by DESC 2

4.

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

The resulting SQL statement is: SELECT * from ' think_user ' WHERE (' id ' = 2 )

In this case, the default array called, that is, the call scope () does not need to fill in the parameters

5.

Var_dump ($user->scope (' sql2 ',array(' limit ' =>4))->select ());

Changing the condition of the original SQL2 array at the time of invocation can also change the entire

6.

Var_dump ($user->scope (' where ' = =array(' id ' =>1), ' order ' = ' Date desc ', ' Limit ' =>4))->select ());

This is the direct write parameter, not the parameters inside the class

7.

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

Here is the array written in the class directly as a method to invoke, the effect and call SQL1 is the same.

SQL coherent operation [2]