In the recipe, Performing GROUP and COUNT queries, we learnt how to add computed SQL expressions to a find
operation. Some of these expressions may be needed regularly for a model, introducing the need for virtual fields.
Using virtual fields, we get the resulting values of our SQL expressions as if they were real fields of our models. They allow us to get the same results shown in the previous recipe in a much more transparent way, without needing the override of afterFind
.
We need some sample models and data to work with. Follow the Getting ready section of the recipe, Performing GROUP and COUNT queries.
Open the Post
model and add the virtualfields
definition shown as follows:
<?php
class Post extends AppModel {
public $belongsTo = array('Blog'),
public $virtualFields = array(
'period' => 'CONCAT(YEAR(Post.created), '-', MONTH(Post.created))',
'total' => 'COUNT(*)'
);
}
?>
To obtain a count of all the posts per blog, grouped by the period they were created, we do:
$data = $this->Post->find('all', array( 'fields' => array( 'period', 'total', 'Blog.*' ), 'group' => array('Blog.id', 'period') ));
Using our sample data, the preceding query will result in the following array structure, which is exactly the same result as the one obtained in the last example shown in the recipe Performing GROUP and COUNT queries:
array( array( 'Blog' => array( 'id' => 1, 'user_id' => 1, 'name' => 'John Doe's Blog' ), 'Post' => array( 'period' => '2010-4', 'total' => 4 ) ), array( 'Blog' => array( 'id' => 1, 'user_id' => 1, 'name' => 'John Doe's Blog' ), 'Post' => array( 'period' => '2010-5', 'total' => 1 ) ), array( 'Blog' => array( 'id' => 2, 'user_id' => 2, 'name' => 'Jane Doe's Blog' ), 'Post' => array( 'period' => '2010-10', 'total' => 1 ) ), array( 'Blog' => array( 'id' => 2, 'user_id' => 2, 'name' => 'Jane Doe's Blog' ), 'Post' => array( 'period' => '2010-4', 'total' => 1 ) ) array( 'Blog' => array( 'id' => 2, 'user_id' => 2, 'name' => 'Jane Doe's Blog' ), 'Post' => array( 'period' => '2010-6', 'total' => 1 ) ) )
Virtual fields are always obtained when issuing a find
operation on the model. The only real way to avoid including them is specifying a list of fields to obtain in the find, and omitting the virtual fields:
$data = $this->Post->find('all', array( 'fields' => array_keys($this->Post->schema()) ));
The schema()
model function returns the list of real fields in the model, with information about each field, such as data type, and length.
We will now add a way for us to manage which virtual fields, if any, are returned. To do so, we override the beforeFind()
and afterFind()
model methods. If you don't have one already, create a file named app_model.php
in your app/
folder. Make sure your AppModel
class includes the following contents:
<?php class AppModel extends Model { public function beforeFind($query) { if (!empty($this->virtualFields)) { $virtualFields = isset($query['virtualFields']) ? $query['virtualFields'] : array_keys($this->virtualFields); if ($virtualFields !== true) { $this->_backVirtualFields = $this->virtualFields; $this->virtualFields = !empty($virtualFields) ? array_intersect_key($this->virtualFields, array_flip((array) $virtualFields)) : array(); } } return parent::beforeFind($query); } public function afterFind($results, $primary = false) { if (!empty($this->_backVirtualFields)) { $this->virtualFields = $this->_backVirtualFields; } return parent::afterFind($results, $primary); } } ?>
If we want to disable virtual fields when issuing a find
operation, we can easily do so by specifying the virtualFields
find setting to false
. We can also set it to the list of virtual fields we want to include. For example, to only include the period
virtual field, we do:
$person = $this->Post->find('all', array(
'virtualFields' => array('period')
));
CakePHP treats virtual fields almost as if they were real model fields. They are not exactly like real fields because we cannot specify a value for a virtual field when creating / editing a model record. However, in regards to find
operations, they are treated like any other field.
Virtual fields are included on every find
operation performed against the model to which they belong. However, there are times were we don't want or need certain virtual fields. This is particularly important when we include virtual fields that depend on grouping expressions, such as COUNT
, as they affect the number of rows returned. In these cases, we want to be able to specify what, or even if, virtual fields should be returned.
To allow us to control virtual fields returned from a find
operation, we add a new find setting by overriding the beforeFind
and afterFind
model callbacks. In the beforeFind
callback, executed before a find
operation is executed, we check for the existence of a virtualFields
setting. If such setting is defined, we use its value to check if virtual fields should be returned or not.
Based on these setting values, we alter the real value of the model virtualFields
property. We backup its original value, and then restore it after the find
operation is completed, that is, in the afterFind
callback.
18.223.108.119