Vaishak Vaishak - 3 months ago 10
MySQL Question

Group By Three columns doesn't work in CodeIgniter

I needed a result with Group By three columns and the SQL works perfectly without any issue. I'm facing the issue when I use it in the CodeIgniter framework; it doesn't execute the query. My Code and SQL are as follows.

Code

$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;


This works fine when the group_by has only two columns, like,

->group_by(['scr.course', 'trk.userid'])


What could be the reason?

Answer

In codeigniter group_by() works with two fields only.


$this->db->group_by()

Permits you to write the GROUP BY portion of your query:

$this->db->group_by(array("title", "date"));  // Produces: GROUP BY title, date

CI 3 group_by()


Edit 01

$query=$this->db->query("
        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;");

$result = $query->result_array();
return $result;