Home >Backend Development >PHP Tutorial >CakePHP 2.x CookBook Chinese Version Chapter 7 Model - Retrieve Data_PHP Tutorial
CakePHP 2.x CookBook Chinese Version Chapter 7 Model - Retrieve Data_PHP Tutorial
- WBOYOriginal
- 2016-07-14 10:08:321093browse
Retrieve data
As mentioned before, one role of the model layer is to obtain data from various storages. CakePHP model classes come with a lot of functionality to help you search, sort, paginate and filter this data. Many of the functions you will use are integrated into the model's Model::find()
find
find(string $type = 'first', array $params = array())
Find is the workhorse of all model data retrieval functions. $type can be 'all', 'first', 'count', 'list', 'neighbors', 'threaded' or any custom search type. Remember, $type is case-sensitive. Using uppercase letters (such as All) will not produce the desired results.
$params is used to pass all parameters to different find. It has the following key values by default - each one is optional:
1 array(
2 'conditions' => array('Model.field' => $thisValue), //array of conditions
3 'recursive' => 1, //int
4 'fields' => array('Model.field1', 'DISTINCT Model.field2'), //array of field names
5 'order' => array('Model.created', 'Model.field3 DESC'), //string or array defining order
6 'group' => array('Model.field'), //fields to GROUP BY
7 'limit' => n, //int
8 'page' => n, //int
9 'offset' => n, //int
10 'callbacks' => true //other possible values are false, 'before', 'after'
11 )
You can also add and use other parameters for lookup types, behaviors, and your own model methods.
find(‘first’)
find('first', $params) returns a result, you can use it in any situation where you expect a result. Here are a few simple (controller code) examples:
1 public function some_function() {
2 // ...
3 $semiRandomArticle = $this->Article->find('first');
4 $lastCreated = $this->Article->find('first', array(
5 'order' => array('Article.created' => 'desc')
6 ));
7 $specificallyThisOne = $this->Article->find('first', array(
8 'conditions' => array('Article.id' => 1)
9 ));
10 // ...
11 }
In the first example, no arguments are passed to find - so there are no conditions or sorting. This form of find('first') call returns the following format:
1 Array
2 (
3 [ModelName] => Array
4 (
5 [id] => 83
6 [field1] => value1
7 [field2] => value2
8 [field3] => value3
9 )
10
11 [AssociatedModelName] => Array
12 (
13 [id] => 1
14 [field1] => value1
15 [field2] => value2
16 [field3] => value3
17 )
18 )
find(‘count’)
find('count', $params) returns an integer value. Here are a few simple (controller code) examples:
1 public function some_function() {
2 // ...
3 $total = $this->Article->find('count');
4 $pending = $this->Article->find('count', array(
5 'conditions' => array('Article.status' => 'pending')
6 ));
7 $authors = $this->Article->User->find('count');
8 $publishedAuthors = $this->Article->find('count', array(
9 'fields' => 'DISTINCT Article.user_id',
10 'conditions' => array('Article.status !=' => 'pending')
11 ));
12 // ...
13}
Annotations
Don’t pass the fields array to find('count'). You can only specify columns for DISTINCE count (otherwise the count result is always the same - just depends on the condition).
find(‘all’)
find('all', $params) returns an array (possibly multiple) of results.
Actually, it's all variants of find() (including pagination). Here are a few simple (controller code) examples:
1 public function some_function() {
2 // ...
3 $allArticles = $this->Article->find('all');
4 $pending = $this->Article->find('all', array(
5 'conditions' => array('Article.status' => 'pending')
6 ));
7 $allAuthors = $this->Article->User->find('all');
8 $allPublishedAuthors = $this->Article->User->find('all', array(
9 'conditions' => array('Article.status !=' => 'pending')
10 ));
11 // ...
12}
Annotations
In the above example, $allAuthors will contain every user in the users table. No conditions are passed to that find to apply.
The result format of calling find('all') is as follows:
1 Array
2 (
3 [0] => Array
4 (
5 [ModelName] => Array
6 (
7 [id] => 83
8 [field1] => value1
9 [field2] => value2
10 [field3] => value3
11 )
12
13 [AssociatedModelName] => Array
14 (
>
16 [field1] => value1
17 [field2] => value2
18 [field3] => value3
19 )
20
21 )
22 )
find(‘list’)
find('list', $params) returns an indexed array, useful when you want a list for something like a select element in an HTML input form. Here are a few simple (controller code) examples:
1 public function some_function() {
2 // ...
3 $allArticles = $this->Article->find('list');
4 $pending = $this->Article->find('list', array(
5 'conditions' => array('Article.status' => 'pending')
6 ));
7 $allAuthors = $this->Article->User->find('list');
8 $allPublishedAuthors = $this->Article->find('list', array(
9 'fields' => array('User.id', 'User.name'),
10 'conditions' => array('Article.status !=' => 'pending'),
11 'recursive' => 0
12 ));
13 // ...
14}
Annotations
In the above example, $allAuthors will contain every user in the users table. No conditions are passed to that find to apply.
The result format of calling find('list') is as follows:
1 Array
2 (
3 //[id] => 'displayValue',
4 [1] => 'displayValue1',
5 [2] => 'displayValue2',
6 [4] => 'displayValue4',
7 [5] => 'displayValue5',
8 [6] => 'displayValue6',
9 [3] => 'displayValue3',
10 )
When calling find('list'), the fields parameter passed is used to determine what to use for grouping the keys, values, and (optionally) results of the array. By default, the model's primary key is used as the key and the display column is used as the value (can be configured with the model's displayField) attribute)
Some clear examples:
1 public function some_function() {
2 // ...
3 $justusernames = $this->Article->User->find('list', array(
4 'fields' => array('User.username')
5 ));
6 $usernameMap = $this->Article->User->find('list', array(
7 'fields' => array('User.username', 'User.first_name')
8 ));
9 $usernameGroups = $this->Article->User->find('list', array(
10 'fields' => array('User.username', 'User.first_name', 'User.group')
11 ));
12 // ...
13}
In the above example, the result variable looks like this:
1 $justusernames = Array
2 (
3 //[id] => 'username',
4 [213] => 'AD7six',
5 [25] => '_psychic_',
6 [1] => 'PHPNut',
7 [2] => 'gwoo',
8 [400] => 'jperras',
9 )
10
11 $usernameMap = Array
12 (
13 //[username] => 'firstname',
14 ['AD7six'] => 'Andy',
15 ['_psychic_'] => 'John',
16 ['PHPNut'] => 'Larry',
17 ['gwoo'] => 'Gwoo',
18 ['jperras'] => 'Joël',
19 )
20
21 $usernameGroups = Array
22 (
23 ['User'] => Array
24 (
25 ['PHPNut'] => 'Larry',
26 ['gwoo'] => 'Gwoo',
27 )
28
29 ['Admin'] => Array
30 (
31 ['_psychic_'] => 'John',
32 ['AD7six'] => 'Andy',
33 ['jperras'] => 'Joël',
34 )
35
36 )
find(‘threaded’)
find('threaded', $params) returns a nested array, if you want to use the parent_id column of the model data to build the corresponding nested results. Here are a few simple (controller code) examples:
1 public function some_function() {
2 // ...
3 $allCategories = $this->Category->find('threaded');
4 $comments = $this->Comment->find('threaded', array(
5 'conditions' => array('article_id' => 50)
6 ));
7 // ...
8 }
Tips
A better way to handle nested data is to use the tree behavior
In the above example, $allCategories will contain a nested array that represents the entire category structure. The result format of calling find('threaded') is as follows:
1 Array
2 (
3 [0] => Array
4 (
5 [ModelName] => Array
6 (
7 [id] => 83
8 [parent_id] => null
9 [field1] => value1
10 [field2] => value2
11 [field3] => value3
12 )
13
14 [AssociatedModelName] => Array
15 (
16 [id] => 1
17 [field1] => value1
18 [field2] => value2
19 [field3] => value3
20 )
21
22 [children] => Array
23 (
24 [0] => Array
25 (
26 [ModelName] => Array
27 (
>
> 29
30 [field1] => value1
31 [field2] => value2
32 [field3] => value3
33 )
34
35 [AssociatedModelName] => Array
36 (
> 37
38 [field1] => value1
39 [field2] => value2
40 [field3] => value3
41 )
42
43 [children] => Array
44 (
45 )
46 )
47 ...
48 )
49 )
50 )
The order in which results are presented can be changed because it is affected by order processing. If you pass 'order' => 'name ASC' as an argument to find('threaded'), the results will be sorted by name . Any order similar to this can be used, this method has no built-in ordering of the top results returned first.
Warning
If fields are specified, parent_id (or its current alias) must be included:
1 public function some_function() {
2 $categories = $this->Category->find('threaded', array(
3 'fields' => array('id', 'name', 'parent_id')
4 ));
5}
Otherwise, the array returned in the above example will not be the expected nested structure.
find(‘neighbors’)
find('neighbors', $params) performs the same search as 'first' but returns the row before and after the request. Here's a simple (controller code) example: :
1 public function some_function() {
2 $neighbors = $this->Article->find('neighbors', array('field' => 'id', 'value' => 3));
3}
In this example, the $params array contains two elements: field and value. All other elements in the find are still available (e.g. if the model is containable, you can specify 'includes' in $params). The result format of calling find('neighbors') is as follows:
1 Array
2 (
3 [prev] => Array
4 (
5 [ModelName] => Array
6 (
7 [id] => 2
8 [field1] => value1
9 [field2] => value2
10 ...
11 )
12 [AssociatedModelName] => Array
13 (
14 [id] => 151
15 [field1] => value1
16 [field2] => value2
17 ...
18 )
19 )
20 [next] => Array
21 (
22 [ModelName] => Array
23 (
24 [id] => 4
25 [field1] => value1
26 [field2] => value2
27 ...
28 )
29 [AssociatedModelName] => Array
30 (
31 [id] => 122
32 [field1] => value1
33 [field2] => value2
34 ...
35 )
36 )
37 )
Annotations
Note that the result always contains only two root elements: prev and next. This function does not honor the model's default recursive variables. Recursive settings must be passed as parameters to each required call.
Create a custom find type
The find method is flexible and can accept custom searches, which is done by defining your own types in the model variables and implementing specific functions in the model class.
The model's find type is a shortcut for the find option. For example, the following two searches are the same:
1 $this->User->find('first');
1 $this->User->find('all', array('limit' => 1));
The following are the predefined types in the kernel:
first
all
count
list
threaded
neighbors
What about other types? Take, for example, finding all published articles in the database. Each change is made by adding the type to the Model::$findMethods variable in the model:
1 class Article extends AppModel {
2 public $findMethods = array('available' => true);
3}
This tells CakePHP to accept the value available as the first parameter of the find function. The second step is to implement the _findAvailable function. This is a convention. If you want to implement a search called myFancySearch, you need to implement a method called _findMyFancySearch.
1 class Article extends AppModel {
2 public $findMethods = array('available' => true);
3
4 protected function _findAvailable($state, $query, $results = array()) {
5 if ($state == 'before') {
6 $query['conditions']['Article.published'] = true;
7 return $query;
8 }
9 return $results;
10 }
11 }
Here is the complete example (controller code):
1 class ArticlesController extends AppController {
2
3 // Will find all published articles and order them by the created column
4 public function index() {
5 $articles = $this->Article->find('available', array(
6 'order' => array('created' => 'desc')
7 ));
8 }
9
10}
The specific _find[Type] method in the code shown above receives 3 parameters. The first one means when the query is executed, which can be before or after. This is because this function is a callback function that has the ability to edit the query before completion, or edit the results after they are fetched.
Usually the first thing is to check the query status of the find function. The before state is when you edit the query, bind new relationships, apply additional behaviors, and interpret the special keys passed to find as the second argument. This state requires the $query parameter to be returned (modified or not).
The after state is to check the result, inject new data, calculate and return it in another format, or do whatever you like on the most recently returned data. This state requires the $result array to be returned (modified or not).
Create as many custom lookups as you like, which is also a great way to enlist code across models in your application.
You can also paginate your searches with the following types of customization:
1 class ArticlesController extends AppController {
2
3 // All published articles will be paginated
4 public function index() {
5 $this->paginate = array('available');
6 $articles = $this->paginate();
7 $this->set(compact('articles'));
8 }
9
10}
Setting the $this->paginate attribute in the controller as above will cause the type of find to become available, and also allows you to continue to modify the results of the find.
If the paging count is wrong, you may need to add the following code to the AppModel, which can correct the paging count:
1 class AppModel extends Model {
2
3 /**
4 * If the 'fields' key in the count query of the custom search is an array, delete it,
5 * because it will completely break the Model::_findCount() call
6 *
7 * @param string $state Either "before" or "after"
8 * @param array $query
9 * @param array $results
10 * @return int The number of records found, or false
11 * @access protected
12 * @see Model::find()
13*/
14 protected function _findCount($state, $query, $results = array()) {
15 if ($state === 'before') {
16 if (isset($query['type']) && isset($this->findMethods[$query['type']])) {
17 $query = $this->{'_find' . ucfirst($query['type'])}('before', $query);
18 if (!empty($query['fields']) && is_array($query['fields'])) {
19 if (!preg_match('/^count/i', current($query['fields']))) {
20 unset($query['fields']);
21 }
22 }
23 } }
24 }
25 return parent::_findCount($state, $query, $results);
26 }
27
28}
29 ?>
Changed in version 2.2.
It is no longer necessary to override _findCount to correct errors in counting results. The 'before' state of custom lookups will now be re-invoked with $query['operation'] = 'count' . The returned $query will be used in _findCount(). If necessary you can identify and return a different $query by checking the 'operation' key:
1 protected function _findAvailable($state, $query, $results = array()) {
2 if ($state == 'before') {
3 $query['conditions']['Article.published'] = true;
4 if (!empty($query['operation']) && $query['operation'] == 'count') {
5 return $query;
6 }
7 $query['joins'] = array(
8 //array of required joins
9 );
10 return $query;
11 }
12 return $results;
13}
Magic Search Type
These magic functions are shortcuts for searching tables based on specific columns. Just add the column names (in camelCase format) to the end of these functions and provide the rules for those columns as their first argument.
The findAllBy() function returns results in a format similar to find('all'), while findBy returns a result in the same format as find('first').
findAllBy
findAllBy(string $value, array $fields, array $order, int $limit, int $page, int $recursive)
findAllBy Example Corresponding SQL fragment
$this->Product->findAllByOrderStatus('3'); Product.order_status = 3
$this->Recipe->findAllByType('Cookie'); Recipe.type = 'Cookie'
$this->User->findAllByLastName('Anderson'); User.last_name = 'Anderson'
$this->Cake->findAllById(7); Cake.id = 7
$this->User->findAllByEmailOrUsername('jhon'); User.email = 'jhon' OR User.username ='jhon';
$this->User->findAllByUsernameAndPassword('jhon', '123'); User.username = 'jhon' ANDUser.password = '123';
$this->User->findAllByLastName('psychic', array(),array('User.user_name => 'asc')); User.last_name = 'psychic' ORDER BYUser.user_name ASC
The format of the returned result array is similar to the return value format of find('all').
findBy
findBy(string $value);
The findBy magic function also accepts some optional parameters:
findBy(string $value[, mixed $fields[, mixed $order]]);
findBy Example Corresponding SQL fragment
$this->Product->findByOrderStatus('3'); Product.order_status = 3
$this->Recipe->findByType('Cookie'); Recipe.type = 'Cookie'
$this->User->findByLastName('Anderson'); User.last_name = 'Anderson';
$this->User->findByEmailOrUsername('jhon'); User.email = 'jhon' OR User.username = 'jhon';
$this->User->findByUsernameAndPassword('jhon','123'); User.username = 'jhon' AND User.password ='123';
$this->Cake->findById(7); Cake.id = 7
The findBy() function returns results similar to find('first').
Model::query()
query(string $query)
Although rarely necessary, if you can't or don't want to call SQL through other methods, you can use the model's query() method directly.
If you really want to use this approach in your application, make sure you read CakePHP's Data Cleansing, which helps clean user-supplied data to prevent injection and cross-site scripting attacks.
Annotations
query() ignores $Model->cacheQueries because its functionality is inherently unrelated to the calling model. To avoid caching the calling query, set the second parameter to false, for example: query($query, $cachequeries = false).
query() uses the table name in the query as the array key for the returned data, rather than the model name. For example:
1 $this->Picture->query("SELECT * FROM pictures LIMIT 2;");
may return the following array:
1 Array
2 (
3 [0] => Array
4 (
5 [pictures] => Array
6 (
7 [id] => 1304
8 [user_id] => 759
9 )
10 )
11
12 [1] => Array
13 (
14 [pictures] => Array
15 (
16 [id] => 1305
17 [user_id] => 759
18 )
19 )
20 )
To use the model name as the array key to be consistent with the return result of the find method, you can write the query as:
1 $this->Picture->query("SELECT * FROM pictures AS Picture LIMIT 2;");
will return:
1 Array
2 (
3 [0] => Array
4 (
5 [Picture] => Array
6 (
7 [id] => 1304
8 [user_id] => 759
9 )
10 )
11
12 [1] => Array
13 (
14 [Picture] => Array
15 (
16 [id] => 1305
17 [user_id] => 759
18 )
19 )
20 )
Annotations
This syntax and associative array structure are only valid in MySQL. Cake does not provide any abstract data when running queries manually, so the results will vary between databases.
Model::field()
field(string $name, array $conditions = null, string $order = null)
The return value is only the single column specified by $name in the first record that matches the sorting condition specified by $order. If no condition is passed and the model has an id set, the value of the column of the current model result will be returned. If there are no matching records, the lookup will return false.
1 $this->Post->id = 22;
2 echo $this->Post->field('name'); // echo the name for row id 22
3
4 echo $this->Post->field('name', array('created <' => date('Y-m-d H:i:s')), 'created DESC');
5 // Display the 'name' column of the last created instance.
Model::read()
read($fields, $id)
read() is a method used to set the current model data (Model::$data) - for example during editing - but cannot be used in other situations where a single record is fetched from the database
$fields is used to pass the name of a single column, which can be a string or an array of column names; if it is empty, all columns are obtained.
$id specifies the ID of the record to be read. The default is the currently selected record specified by Model::$id. Passing a different value to $id will cause the record to be selected.
read() always returns an array (even if it contains only one column name). Use field to get the value of a single column.
Warning
Since the read method overwrites any information stored in the model's data and id attributes, be very careful when using this function, especially when used in model callback functions like beforeValidata and beforeSave. Generally the find method provides a more powerful and easier-to-use API than the read method.
Complex search conditions
The find call of most models requires passing a condition set. Typically, CakePHP uses arrays to combine conditions that need to be placed in the WHERE clause of a SQL query.
Working with arrays is neat and readable, and building queries is very easy. This syntax also breaks up the elements in the query (columns, values, operations, and so on). It makes it possible for CakePHP to generate more efficient queries, ensuring that the SQL syntax is correct and each individual part of the query is correctly decomposed. Using array syntax also enables CakePHP to ensure that queries are safe from SQL injection attacks.
The most basic array-based query is similar to:
1 $conditions = array("Post.title" => "This is a post", "Post.author_id" => 1);
2 // Example with model
3 $this->Post->find('first', array('conditions' => $conditions));
This structure is very simple: it will find posts with a title equal to "This is a post". Note that we could use "title" as the column name, but it's best to always specify the model name when building queries, as it improves code clarity and helps prevent conflicts if you choose to change the schema.
What about other match types? Just as easy. Suppose you want to find all posts whose title is not This is a post:
1 array("Post.title !=" => "This is a post")
Note that ‘!=’ follows the column name. CakePHP can parse any valid SQL comparison operator, including match expressions using LIKE, BETWEEN, and REGEX, as long as you separate the column name and operator with spaces. IN(...) style matching exceptions. Suppose you want to find posts whose title is within a given set of values:
1 array(
2 "Post.title" => array("First post", "Second post", "Third post")
3 )
To perform a NOT IN(...) match to find posts whose title is not within the given set of values:
1 array(
2 "NOT" => array("Post.title" => array("First post", "Second post", "Third post"))
3 )
Adding additional filtering to a condition is as simple as adding additional key/value pairs to an array:
1 array (
2 "Post.title" => array("First post", "Second post", "Third post"),
3 "Post.created >" => date('Y-m-d', strtotime("-2 weeks"))
4 )
You can also create a lookup that compares two columns in the database:
1 array("Post.created = Post.modified")
The above example will return posts whose creation time and editing time are the same (that is, those posts that have never been edited).
Remember, if you find yourself unable to generate a WHERE clause in a method (e.g. a logical operation), you can always specify it using a string:
1 array(
2 'Model.field & 8 = 1',
3 // Other common conditions
4 )
By default, CakePHP uses logical AND to connect multiple conditions; that is, the snippet above only matches posts that were created two weeks ago and have a title that matches one of the given sets. But we can also easily find posts that meet any of the conditions:
1 array("OR" => array(
2 "Post.title" => array("First post", "Second post", "Third post"),
3 "Post.created >" => date('Y-m-d', strtotime("-2 weeks"))
4 ))
CakePHP accepts all valid SQL logical operations, including AND, OR, NOT, XOR, etc., and is not case sensitive. These conditions can be nested without restriction. Assume there is a belongsTo relationship between Posts and Authors, and you want to find all posts that contain a specific keyword ("magic") or were created two weeks ago, but only by Bob:
1 array(
2 "Author.name" => "Bob",
3 "OR" => array(
4 "Post.title LIKE" => "%magic%",
5 "Post.created >" => date('Y-m-d', strtotime("-2 weeks"))
6 )
7 )
If you need to set multiple conditions on the same column, for example, if you want to perform a LIKE search with multiple terms, you can use conditions similar to the following:
1 array('OR' => array(
2 array('Post.title LIKE' => '%one%'),
3 array('Post.title LIKE' => '%two%')
4 ))
Cake cannot check for null columns. In this example, the query will return all records with a non-null title:
1 array("NOT" => array(
2 "Post.title" => null
3 )
4 )
To handle BETWEEN queries, you can use the following conditions:
1 array('Post.read_count BETWEEN ? AND ?' => array(1,10))
Annotations
The numeric value that CakePHP will reference depends on the DB's column type.
GROUP BY?:
1 array(
2 'fields' => array(
3 'Product.type',
4 'MIN(Product.price) as price'
5 ),
6 'group' => 'Product.type'
7 )
The returned value format is as follows:
1 Array
2 (
3 [0] => Array
4 (
5 [Product] => Array
6 (
7 [type] => Clothing
8 )
9 [0] => Array
10 (
11 [price] => 32
12 )
13 )
14 [1] => Array
15 ...
Here is a simple example of executing a DISTINCT query. Other operators, such as MIN(), MAX(), etc., can be used in a similar format:
1 array(
2 'fields' => array('DISTINCT (User.name) AS my_column_name'),
3 'order' = >array('User.id DESC')
4 )
By nesting multiple condition arrays, very complex conditions can be constructed:
1 array(
2 'OR' => array(
3 array('Company.name' => 'Future Holdings'),
4 array('Company.city' => 'CA')
5 ),
6 'AND' => array(
7 array(
8 'OR' => array(
9 array('Company.status' => 'active'),
10 'NOT' => array(
11 array('Company.status' => array('inactive', 'suspended'))
12 )
13 )
14 )
15 )
16 )
The corresponding SQL query is:
1 SELECT `Company`.`id`, `Company`.`name`,
2 `Company`.`description`, `Company`.`location`,
3 `Company`.`created`, `Company`.`status`, `Company`.`size`
4
5 FROM
6 `companies` AS `Company`
7 WHERE
8 ((`Company`.`name` = 'Future Holdings')
9 OR
10 (`Company`.`name` = 'Steel Mega Works'))
11 AND
12 ((`Company`.`status` = 'active')
13 OR (NOT (`Company`.`status` IN ('inactive', 'suspended'))))
Subquery
The example below assumes we have a "users" table with "id", "name" and "status" columns. status can be "A", "B", "C". And we want to use a subquery to get all users whose status is different from "B".
To do this, we will get the model data source and make a request to it to build the query, just like we are calling the find method, except that a SQL statement will be returned. We then generate an expression and add it to the criteria array:
1 $conditionsSubQuery['"User2"."status"'] = 'B';
2
3 $db = $this->User->getDataSource();
4 $subQuery = $db->buildStatement(
5 array(
6 'fields' => array('"User2"."id"'),
7 'table' => $db->fullTableName($this->User),
8 'alias' => 'User2',
9 'limit' => null,
10 'offset' => null,
11 'joins' => array(),
12 'conditions' => $conditionsSubQuery,
13 'order' => null,
14 'group' => null
15 ),
16 $this->User
17 );
18 $subQuery = ' "User"."id" NOT IN (' . $subQuery . ') ';
19 $subQueryExpression = $db->expression($subQuery);
20
21 $conditions[] = $subQueryExpression;
22
23 $this->User->find('all', compact('conditions'));
The generated SQL query is:
1 SELECT
2 "User"."id" AS "User__id",
3 "User"."name" AS "User__name",
4 "User"."status" AS "User__status"
5 FROM
6 "users" AS "User"
7 WHERE
8 "User"."id" NOT IN (
9 SELECT
10 "User2"."id"
11 FROM
12 "users" AS "User2"
13 WHERE
14 "User2"."status" = 'B'
15 )
Also, if you need to pass part of the raw SQL query above, data source **expressions** with raw SQL work for arbitrary parts of the query. www.2cto.com
Prepared statements
If you need more control over your query, you can use prepared statements. It allows you to talk directly to the database driver and pass any custom queries you need:
1 $db = $this->getDataSource();
2 $db->fetchAll(
3 'SELECT * from users where username = ? AND password = ?',
4 array('jhon', '12345')
5 );
6 $db->fetchAll(
7 'SELECT * from users where username = :username AND password = :password',
8 array('username' => 'jhon','password' => '12345')
9 );
Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：PHP type operator instanceof_PHP tutorialNext article：PHP type operator instanceof_PHP tutorial