I am creating an assessment database and using cakephp for the first time. I have gotten all the data entry and editing sorted but am having trouble retrieving some crucial data from a deep association:
The essence is
Course HABTM Units (table courses_units)
Unit hasMany Artefacts
Artefact hasOne AdminArtefact Artefact belongsTo an Unit
The AdminArtefact model is used for each years dynamic data for an artefact (due_date, late_date and so on).
My problem is when I want to get all the due dates of artefacts for a particular course (which we need to check for hand in congestion)
If I do a findAll in the Artefacts controller with recursion set to '2' it gets all the data I need...but it overloads the page due to the amount of data returned. So I want to set the conditions for the findAll for the specific fields I need. However I cannot set the course in the query - From my understanding the course data is pulled from a 'separate' query as it appears in the results like this:
[Course] => Array
(
[0] => Array
(
[id] => 2
[course_code] => C0767S
[course_name] => BSc (hons) Entertainment Technology
[course_local_code] => ET
[course_leader] => 6
[colour] =>
[CoursesUnit] => Array
so as part of the query, I cannot set the course equal to a specific one, which I need to do so I can also specify the other data elements I want to pull and not the wealth of other data.
Ive tried loading the Unit model and changing the bind to a belongsTo instead of HABTM to the courses_units table but got the same dataset back again.
Ive tried going 'higher up the chain' to the CourseUnits controller and doing a '$uses' and specified all the relevant tables I want to pull data from but that didn't get anything useful.
Reading up on HABTM it doesn't automatically pull the data so I looked at joining the tables, but couldn't really see which model to make the join to get it to work.
I need to put this to bed now so hence I am asking the question: What is the most elegant way to get the due dates for all the artefacts for a specific course? (I need the course, the unit, the artefact and the due date...so something from all the tables)
QUERY String I want to use in Artefacts controller:
$data = $this->Artefact->find('all', array(
'fields' => array('Artefact.reference', 'AdminArtefact.due_date', 'ArtefactUnit.short_code', 'Course.course_code' )));
This gives the error:
Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Course.course_code' in 'field list'
Thanks in advance!
Kevin
All relevant models:
class Course extends AppModel {
public $displayField = 'course_name';
public $hasAndBelongsToMany = array(
'Unit' =>
array(
'className' => 'Unit',
'joinTable' => 'courses_units',
'foreignKey' => 'course_id',
'associationForeignKey' => 'unit_id',
'unique' => true,
'conditions' => '',
'fields' => '',
'order' => '',
'limit' => '',
'offset' => '',
'finderQuery' => '',
'with' => 'CoursesUnit'
)
);
public $belongsTo = array(
'CourseLeader' => array(
'className' => 'User',
'foreignKey' => 'course_leader'
)
);
class Unit extends AppModel {
public $displayField = 'short_code';
public $hasMany = array(
'UnitArtefacts' => array(
'className' => 'Artefacts',
'foreignKey' => 'unit_id'
));
public $hasAndBelongsToMany = array(
'Course' =>
array(
'className' => 'Course',
'joinTable' => 'courses_units',
'foreignKey' => 'unit_id',
'associationForeignKey' => 'course_id',
'unique' => true,
'conditions' => '',
'fields' => '',
'order' => '',
'limit' => '',
'offset' => '',
'finderQuery' => '',
'with' => 'CoursesUnit'
)
);
public $belongsTo = array(
'External' => array(
'className' => 'User',
'foreignKey' => 'external_examiner'
),
'Coordinator' => array(
'className' => 'User',
'foreignKey' => 'coordinator'
),
'UnitLevel' => array(
'className' => 'Level',
'foreignKey' => 'level_id'
)
);
class Artefact extends AppModel {
public $displayField = 'reference';
public $hasOne = array(
'AdminArtefact' => array(
'className' => 'AdminArtefact',
'foreignKey' => 'artefact_id'
)
);
public $belongsTo = array(
'ArtefactUnit' => array(
'className' => 'Unit',
'foreignKey' => 'unit_id'
)
);
