I am trying to get a list of all Visitors with only the latest visit information and the associated VisitType description for an API call and I need to be able to do this in one find. Previously we had the second code block below, but this returns multiple records for Visitors when the Visitor has more than one associated Visit record (one for each Visit Record). I just need to get the most recent (MAX(Visit.visit_date) but obviously cannot use this if we intend to join against VisitType) with it's associated VisitType.type_description.
The beforeFind() function is on the Visitor model since we are trying to get a list of Visitors.
I am getting the follow issue, when I tried to use the Containable code below:
Warning (512): Model "Site" is not associated with model "Site" [CORE/Cake/Model/Behavior/ContainableBehavior.php, line 343]
Warning (512): Model "SubjectStatus" is not associated with model "SubjectStatus" [CORE/Cake/Model/Behavior/ContainableBehavior.php, line 343]
I have these associations established:
Failing Containable code:
public function beforeFind($options, $primary=false) {
if ($this->__AjaxDataLoad) {
// Replacing a bunch of Model rebinds with Containable
$this->Behaviors->load('Containable');
$this->contain(array(
'Site' => array(
'conditions' => array('Site.id = Visit.site_id'),
'field' => array('Site.site_name')
),
‘VisitorStatus' => array(
'conditions' => array(‘VisitorStatus.id = Visitor.visit_status_id'),
'field' => array(‘VisitorStatus.status')
),
'Visit' => array(
'conditions' => array('Visit.subject_id = Visitor.id'),
'order' => 'Visit.visit_date DESC',
'limit' => 1,
'VisitType' => array(
'conditions' => array('Visit.visit_type_id = VisitType.id'),
'fields' => array('VisitType.type_description'),
)
)
));
}
}
Previously, the follow code "works" with the undesirable side-effect of including multiple rows per visitor, one row per Visit:
public function beforeFind($options, $primary=false) {
if ($this->__AjaxDataLoad) {
$this->unbindModelAll();
$this->bindModel(array(
'belongsTo' => array(
'Site' => array(
'className' => 'Site',
'foreignKey' => 'site_id',
),
‘VisitorStatus' => array(
'className' => ‘VisitorStatus',
'foreignKey' => ‘visitor_status_id',
)
),
'hasOne' => array(
'Visit' => array(
'className' => 'Visit',
'foreignKey' => ‘visitor_id',
'order' => 'Visit.visit_date DESC’,
‘limit’ => 1 // <—————————————— THIS DOES NOTHING
),
'VisitType => array(
'foreignKey' => false,
'conditions' => array("Visit.visit_type_id = VisitType.id"),
'fields' => array('VisitType.type_description’)
)
)
));
}
}
Is there something wrong with the Containable structure I am attempting?
I have configure Debug = 2, CakePHP version 2.2.5
The solution I ultimately came up with complete circumvents CakePHP relationship techniques. It came down to using virtualFields and a subquery as the last resort.
public function beforeFind($options) {
if ($this->__AjaxDataLoad) {
$options['joins'] = array(
array(
'table' => 'visits',
'alias' => 'Visit',
'type' => 'LEFT',
'conditions' => array('Visit.visitor_id = Visitor.id')
)
);
$options['group'] = array('Visitor.id');
if (is_null($options['fields'])) {
$options['fields'] = array(
'Visitor.*', 'Site.*', 'VisitorStatus.*'
);
}
$this->virtualFields['last_visit_date'] = 'MAX(Visit.visit_date)';
$this->virtualFields['last_visit_kind'] = 'SELECT visit_kinds.kind FROM visits INNER JOIN visit_kinds ON visits.visit_kind_id = visit_kinds.id WHERE visits.visitor_id=Visitor.id ORDER BY visits.visit_date DESC LIMIT 1';
}
return $options;
}