Unknown column in 'order clause'

Need Help
1

Hi all,

I have an app tracking training courses for volunteers. There are a number of mandatory training courses that must be completed every two years, and I need to track completion/compliance on this requirement. Based on a classification, not all members need to complete all courses.

There is a Members table and a TrainingCourses table, and finally a MemberTrainingCourses that tracks the course a member completed along with completion date and expiry date so we know when the course needs to be re-sat.

Member has many MemberTrainingCourses

        $this->hasMany('MemberTrainingCourses', [
            'dependent' => true,
            'foreignKey' => 'member_id'
        ]);

MemberTrainingCourse belong to Members and TrainingCourses

        $this->belongsTo('Members', [
            'foreignKey' => 'member_id',
            'joinType' => 'INNER',
        ]);
        $this->belongsTo('TrainingCourses', [
            'foreignKey' => 'training_course_id',
            'joinType' => 'INNER',
        ]);

I’ve created a report that gives me members and the courses they’ve done

$members = $this->Members->find()
    ->contain([
        'MemberTrainingCourses' => [
            'queryBuilder' => function($q) use($training_course_selected) {
            return $q
                ->contain('TrainingCourses')
                ->where(['training_course_id' => $training_course_selected])
                ->order('date_of_expiry DESC')
                ->limit(1);
        }]
    ]);

Because they resit every two years and there might be multiple entries per member, this will return all relevant members and their most recently completed course.

The report all works fine, however when I try to sort on course name or date_of_expiry I get

Column not found: 1054 Unknown column 'MemberTrainingCourses.date_of_expiry' in 'order clause'

I’m guessing this is because some Members returned have no relevant Training Courses (these are the people who need reminding/following up), and the associated record for this member is empty?

Everything else is working for me - just the sorting on the fields doesn’t.

Thanks in advance!

2

->where([‘.MemberTrainingCourses.training_course_id’ => $training_course_selected])

for short answer use this (MemberTrainingCourses.training_course_id),

the query will be confuse id the tables have the same column name you must include the table name then column.

Then if you have a nested columns

$members = $this->Members->find()
->contain([
‘MemberTrainingCourses’ => [
‘queryBuilder’ => function($q) use($training_course_selected) {
return $q
->contain(‘TrainingCourses’)
->where([‘training_course_id’ => $training_course_selected])
->order(‘date_of_expiry DESC’)
->limit(1);
},
‘MemberTrainingCourses.TrainingCourses’ => [
‘queryBuilder’ => function($q) use($training_course_selected) {
return $q->find(‘all’);
}]
]);

preferly use this

if you have more to use please read the documentation about loadinf association

Query Builder - 4.x (cakephp.org)

3

Sorry if not clear, the sorting I was referring to was in the Paginator. I set the sortableFields but when I test, the fields related to the MemberTrainingCourses return the error in the subject

            $this->paginate = [
                'order' => [
                    'membership_number' => 'ASC'],
                'sortableFields' => [
                    'MemberTrainingCourses.training_course_id',
                    'MemberTrainingCourses.date_of_completion',
                    'MemberTrainingCourses.date_of_expiry',

<?= $this->Paginator->sort('MemberTrainingCourses.training_course_id', 'Course Name') ?>
<?= $this->Paginator->sort('MemberTrainingCourses.date_of_completion', 'Date of Completion') ?>
<?= $this->Paginator->sort('MemberTrainingCourses.date_of_expiry', 'Date of Expiry') ?>

I’m guessing the error is because not all Member records returned have a MemberTrainingCourse (the purpose here is to find people who haven’t completed or renewed training), and when I look at the Sql Log it’s two separate SELECTS, one that gets the Members and one that gets MemberTrainingCourses.

4

If you need to have all the fields in a single query, you’ll need to force a join instead of (or perhaps in addition to) the containment.

5

use the MemberTrainingCourses as your main table then contain or join the parent tables