A couple years ago I developed something with CakePHP 2.2. In my Bill model I have a virtualField which computes whether a given bill is “done” already:
scheduled_payment = 1
&& (payed_beihilfe IS NOT NULL && payed_beihilfe > 0)
&& (payed_pkv IS NOT NULL && payed_pkv > 0)
This has worked fine until there arose the situation that the payed_beihilfe is always zero as this part does not take any more role. The information about the distribution is in the field Person.beihilfe_part which ranges from 0 to 100. If it is one of the extremes, I do not want to consider the amount there. So I first tried this:
scheduled_payment = 1
&& ((payed_beihilfe IS NOT NULL && payed_beihilfe > 0) || Person.beihilfe_part = 0)
&& ((payed_pkv IS NOT NULL && payed_pkv > 0) || Person.beihilfe_part = 100)
This works when one looks at a single Bill object. However, as soon as one looks at a Person, I get the SQL errors that are mentioned in the manual. Basically this says that one cannot have virtualFields that need a JOIN operation. This is a bummer as I really need to extend this “done” property that I have.
This is my Bill class:
class Bill extends AppModel {
public $displayField = 'amount';
public $virtualFields = array(
# XXX There is a limitation in the `virtualFields` that it cannot
# use any sort of `JOIN`. Therefore I have to make due with the
# data that is available in the `bills` table. This is a real
# kludge but it seems to work.
"done" =>
"scheduled_payment = 1
&& ((payed_beihilfe IS NOT NULL && payed_beihilfe > 0) || (person_id = 7 && date > '2016-06-01'))
&& (payed_pkv IS NOT NULL && payed_pkv > 0)"
);
public $validate = array(
'amount' => array(
"rule" => array("comparison", ">", 0),
"message" => "Der Rechnungsbetrag muss größer als € 0 sein."
)
);
public $belongsTo = array(
'Doctor' => array(
'className' => 'Doctor',
'foreignKey' => 'doctor_id',
'conditions' => '',
'fields' => '',
'order' => ''
),
'Person' => array(
'className' => 'Person',
'foreignKey' => 'person_id',
'conditions' => '',
'fields' => '',
'order' => ''
)
);
}
This is the Person model:
class Person extends AppModel {
public $displayField = 'name';
public $hasMany = array(
'Bill' => array(
'className' => 'Bill',
'foreignKey' => 'person_id',
'dependent' => false,
'conditions' => '',
'fields' => '',
'order' => '',
'limit' => '',
'offset' => '',
'exclusive' => '',
'finderQuery' => '',
'counterQuery' => ''
)
);
}
The querying happens for instance in the PeopleController::view function:
public function view($id = null) {
$this->Person->id = $id;
if (!$this->Person->exists()) {
throw new NotFoundException(__('Invalid person'));
}
$this->set('person', $this->Person->read(null, $id));
}
This will also draw in the related Bill objects and there it crashes as the Person table is not JOINed into the query that obtains the Bill objects related to the given Person. This happens implicitly within CakePHP, I just used the scaffolding.
I have not worked with CakePHP in quite a while as I have moved away from web development mostly. In a very small Django project I saw that the rows from the SQL tables get instantiated into real Python objects. It would be really easy to add a function or field there. In CakePHP everything is just stored in associative arrays. Therefore I do not really see how to add a complex function that computes this “done” property.
What is a sensible way to go about this?
Unfortunately there is no overly clean way to overcome this limitation. There is no Model.beforeFind event for associations, where one could modify the query, and the point where the queries for associated data are built, are impossible to hook in without completely reimplementing parts of the datasource logic (DboSource::generateAssociationQuery()).
I solved similar problems in the past by using custom datasources with an overriden buildStatement() method that would dispatch a Model.beforeBuildStatement event. That event would be dispatched for all queries, so associated models could listen to it and modify queries if necessary.
Something like this:
app/Model/Datasource/Database/AppMysql.php
<?php
App::uses('CakeEvent', 'Event');
App::uses('Mysql', 'Model/Datasource/Database');
class AppMysql extends Mysql
{
public function buildStatement($query, Model $Model)
{
$targetModel = $Model;
$linkedModel = $Model->{$query['alias']};
if ($linkedModel !== null) {
$targetModel = $linkedModel;
}
$event = new CakeEvent('Model.beforeBuildStatement', $this, array($query, $Model));
$targetModel->getEventManager()->dispatch($event);
if (is_array($event->result)) {
$query = $event->result;
}
return parent::buildStatement($query, $Model);
}
}
Then in the model one could add joins so that virtual fields have access to them. In your case it could be something along the lines of
class Bill extends AppModel
{
// ...
public function implementedEvents()
{
return parent::implementedEvents() + array(
'Model.beforeBuildStatement' => array(
'callable' => 'beforeBuildStatement',
'passParams' => true
)
);
}
public function beforeBuildStatement(array $query, Model $Model)
{
if ($Model->name === 'Person') {
$query['joins'][] = array(
'table' => 'persons',
'alias' => 'Person',
'type' => 'LEFT',
'conditions' => array(
'Person.id = Bill.person_id',
)
);
}
return $query;
}
}
If $Model is not the current model, this indicates that the current model is being queried as a part of an association query. This snippet would join in the persons table everytime the bill table is queried as a part of an association query for the Person model.
It should be noted that this example comes with possible pitfalls since the only condition is whether "parent model = Person model". You may need to implement further measures in order to avoid problems like duplicate/non-unique joins and stuff, depending on what's going on in your app.
See also