Codeigniter group_by not working properly

Question

Im trying get a list of userIds from the videos table. This table contains the media files that get uploaded the users. This is my code

        $this->db->select("videos.user_id as userId");
        $this->db->limit('10', $document['offset']);
        $this->db->group_by('userId');
        $this->db->order_by('id','desc');

        $recentUploads = $this->db->get('videos')->result_array();
        if (!empty($recentUploads)) 
        {
            foreach ($recentUploads as $record) 
            {
                $totalPostMedia = $obj->totalPostMedia($record);
                $record['totalPostMedia'] = $totalPostMedia;

                $resData = $this->db->select("username, profileImage")->from('users')->where('id', $record['userId'])->get()->row_array();
                $record['uploadBy'] = $resData['username'];
                $record['profileImage'] = "http:...com/profileImage/".$resData['profileImage'];

                $Mydata[] = $record;
            }
        }

The result get is missing some of the userIds from the table. I have tried $this->db->distinct() as well. Still got the same result. The only way i get a result with no duplicates is when i remove $this->db->order_by('id','desc'); or make it asc instead of desc. But i want to get the latest records from the table. how do i do this? Am i doing something wrong? any help would be much appreciated.

Write first--> $this->db->order_by('id','desc'); than this $this->db->group_by('userId'); — Anand Pandey, Jul 21 '17 at 12:42

score 1 · Answer 1 · answered Jul 21 '17 at 13:21

try this

$this->db->select("videos.user_id as userId");
$this->db->from("videos");
$this->db->group_by('userId');
$this->db->order_by('id','desc');
$this->db->limit('10', $document['offset']);

$recentUploads = $this->db->get()->result_array();
if (count($recentUploads)>0) 
{
    foreach ($recentUploads as $record) 
    {
        $totalPostMedia = $obj->totalPostMedia($record);
        $record['totalPostMedia'] = $totalPostMedia;

        $resData = $this->db->select("username, profileImage")->from('users')->where('id', $record['userId'])->get()->row_array();
        $record['uploadBy'] = $resData['username'];
        $record['profileImage'] = "http:...com/profileImage/".$resData['profileImage'];

        $Mydata[] = $record;
    }
}

score 1 · Answer 2 · answered Jul 21 '17 at 13:50

in your request (i mean select videos.user_id as userId) in the group_by ligne you make userId to do the group by traitement. your userId alias is not knowing as colum name that can do any traitement of it.

for that replace your userId by videos.user_id in your group by ligne.

your code will be like this to work for you

 $this->db->select("videos.user_id as userId");
    $this->db->limit('10', $document['offset']);
    $this->db->group_by('videos.user_id');
    $this->db->order_by('id','desc');

    $recentUploads = $this->db->get('videos')->result_array();
    if (!empty($recentUploads)) 
    {
        foreach ($recentUploads as $record) 
        {
            $totalPostMedia = $obj->totalPostMedia($record);
            $record['totalPostMedia'] = $totalPostMedia;

            $resData = $this->db->select("username, profileImage")->from('users')->where('id', $record['userId'])->get()->row_array();
            $record['uploadBy'] = $resData['username'];
            $record['profileImage'] = "http:...com/profileImage/".$resData['profileImage'];

            $Mydata[] = $record;
        }
    }

score 0 · Answer 3 · answered Oct 12 '18 at 06:31

use max(id)

as in $this->db->select("videos.user_id as userId,max(id)");`

so you might try:

    $this->db->select("videos.user_id as userId, max(id)");
    $this->db->limit('10', $document['offset']);
    $this->db->group_by('userId');
    $this->db->order_by('id','desc');

    $recentUploads = $this->db->get('videos')->result_array();
    if (!empty($recentUploads)) 
    {
        foreach ($recentUploads as $record) 
        {
            $totalPostMedia = $obj->totalPostMedia($record);
            $record['totalPostMedia'] = $totalPostMedia;

            $resData = $this->db->select("username, profileImage")->from('users')->where('id', $record['userId'])->get()->row_array();
            $record['uploadBy'] = $resData['username'];
            $record['profileImage'] = "http:...com/profileImage/".$resData['profileImage'];

            $Mydata[] = $record;
        }
    }

https://stackoverflow.com/a/14770936/1815624

Codeigniter group_by not working properly

3 Answers3