Pagination and sortable fields with null

Need Help
1

I have a table of members and have an associated (OneToMany) table of training courses completed. The training courses have an expiry date.

I have a page that shows members who either have not completed the training course, or whose course is now expired.

In the Paginator Sort I cannot sort on the Training Course expiry date as the member may not have a record at all and it throws an error

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

Any way I can work around this?

2

To sort associated fields you need to set the fields in your ->paginate() call.

Something along the line of

        $result = $this->paginate($query, [
            'sortableFields' => [
                'MemberTrainingCourses.date_of_expiry',
                'name',
                'other_field',
                'modified',
                'created',
            ],
        ]);
3

Sorry - should have mentioned I’m still on 4.x, not sure if that makes a difference?

I’ve tried this, however because the main tables (“Members”) includes some records that don’t have any MemberTrainingCourses associated tables (this screen is to show those with expired, or simply no courses yet completed), when I sort it fails with the error above -

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

4

That’s weird. If you’re doing a left join, the column should be there but null. If you’re doing an inner join, such member records should not even be part of the result set. Are you sure that this is because some records have nulls, and not because of a complete lack of joining on the query? Try it with a database that doesn’t include any such members and see what happens.

Or show us your actual query code…

5

modified logic for a workaround - thanks for assistance anyway