I am logging the query and it's consistently the same.
Code:
$db['school_db']['dbdriver'] = 'mysqli';
$db['school_db']['dbprefix'] = '';
$db['school_db']['pconnect'] = FALSE;
$db['school_db']['db_debug'] = FALSE;
$db['school_db']['cache_on'] = FALSE;
$db['school_db']['cachedir'] = '';
$db['school_db']['char_set'] = 'utf8';
$db['school_db']['dbcollat'] = 'utf8_general_ci';
$db['school_db']['swap_pre'] = '';
$db['school_db']['autoinit'] = TRUE;
$db['school_db']['stricton'] = FALSE;
These are my connection options. This project was migrated from CI2 to CI3 (not sure the exact release, I can try and find out if relevant).
The exact query is:
Code:
select schedules.start_time as date, student_lesson_progress.lesson_status, largest_package_details.lesson_order
from (
select package_details.lesson_order, package_details.lesson_id, lessons.lesson_name, package_id from package_details
join lessons on lessons.lesson_id = package_details.lesson_id
where package_details.package_id in (select package_id from (
select package_id, max(package_size)
from (SELECT package_id, count(*) package_size FROM package_details group by package_id order by package_size desc, package_id asc) package_size) largest_package))
largest_package_details
left join student_lesson_progress on largest_package_details.lesson_id = student_lesson_progress.lesson_id
and student_lesson_progress.student_id = ? -- this id is the only argument, but for all of my testing I'm using the same value
left join schedules on schedules.schedule_id = student_lesson_progress.schedule_id
where student_lesson_progress.product_id > 0 or student_lesson_progress.product_id is null
order by largest_package_details.lesson_order;
Wrapped in PHP:
PHP Code:
$query = "the query in the code block above";
$args = array($student_id);
$query = $this->db->query($query,$args);
return $query->result_array();
I'm not particularly proud of this query, but essentially:
Lessons are templates for schedules that dictate what content will be covered. All schedules for a particular lesson will cover the same material.
A package is a named collection of lessons. The details of what lessons are included in a package are stored in package_details alongside what order the lessons should be in.
Schedules set a start and end time for lesson and can be assigned to one or more students curriculum (student_lesson_progress).
Line 2-8 find the package with the largest collection of lessons.
The lesson_order of the lessons in the largest package is important to the results of the query.
For a particular student, for each lesson in the largest package, find the lesson status (completed, invited, not scheduled) in the student_lesson_progress that corresponds to that lesson. If a student_lesson_progress is found, grab the start time from the schedule in student lesson progress.
In the rows that differ, lesson_order is maintained while date and lesson_status are null/0.
Code:
{"date":"2019-04-03 16:30:00","lesson_status":"3","lesson_order":"0","package_id":"1"}
{"date":"2019-04-10 16:30:00","lesson_status":"3","lesson_order":"1","package_id":"1"}
{"date":"2019-04-17 16:30:00","lesson_status":"3","lesson_order":"2","package_id":"1"}
{"date":"2019-04-24 16:30:00","lesson_status":"3","lesson_order":"3","package_id":"1"}
{"date":"2019-05-01 16:30:00","lesson_status":"3","lesson_order":"4","package_id":"1"}
{"date":"2019-05-15 19:00:00","lesson_status":"3","lesson_order":"5","package_id":"1"}
{"date":"2019-09-04 16:00:00","lesson_status":"3","lesson_order":"6","package_id":"1"}
{"date":"2019-09-18 16:00:00","lesson_status":"3","lesson_order":"7","package_id":"1"}
{"date":"2019-08-13 19:00:00","lesson_status":"3","lesson_order":"8","package_id":"1"}
{"date":"2019-10-02 18:00:00","lesson_status":"3","lesson_order":"9","package_id":"1"}
{"date":"2019-10-02 19:00:00","lesson_status":"3","lesson_order":"10","package_id":"1"}
{"date":"2019-10-16 19:00:00","lesson_status":"3","lesson_order":"11","package_id":"1"}
{"date":"2019-11-01 11:00:00","lesson_status":"3","lesson_order":"12","package_id":"1"}
{"date":"2019-11-01 12:00:00","lesson_status":"3","lesson_order":"13","package_id":"1"}
{"date":"2019-10-23 19:00:00","lesson_status":"3","lesson_order":"14","package_id":"1"}
{"date":"2019-12-04 18:00:00","lesson_status":"3","lesson_order":"15","package_id":"1"}
{"date":"2019-12-04 19:00:00","lesson_status":"3","lesson_order":"16","package_id":"1"}
{"date":"2020-02-17 19:00:00","lesson_status":"3","lesson_order":"17","package_id":"1"}
{"date":"2020-01-22 15:45:00","lesson_status":"3","lesson_order":"18","package_id":"1"}
{"date":"2020-01-22 17:00:00","lesson_status":"3","lesson_order":"19","package_id":"1"}
{"date":"2020-04-20 13:30:00","lesson_status":"3","lesson_order":"20","package_id":"1"}
{"date":"2020-07-17 09:00:00","lesson_status":"3","lesson_order":"21","package_id":"1"}
{"date":"2020-06-24 16:30:00","lesson_status":"3","lesson_order":"22","package_id":"1"}
{"date":"2020-06-24 17:30:00","lesson_status":"3","lesson_order":"23","package_id":"1"}
{"date":"2020-05-27 14:30:00","lesson_status":"3","lesson_order":"24","package_id":"1"}
{"date":"2020-07-17 10:00:00","lesson_status":"3","lesson_order":"25","package_id":"1"}
{"date":"2020-07-20 10:00:00","lesson_status":"3","lesson_order":"26","package_id":"1"}
