CODEWITHSUNDEEP
phpmysqlcodeigniter
Rehan Aslam
Rehan Aslam

Reputation: 241

CodeIgniter Active Record - Prevent duplicates, sorted by date

The tried code:

    $this->db->select('*');
    $this->db->from('documents_table');
    $this->db->order_by("last_modified_date", "desc");
    $this->db->group_by('document_id');

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

Table structure:

id , document_id , content , last_modified_date

Problem:

The design of the system means that when documents are updated, a actual new record is created with the same document_id and the updated content.

Now I need the documents displayed in a list, however if they are multiple documents with the same document_id (basically older versions of the file), only the latest version should be displayed. The current code outputs the first versions only, so this needs to be switched.

Is group_by the right method for this? Anyone with some experience can work out why its not working as intended, or am I just doing it wrong?

Upvotes: 1

Views: 538

Answers (2)

Santhosh Paloor
Santhosh Paloor

Reputation: 31

 $this->db->select('MAX(id) , document_id , content , last_modified_date');
 $this->db->from('documents_table');
 $this->db->order_by("last_modified_date", "desc");
 $this->db->group_by('document_id');

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

Untested

Upvotes: 0

Patrick
Patrick

Reputation: 922

$this->db->select('*');
$this->db->from('documents_table');
$this->db->order_by("last_modified_date", "desc");    
$this->db->where('id IN (SELECT MAX(id) FROM documents_table GROUP BY document_id)');

Give that one a try (untested).

Upvotes: 1

Related Questions