CODEWITHSUNDEEP
phpmysqlcodeigniter
Sachinthana Aluvihare
Sachinthana Aluvihare

Reputation: 59

Codeigniter group_by not working properly

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.

Upvotes: 2

Views: 814

Answers (3)

CrandellWS
CrandellWS

Reputation: 2804

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

Upvotes: 0

Thamer
Thamer

Reputation: 1954

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

Upvotes: 1

Kundan Prasad
Kundan Prasad

Reputation: 576

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

Upvotes: 1

Related Questions