CakePHP: bind hasMany as hasOne

This is totally brilliant. I came across this marvel somewhere and adapted to my application. See, if you have a hasMany relation, you end up with (1) an extra query and (2) with a lot of data. I have a case where I just need the last (time of creation) row. So I basically want to bind that model in a hasOne relation where the one row is determined by an expression selecting a single row.

In my case, there is a model User that hasMany Visits. Here is the code snippet executed in the User model that allows me to get the last visit in a single SQL query.


public function beforeFind( $queryData ) {
$this->_bindRecentVisit();
return parent::beforeFind($queryData);
}

function _bindRecentVisit() {
if ( isset($this->hasOne['RecentVisit'])) { return; }
$dbo = $this->Visit->getDatasource();
$subQuery = String::insert("`RecentVisit`.`id` = (:q)", array(
'q'=>$dbo->buildStatement(array(
'fields' => array( String::insert(':sqVisit:eq.:sqid:eq', array('sq'=>$dbo->startQuote, 'eq'=>$dbo->endQuote))),
'table' => $dbo->fullTableName($this->Visit),
'alias' => 'Visit',
'limit' => 1,
'order' => array('Visit.created'=>'DESC'),
'group' => null,
'conditions' => array(
'Visit.user_id = User.id'
)
), $this->Visit)
));
$this->bindModel(array('hasOne'=>array(
'RecentVisit'=>array(
'className' => 'Visit',
'conditions' => array( $subQuery )
)
)),false);
}

I do not remember whose suggestion it was but I am thankful that I could find this elegant solution and adapt it to my needs. So here it is if you ever need to do something like this too.

Leave a Reply

Your email address will not be published. Required fields are marked *