Getting the most recent post based on date

How do I go about displaying the most recent post when I have two tables, both containing a column called creation_date

This would be simple if all I had to do was get the most recent post based on posts created_on value however if a post contains replies I need to factor this into the equation. If a post has a more recent reply I want to get the replies created_on value but also get the posts post_id and subject.

The posts table structure:

CREATE TABLE `posts` (
  `post_id` bigint(20) unsigned NOT NULL auto_increment,
  `cat_id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `subject` tinytext NOT NULL,
  `comments` text NOT NULL,
  `created_on` datetime NOT NULL,
  `status` varchar(10) NOT NULL default 'INACTIVE',
  `private_post` varchar(10) NOT NULL default 'PUBLIC',
  `db_location` varchar(10) NOT NULL,
  PRIMARY KEY  (`post_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

The replies table structure:

CREATE TABLE `replies` (
  `reply_id` bigint(20) unsigned NOT NULL auto_increment,
  `post_id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `comments` text NOT NULL,
  `created_on` datetime NOT NULL,
  `notify` varchar(5) NOT NULL default 'YES',
  `status` varchar(10) NOT NULL default 'INACTIVE',
  `db_location` varchar(10) NOT NULL,
  PRIMARY KEY  (`reply_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

Here is my query so far. I've removed my attempt of extracting the dates.

$strQuery = "SELECT posts.post_id, posts.created_on, replies.created_on, posts.subject ";
$strQuery = $strQuery."FROM posts ,replies ";
$strQuery = $strQuery."WHERE posts.post_id = replies.post_id ";
$strQuery = $strQuery."AND posts.cat_id = '".$row->cat_id."'";

Answers


$strQuery = "
  SELECT posts.post_id, GREATEST(posts.created_on, replies.created_on)  AS latestDate, posts.subject
  FROM posts, replies
  WHERE posts.post_id = replies.post_id
  AND posts.cat_id = {$row->cat_id}
  GROUP BY posts.post_id
  ORDER BY latestDate DESC;
";

UPDATE: On second looks, the above is actually incorrect, as it will not include those posts which do not yet have any replies. The more correct way to do it is:

$strQuery = "
  SELECT posts.post_id, GREATEST(posts.created_on, replies.created_on) AS latestDate,
  FROM posts
  LEFT JOIN replies ON (posts.post_id = replies.post_id)
  WHERE posts.cat_id = {$row->cat_id}
  GROUP BY posts.post_id
  ORDER BY latestDate DESC
  LIMIT 0,1;
";

SELECT posts.post_id, posts.subject, replies.post_id

FROM posts LEFT JOIN replies ON post.post_id = replies.post_id

WHERE posts.cat_id = '$row->cat_id'

ORDER BY posts.post_id DESC, replies.post_id DESC

The one that has no replies will return NULL, and you can filter out using PHP on your output.


Get the most recent post, the most recent reply to a post, then get the most recent one of them.

This uses order by created_on desc limit 1 to only get the last item from a table. I'd suggest adding keys on created_on columns for both tables, and for post_id column in replies table.

Update: also cat_id needs indexing.

select * from (
select * from 
(
select p.post_id, p.created_on , 'post' as post_type
from posts p
where p.cat_id = '$row->cat_id'
order by p.created_on  desc limit 1
)post
union 
select * from 
(
select p.post_id, r.created_on , 'reply'
from posts p
inner join replies r on r.post_id = p.post_id
where p.cat_id = '$row->cat_id'
order by r.created_on  desc limit 1
)reply
)big order by big.created_on limit 1;

Need Your Help

FontAwesome version 3 vs. 4 icon rendering performance

html css icons google-chrome-devtools font-awesome

The FontAwesome authors say that icon rendering can be up to 30% faster in version 4. I'm wondering about the details of that number.

Error Trying to Parse a Generic Type whenType is TimeSpan

.net vb.net parsing generics type-conversion

I have the following code that I am using to try and parse a string to a generic type. In the instance I am using it fails when I try and parse to a TimeSpan. The input string is "12:34" which Parses