Search code examples
cakephpcakephp-2.x

CakePHP 2 Models hasMany as hasOne

I am trying to do this as MVC / CakePHP 2 as possible so if my approach is the incorrect, I would love to know (still learning). I feel like what I am doing should happen in the model and less so in the controller (to follow the fat model skinny controller principals).

I have a hasMany relationship between two tables:

trainings hasMany days.

If I want all the days in a training, this setup works as expected.

But I want (in every instance of training) the first day in a training. My thought process was to setup a hasOne relationship in the Training model as follows:

public $hasOne = array(
    ...
    'FirstDay' => array(
      'className' => 'Day',
      'foreignKey' => 'training_id',
      'fields' => 'FirstDay.training_date',
      'order' => array('FirstDay.training_date ASC'),
    )
);

In essence training hasOne days as FirstDay.

I assumed that with this setup, if I call a Training object I will get the associated FirstDay.

Instead I get multiple entries for Training -- one for each instance of days for a given training. The SQL that gets output is as follows:

SELECT `Training`.`id`, `Training`.`course_id`, `Course`.`name`, ...  `FirstDay`.`training_date`
  FROM `tst`.`trainings` AS `Training`
  LEFT JOIN `tst`.`courses` AS `Course` ON (`Training`.`course_id` = `Course`.`id`)
  ...
  shortened for your benefit
  ...
  LEFT JOIN `tst`.`days` AS `FirstDay` ON (`FirstDay`.`training_id` = `Training`.`id`)
  WHERE 1 = 1 ORDER BY `FirstDay`.`training_date` ASC LIMIT 20

I was assuming that the hasOne would put a limit 1 instead of 20 in the above clause. Since it did not, I tried adding a 'limit' => 1 but that didn't work and the documentation does not mention that as an option in a hasOne relationship. I also do not understand why WHERE 1 = 1 is there but I figure it does not matter since it is a true statement that does not limit anything -- just seems like unnecessary lifting.

Solution

  • The relationship type hasOne is implemented with a LEFT JOIN, and therefore can't support LIMIT as an option, as it would affect the whole query (limiting not only Day but Training too).

    There are several approaches to your problem. The most simple is to define your association as hasMany, but setting 'limit'=>1.

    public $hasMany = array(
    'FirstDay' => array(
        'className' => 'Day',
        'foreignKey' => 'training_id',
        'fields' => 'FirstDay.training_date',
        'order' => array('FirstDay.training_date ASC'),
        'limit' => 1
    )
);

    Optionally, you then get rid of the extra numerical index [0] by using Hash::map() after the find():

    $this->Training->contain('FirstDay');
$trainings=$this->Training->find('all');

$trainings = Hash::map($trainings, "{n}", function($arr){
        $arr['FirstDay']=$arr['FirstDay'][0];
        return $arr;
    });

    For other possible options, see: