CURD method check: where method

Today, I would like to explain to you the most common but complex where method for queries. The where method is also one of model-class coherent operation methods and is mainly used to set query and operation conditions. Today, I would like to explain to you the most common but complex where method for queries. The where method is also one of model-class coherent operation methods and is mainly used to set query and operation conditions.
The usage of the where method is the essence of ThinkPHP query language and an important component and highlight of ThinkPHP ORM, you can perform common queries, expression queries, quick queries, interval queries, and combined queries. The where method supports strings and arrays. Although objects can be used, it is not recommended. String conditions use string conditions to directly query and operate, for example:

1. $ User = M ("User"); // instantiate the User object
2. $ User-> where ('Type = 1 AND status = 1')-> select ();

The SQL statement generated after the code is copied is

1. SELECT * FROM think_user WHERE type = 1 AND status = 1

If the code version is 3.1 or later, we recommend that you use the preprocessing mechanism to ensure better security when using string conditions. for example:

1. $ Model-> where ("id = % d and username = '% s' and
2. Xx = '% f' ", array ($ id, $ username, $ xx)-> select ();

Copy the code or use:

1. $ Model-> where ("id = % d and username = '% s' and xx =' % f'", $ id, $ username, $ xx) -> select ();

Copy the code. if the $ id variable is submitted by the user or the URL address, if it is not of the numeric type, it is forcibly formatted as a number for query.
String preprocessing formats support specifying numbers and strings. for details, refer to the parameter description of the vsprintf method. The where usage of array conditions is recommended by ThinkPHP. The simplest way to query arrays is as follows:

1. $ User = M ("User"); // instantiate the User object
2. $ Map ['name'] = 'thinkphp ';
3. $ Map ['status'] = 1;
4. // Pass the query condition into the query method
5. $ User-> where ($ map)-> select ();

The SQL statement generated after the code is copied is

1. SELECT * FROM think_user WHERE 'name' = 'thinkphp' AND status = 1

The query condition above the query by copying the code expression is only a simple equivalent judgment. you can use the query expression to support more SQL query syntaxes. the format of the query expression is as follows:

1. $ Map ['Field 1'] = array ('expression', 'query condition 1 ');
2. $ Map ['Field 2'] = array ('expression', 'query condition 2 ');
3. $ Model-> where ($ map)-> select (); // also supported

The expression used to copy code is case-insensitive. the supported query expressions include the following:

Expression	Description
EQ	Equal to (=)
NEQ	Not equal to (<>)
GT	Greater than (>)
EGT	Greater than or equal to (> =)
LT	Less than (<)
ELT	Less than or equal to (<=)
LIKE	Fuzzy search
[NOT]	(Not in) interval query
[NOT] IN	(Not IN) IN query
EXP	Expression query, supporting SQL syntax

Example:
EQ: Equal to (=)
For example:

1. $ Map ['id'] = array ('EQ ', 100 );

The copied code is equivalent to the following query:

1. $ Map ['id'] = 100;

The query condition indicated by the copy code is id = 100.

NEQ: Not equal to (<>)
For example:

1. $ Map ['id'] = array ('neq', 100 );

The query condition represented by the copied code is id <> 100

GT: Greater than (>)
For example:

1. $ Map ['id'] = array ('GT', 100 );

The query condition indicated by the copied code is id> 100.

EGT: Greater than or equal to (> =)
For example:

1. $ Map ['id'] = array ('egt', 100 );

The query condition indicated by the copied code is id> = 100.

LT: Less than (<)
For example:

1. $ Map ['id'] = array ('Lt ', 100 );

The query condition represented by the copied code is id <100

ELT: Less than or equal to (<=)
For example:

1. $ Map ['id'] = array ('elt', 100 );

The query condition represented by the copied code is id <= 100

[NOT] LIKE: LIKE
For example:

1. $ Map ['name'] = array ('like', 'thinkphp % ');

Copy the code query condition to name like 'thinkphp %'
If the DB_LIKE_FIELDS parameter is configured, some fields are automatically fuzzy queried. For example:

1. 'Db _ LIKE_FIELDS '=> 'Title | content'

To copy the code, use

1. $ Map ['title'] = 'thinkphp ';

Copy the code to change the query condition to name like '% thinkphp %'
Supports arrays, such

1. $ Map ['A'] = array ('like', array ('% thinkphp %', '% tp'), 'OR ');
2. $ Map ['B'] = array ('notlike', array ('% thinkphp %', '% tp'), 'AND ');

The query conditions generated by copying the code are:
(A like '% thinkphp %' OR a like '% tp') AND (B not like '% thinkphp %' AND B not like '% tp ')

[NOT]: Same as SQL [not] between, query conditions support strings or arrays, for example:

1. $ Map ['id'] = array ('between', '1, 8 ');

The replication code is equivalent to the following:

1. $ Map ['id'] = array ('between', array ('1', '8 '));

Copy the code query condition to the id BETWEEN 1 AND 8

[NOT] IN: Same as SQL [not] in, query conditions support strings or arrays, for example:

1. $ Map ['id'] = array ('not in', '1, 5, 8 ');

The replication code is equivalent to the following:

1. $ Map ['id'] = array ('not in', array ('1', '5', '8 '));

Copy the code query condition to the id not in (, 8)

EXP: Expression, supporting more complex queries
For example:

1. $ Map ['id'] = array ('in', '1, 3, 8 ');

The copy code can be changed:

1. $ Map ['id'] = array ('Exp ', 'In (1, 3, 8 )');

The condition for copying the code exp query is not treated as a string, so the following query conditions can use any syntax supported by SQL, including using functions and field names.

A query expression can be used not only for query conditions, but also for data updates. for example:

1. $ User = M ("User"); // instantiate the User object
2. // Assign values to the attributes of the data object to be modified
3. $ Data ['name'] = 'thinkphp ';
4. $ Data ['score '] = array ('Exp', 'Score + 1'); // add 1 to your credit
5. $ User-> where ('Id = 5')-> save ($ data); // save the modified data according to the conditions.

The copy code shortcut query where method supports the shortcut query method, which can further simplify the query condition writing, for example:
1. implement the same query conditions for different fields

1. $ User = M ("User"); // instantiate the User object
2. $ Map ['name | title'] = 'thinkphp ';
3. // Pass the query condition into the query method
4. $ User-> where ($ map)-> select ();

Copy the code query condition to name = 'thinkphp' OR title = 'thinkphp'

2. implement different query conditions for different fields

1. $ User = M ("User"); // instantiate the User object
2. $ Map ['status & title'] = array ('1', 'thinkphp', '_ multi' => true );
3. // Pass the query condition into the query method
4. $ User-> where ($ map)-> select ();

Copy the code '_ multi' => true must be added to the end of the array, indicating that the current multi-condition match is used, so that the query condition becomes status = 1 AND title = 'thinkphp ', more query fields are supported, for example:
$ Map ['status & score & title'] = array ('1', array ('GT ', '0'), 'thinkphp ', '_ multi' => true );
The query condition changes to status = 1 AND score> 0 AND title = 'thinkphp'

Note: "|" and "&" cannot be used at the same time in the quick query method.The where method for interval query supports interval query for a field, for example:

1. $ Map ['id'] = array ('GT ', 1), array ('Lt', 10 ));

The query conditions obtained by copying the code are: ('id'> 1) AND ('id' <10)

1. $ Map ['id'] = array ('GT ', 3), array ('Lt', 10), 'OR ');

The query conditions obtained by copying the code are: ('id'> 3) OR ('id' <10)

1. $ Map ['id'] = array ('neq', 6), array ('GT ', 3), 'and ');

The query condition obtained by copying the code is: ('id '! = 6) AND ('id'> 3)
The last one can be the AND, OR, or xor operator. If no value is specified, it is the AND operation by default.
The condition of the interval query can support all expressions of a common query, that is, expressions LIKE, GT, and EXP are supported. In addition, range query supports more conditions, as long as the conditions for a field can be written together, for example:

1. $ Map ['name'] = array ('like', '% a %'), array ('like', '% B % '), array ('like', '% c %'), 'thinkphp', 'or ');

The final query condition of the copied code is:

1. ('Name' LIKE '% a %') OR ('name' LIKE '% B %') OR ('name' LIKE '% c % ') OR ('name' = 'thinkphp ')

The combined query by copying code is used for complex query conditions. if you need to use strings occasionally during query but do not want to lose the flexibility of the array method, you can use the combined query.
The subject of the combined query is still queried in array mode, but some special query support is added, including string mode query (_ string) and composite query (_ complex), request string query (_ query). only one special query can be defined for each query in a hybrid query. because of the array index method, special queries with the same index will be overwritten.
I. string mode query(Use _ string as the query condition)
Array conditions can also be used together with string conditions, for example:

1. $ User = M ("User"); // instantiate the User object
2. $ Map ['id'] = array ('neq', 1 );
3. $ Map ['name'] = 'OK ';
4. $ Map ['_ string'] = 'status = 1 AND score> 10 ';
5. $ User-> where ($ map)-> select ();

The query condition obtained after the code is copied is:
('Id '! = 1) AND ('name' = 'OK') AND (status = 1 AND score> 10)

II. request string query method
Request string query is similar to passing parameters through a URL. it supports simple equality criteria.

1. $ Map ['id'] = array ('GT ', '123 ');
2. $ Map ['_ query'] = 'status = 1 & score = 100 & _ logic = or ';

The query condition obtained by copying the code is: 'id'> 100 AND ('status' = '1' OR 'score '= '20140901 ')

III. Composite query
A composite query encapsulates a new query condition and is then incorporated into the original query condition. Therefore, complex query condition assembly can be completed.
For example:

1. $ Where ['name'] = array ('like', '% thinkphp % ');
2. $ Where ['title'] = array ('like', '% thinkphp % ');
3. $ Where ['_ logic'] = 'or ';
4. $ Map ['_ complex'] = $ where;
5. $ Map ['id'] = array ('GT ', 1 );

The query condition for copying code is:
(Id> 1) AND (name like '% thinkphp %') OR (title like '% thinkphp % '))
The composite query uses _ complex as the subquery condition. in combination with the previous query method, you can flexibly define more complex query conditions.
Many query methods can be converted to each other. for example, the preceding query conditions can be changed:

1. $ Where ['id'] = array ('GT ', 1 );
2. $ Where ['_ string'] =' (name like "% thinkphp %") OR (title like "% thinkphp ")';

The SQL statements generated after the code is copied are consistent. Multiple calls start with version 3.1.3. The where method supports multiple calls, but the string condition can only appear once. for example:

1. $ Map ['A'] = array ('GT ', 1 );
2. $ Where ['B'] = 1;
3. $ Model-> where ($ map)-> where ($ where)-> where ('status = 1')-> select ();

The array condition expressions that copy the code multiple times are merged, but the string condition is only supported once.