我需要一个按三列分组的结果,SQL没有任何问题地完美工作。但是,当我在CodeIgniter框架中使用它时,出现了问题,查询无法执行。以下是我的代码和SQL。
代码
$this->db->select(['trk.userid AS user_id', 'scr.course AS course_id'])
->from('mdl_scorm scr')
->join('mdl_scorm_scoes_track trk', 'scr.id = trk.scormid', 'inner')
->join('mdl_course_modules mcs', 'mcs.instance = scr.id', 'inner')
->where_in('trk.value', ['completed','incomplete','passed'])
->group_by(['scr.course', 'trk.userid', 'trk.scormid'])
->order_by('trk.userid', 'DESC');
SQL
SELECT `trk`.`userid` AS user_id, `scr`.`course` AS course_id
FROM (`mdl_scorm` scr)
INNER JOIN `mdl_scorm_scoes_track` trk ON `scr`.`id` = `trk`.`scormid` INNER JOIN `mdl_course_modules` mcs ON `mcs`.`instance` = `scr`.`id`
WHERE `trk`.`value` IN ('completed', 'incomplete', 'passed')
GROUP BY `scr`.`course`, `trk`.`userid`, `trk`.`scormid`
ORDER BY `trk`.`userid` DESC LIMIT 0,100;
当group_by只有两列时,如下所示,此方法可以正常工作:
->group_by(['scr.course', 'trk.userid'])
可能的原因是什么?
$result = $query->result();
或者$result = $query->row_array();
这两行代码。 - VaishakCI_DB_mysql_result Object ( [conn_id] => Resource id #2 [result_id] => Resource id #6 [result_array] => Array ( ) [result_object] => Array ( ) [custom_result_object] => Array ( ) [current_row] => 0 [num_rows] => 100 [row_data] => )
@Abdulla, @Drew - VaishakGROUP BY scr.course, trk.userid
时,结果约为25,而当我使用GROUP BY scr.course, trk.userid, trk.scormid
时,结果大约增加了一倍!! - Vaishak