Three-column group not working in CodeIgniter

Question

I need a result with a three-column group, and SQL works fine without any problems. I run into a problem when I use it as part of CodeIgniter; he does not fulfill the request. 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 group_by has only two columns, e.g.

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

What could be the reason?

Answer 1

In codeigniter, it group_by()works with only two fields.

---

$this->db->group_by()

Allows you to write the GROUP BY part of your request:

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

Ci 3 group_by()

---

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

Answer 2

.

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class xyz_model extends CI_Model{
     function __construct()
     {
          // Call the Model constructor
          parent::__construct();
     }

     function get_this_thing($month,$year) 
     {    $myQuery="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;";

          $query = $this->db->query($myQuery);

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

Answer 3

:

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