MySQL get rows, but for any matching get the latest version

Question

I'm developing a CMS, and implementing versioning by adding a new entry to the database with the current timestamp.

My table is set up as follows:

id | page | section | timestamp | content

"Page" is the page being accessed, which is either the path to the page ($page_name below), or '/' (to indicate 'global' fields).

"Section" is the section of the page being edited.

I want to be able to select all rows for a given page, but each section should only be selected once, the one with the latest timestamp being selected.

I've tried using the following CodeIgniter Active Record code:

$this->db->select('DISTINCT(section), content');
$this->db->where_in('page', array('/', $page_name));
$this->db->order_by('timestamp', 'desc');
$query = $this->db->get('cms_content');

Which is producing the following SQL:

SELECT DISTINCT(section), `content`
FROM (`cms_content`)
WHERE `page` IN ('/', 'index.html') 
AND `enabled` = 1
ORDER BY `timestamp` desc

Which is returning both test rows (rows have all same fields except id, timestamp and content).

Any ideas as to where I'm going wrong?

Thanks!

Answer 1

DISTINCT is for all columns selected, and because "content" differs you will get two different rows.

You only want to order by timestamp and limit 1 because you always want the latest.

But may I suggest that you keep a cross reference to the "active" page? That way, you are able to revert to a previous revision without dumping the new ones.

Meaning:

page
----
id
info
active_page_id


page_revisions
--------------
id
page_id
content
timestamp
...

Meaning, you have one-to-many between page <-> page_revisions, aswell as a one-to-one between page and page_revisions to keep track of the "current" revision. With this approach you are able to just join in the active revision.

Answer 2

Your mistake is thinking that DISTINCT applies only to section - an easy mistake to make as the parentheses are misleading here. In fact the DISTINCT applies to the entire row whether or not you have parentheses. It is therefore best to omit the parentheses to avoid confusion.

Your problem is a classic 'max per group' problem. There are many, many ways to write this query and it is probably one of the most popular SQL questions on this site so you can search Stack Overflow to find ways to solve it. One way to get you started is to only select rows which hold the maximum timestamp for that section:

SELECT section, content
FROM cms_content T1
WHERE page IN ('/', 'index.html') 
AND enabled = 1
AND timestamp = (
    SELECT MAX(timestamp)
    FROM cms_content T2
    WHERE page IN ('/', 'index.html') 
    AND enabled = 1
    AND T1.section = T2.section
)

I'm sorry but I do not know how to convert this SQL code into CodeIgniter Active Record. If another user more familiar with Active Record wishes to use this as a starting point for their own answer, they are welcome.