11 Replies - 10430 Views - Last Post: 21 January 2012 - 11:19 PM

#1 cancer10   User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 55
  • Joined: 12-July 08

MySQL: Forum teaser, how to get latest post date and author name?

Posted 15 January 2012 - 01:08 AM

Hello,

I am writing a small forum software and I am stuck to a feature where I need to show a summary of the forum's latest post title, latest post date, and latest post author.

So there is a

1) Thread table.
2) Comments table: This is related to the thread table with the cmnts_thr_id foreign key.
3) Users table.


The latest post date is to be identified by comparing the following 4 dates:
  • threads_tr.thr_date_created
  • threads_tr.thr_date_updated
  • comments_cmnts.cmnts_date_created
  • comments_cmnts.cmnts_date_updated


and whichever date is greater among the above 4, the summary should display that particular date along with the thread title and the user's name who has recently updated the thread or comment.

DDLs:

CREATE TABLE `threads_thr` (
  `thr_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `thr_usr_id` INT(10) DEFAULT NULL,
  `thr_title` VARCHAR(64) DEFAULT NULL,
  `thr_description` TEXT,
  `thr_date_created` DATETIME DEFAULT NULL,
  `thr_date_updated` DATETIME DEFAULT NULL,
  PRIMARY KEY (`thr_id`)
) ENGINE=MYISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1



CREATE TABLE `comments_cmnts` (
  `cmnts_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `cmnts_usr_id` INT(10) DEFAULT NULL,
  `cmnts_thr_id` INT(10) UNSIGNED DEFAULT NULL,
  `cmnts_message` TEXT,
  `cmnts_date_created` DATETIME DEFAULT NULL,
  `cmnts_date_updated` DATETIME DEFAULT NULL,
  PRIMARY KEY (`cmnts_id`)
) ENGINE=MYISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1


CREATE TABLE `users_usr` (
  `usr_id` INT(10) NOT NULL AUTO_INCREMENT,
  `usr_first_name` VARCHAR(66) NOT NULL,
  `usr_last_name` VARCHAR(66) NOT NULL,
  `usr_email_address` VARCHAR(255) DEFAULT NULL,
  `usr_password` VARCHAR(100) NOT NULL,
  `usr_date_created` DATETIME NOT NULL,
  `usr_date_updated` DATETIME DEFAULT NULL,
  PRIMARY KEY (`usr_id`),
  KEY `email_address` (`usr_email_address`)
) ENGINE=MYISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1




Any help is appreciated.


Thanks

This post has been edited by cancer10: 15 January 2012 - 01:09 AM


Is This A Good Question/Topic? 0
  • +

Replies To: MySQL: Forum teaser, how to get latest post date and author name?

#2 Atli   User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4241
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

Re: MySQL: Forum teaser, how to get latest post date and author name?

Posted 15 January 2012 - 06:14 AM

Hey.

First of all, I'd like to suggest avoiding using acronyms in table and column names. It just makes the database and the queries harder to read. Also there is usually little point in attaching the table name to all the columns in the table. You'll most likely end up using table aliases in your queries anyways, so this doesn't really do any good.

Your CREATE statements, I would write them like this:
CREATE TABLE `users` (
  `id` INT(10) NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(66) NOT NULL,
  `last_name` VARCHAR(66) NOT NULL,
  `email_address` VARCHAR(255) DEFAULT NULL,
  `password` VARCHAR(100) NOT NULL,
  `date_created` DATETIME NOT NULL,
  `date_updated` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `users_email_address` (`email_address`)
) ENGINE=MYISAM DEFAULT CHARSET=latin1;

CREATE TABLE `threads` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT(10) DEFAULT NULL,
  `title` VARCHAR(64) DEFAULT NULL,
  `description` TEXT,
  `date_created` DATETIME DEFAULT NULL,
  `date_updated` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MYISAM DEFAULT CHARSET=latin1;

CREATE TABLE `comments` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT(10) DEFAULT NULL,
  `thread_id` INT(10) UNSIGNED DEFAULT NULL,
  `message` TEXT,
  `date_created` DATETIME DEFAULT NULL,
  `date_updated` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MYISAM DEFAULT CHARSET=latin1;


Then instead of this list of columns you posted:
threads_tr.thr_date_created
threads_tr.thr_date_updated
comments_cmnts.cmnts_date_created
comments_cmnts.cmnts_date_updated


You would have this list:
threads.date_created
threads.date_updated
comments.date_created
comments.date_updated


Do you see what I mean?



OK then, on to the real problem. I see two methods you could use to solve it. The first involves a lot of CASE statements and derived tables, to figure out which dates and users to use; and the second involves using the UNION statement to construct a virtual table where all the dates are put into a single column you can sort using a single ORDER BY clause. - Personally I would go with the second method, because it's simpler. (And for some reason it looks more efficient to me. Not sure why though.)

The first thing you'd have to do is create the virtual table. It's important that all the SELECT statements return the column list in the proper order, or you won't get the desired results. It should look something like:
SELECT 
    id AS thread_id, user_id, 
    date_created AS latest_date
FROM threads
UNION
SELECT 
    id AS thread_id, user_id, 
    date_updated AS latest_date
FROM threads
UNION
SELECT 
    thread_id, user_id, 
    date_created AS latest_date
FROM comments
UNION
SELECT 
    thread_id, user_id, 
    date_updated AS latest_date
FROM comments


This would give you a sort of thread and comment creation/update log, where each creation and update date for all threads and posts are listed, with the thread and user ID attached to it.

From there you can use this virtual log table in the FROM clause to a simple SELECT that uses ORDER BY to sort the virtual log table according to the latest date, and JOIN the other tables to fetch the needed info.
SELECT
    vt.latest_date,
    vt.thread_id, tj.title AS thread_title,
    vt.user_id, u.first_name, u.last_name
FROM (    
    -- <INSERT VIRTUAL TABLE HERE>
) AS vt
LEFT JOIN users AS u
    ON vt.user_id = u.id
LEFT JOIN threads AS tj
    ON vt.thread_id = tj.id
ORDER BY latest_date DESC
LIMIT 1;


The result of which should be the thread where the last post or update occurred.
Was This Post Helpful? 2
  • +
  • -

#3 cancer10   User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 55
  • Joined: 12-July 08

Re: MySQL: Forum teaser, how to get latest post date and author name?

Posted 15 January 2012 - 09:32 AM

Hello Atil

Thanks so much for the time you spent on replying my query.

I will follow the suggestion that you mentioned.

Also, do you think its wise to do the date sorting thing with PHP instead of mysql?


Thanks
Was This Post Helpful? 0
  • +
  • -

#4 cancer10   User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 55
  • Joined: 12-July 08

Re: MySQL: Forum teaser, how to get latest post date and author name?

Posted 15 January 2012 - 09:52 AM

Also, is there anyway I can add a column in the result set that will return the total number of comments per thread?


Thanks
Was This Post Helpful? 0
  • +
  • -

#5 cancer10   User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 55
  • Joined: 12-July 08

Re: MySQL: Forum teaser, how to get latest post date and author name?

Posted 19 January 2012 - 12:46 AM

Any updates sir?
Was This Post Helpful? 0
  • +
  • -

#6 e_i_pi   User is offline

  • = -1
  • member icon

Reputation: 879
  • View blog
  • Posts: 1,893
  • Joined: 30-January 09

Re: MySQL: Forum teaser, how to get latest post date and author name?

Posted 19 January 2012 - 01:07 AM

View Postcancer10, on 15 January 2012 - 04:32 PM, said:

Also, do you think its wise to do the date sorting thing with PHP instead of mysql?

It all depends on how you're structuring your code, and what you're going for. I use phpBB myself, and it stores the dates as unix timestamps. Personally, I think this is a bad idea, as the unix timestamp is particular to PHP (possibly some other languages, not sure), and it is very easy to convert the timestamp from and to unix via MySQL functions FROM_UNIXTIME() and UNIX_TIMESTAMP(). Storing datetimes as (int) unix timestamp becomes problematic if you need to move to a different SQL server that isn't MySQL (and hence doesn't support unix timestamp conversion). Also, I believe that if datetime is there, and there are comparison functions for datetime, you ought to use it, as that is what it's designed for.

In terms of sorting, let MySQL do the heavy lifting. That's what it's designed for. Database servers are specifically geared to handle large and often clumsy datasets.

View Postcancer10, on 15 January 2012 - 04:52 PM, said:

Also, is there anyway I can add a column in the result set that will return the total number of comments per thread?

There is, it will involve adjusting the virtual table that Atli posted above. Now, I've only had a brief look at your problem, but I believe the virtual table will need to be rewritten as:
SELECT 
    id AS thread_id, user_id, 
    date_created AS latest_date,
	COUNT(*) - 1 AS comment_count
FROM threads
LEFT OUTER JOIN comments ON comments.thread_id = threads.id
UNION
SELECT 
    id AS thread_id, user_id, 
    date_updated AS latest_date,
	COUNT(*) - 1 AS comment_count
FROM threads
LEFT OUTER JOIN comments ON comments.thread_id = threads.id
UNION
SELECT 
    thread_id, user_id, 
    date_created AS latest_date,
	COUNT(*) AS comment_count
FROM comments
UNION
SELECT 
    thread_id, user_id, 
    date_updated AS latest_date,
	COUNT(*) AS comment_count
FROM comments


...which will expose the comment count as vt.comment_count for your main query.

Quote

Any updates sir?

Not lecturing you, but patience is a virtue :) I'd say most of us here have day jobs, and the world of IT can get pretty busy at times, so if you don't get a response after a few hours or even a day, don't be surprised :)

This post has been edited by e_i_pi: 19 January 2012 - 01:09 AM

Was This Post Helpful? 0
  • +
  • -

#7 cancer10   User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 55
  • Joined: 12-July 08

Re: MySQL: Forum teaser, how to get latest post date and author name?

Posted 21 January 2012 - 05:19 AM

first of all thank you once again for taking time and replying to my query.


Secondly, As per your suggestion, I have altered the column named of my tables.

The structure looks like this:


CREATE TABLE `thread` (
  `thread_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT(10) DEFAULT NULL,
  `project_id` INT(10) DEFAULT NULL,
  `title` VARCHAR(64) DEFAULT NULL,
  `description` TEXT,
  `lock_thread` ENUM('YES','NO') DEFAULT 'NO',
  `date_created` DATETIME DEFAULT CURRENT_TIMESTAMP,,
  `date_updated` DATETIME DEFAULT CURRENT_TIMESTAMP,,
  `last_post_time` INT(10) DEFAULT CURRENT_TIMESTAMP,,
  PRIMARY KEY (`thread_id`)
) ENGINE=MYISAM AUTO_INCREMENT=14 DEFAULT CHARSET=latin1


CREATE TABLE `comment` (
  `comment_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT(10) DEFAULT NULL,
  `thread_id` INT(10) UNSIGNED DEFAULT NULL,
  `comment` TEXT,
  `date_created` DATETIME DEFAULT CURRENT_TIMESTAMP,,
  `date_updated` DATETIME DEFAULT CURRENT_TIMESTAMP,,
  PRIMARY KEY (`comment_id`)
) ENGINE=MYISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1




CREATE TABLE `user` (
  `user_id` INT(10) NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(66) NOT NULL,
  `last_name` VARCHAR(66) NOT NULL,
  `email_address` VARCHAR(255) DEFAULT NULL,
  `password` VARCHAR(100) NOT NULL,
  `role_id` INT(5) NOT NULL,
  `is_active` ENUM('YES','NO') NOT NULL DEFAULT 'YES',
  `date_created` DATETIME NOT CURRENT_TIMESTAMP,
  `date_updated` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  KEY `email_address` (`email_address`)
) ENGINE=MYISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1




Thirdly, I found a simpler solution for finding out the last updated thread. I added a column "last_post_time" in the thread table. This field will be updated each time a comment is posted for that thread. In that way I can know which thread has latest comment.

however I seem to have a problem here. I want to get the count of all comments for each thread. Can this be done easily within one single query?

PS: I was taking a look into the phpBB code and found that they are running multiple queries for:

1. Getting all topics per category
2. Count of replies per topic
3. Count of views per topic
etc etc

Do you think this is a good practice to run mutiple queries to get the desired data or JOINs are much better and efficient?




Thanks in advance

This post has been edited by cancer10: 21 January 2012 - 05:26 AM

Was This Post Helpful? 0
  • +
  • -

#8 Atli   User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4241
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

Re: MySQL: Forum teaser, how to get latest post date and author name?

Posted 21 January 2012 - 06:26 AM

There are a couple of errors in your table structure.

First, CURRENT_TIMESTAMP is not a valid default value for a DATETIME field. If you want it to automatically use the current date, you'll have to resort to using triggers. (Something like this.)

Second, your new last_post_time field is an INT column. Should this not be a DATETIME or a TIMESTAMP, to store an actual date?

View Postcancer10, on 21 January 2012 - 12:19 PM, said:

however I seem to have a problem here. I want to get the count of all comments for each thread. Can this be done easily within one single query?

Sure. I don't know exactly what your new query looks like so I can't suggest how you could integrate this with it, but in general you just need to use the COUNT with a GROUP BY.
SELECT thread_id, COUNT(*) AS post_count
FROM comment
GROUP BY thread_id;



View Postcancer10, on 21 January 2012 - 12:19 PM, said:

Do you think this is a good practice to run mutiple queries to get the desired data or JOINs are much better and efficient?

That depends on the queries. If you can design a single query to get all the data you need, and still have it perform as well or better than a group of simpler queries, then you should definitely do that. But it may not always be the best option. - Something like this has to be evaluated depending on the situation.
Was This Post Helpful? 1
  • +
  • -

#9 cancer10   User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 55
  • Joined: 12-July 08

Re: MySQL: Forum teaser, how to get latest post date and author name?

Posted 21 January 2012 - 08:54 AM

Hi Thanks for your reply.

I am trying the following query but it does not show the correct count of comments per thread.
Any idea why?


SELECT 
	thread.title, 
	CONCAT(user.first_name,' ', user.last_name) AS full_name,
	COUNT(comment.comment_id) AS comment_count
FROM thread
LEFT JOIN COMMENT ON (comment.thread_id=thread.thread_id)
LEFT JOIN USER ON ( (thread.user_id=user.user_id) OR (comment.user_id=user.user_id) )
GROUP BY thread.thread_id


Was This Post Helpful? 0
  • +
  • -

#10 Atli   User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4241
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

Re: MySQL: Forum teaser, how to get latest post date and author name?

Posted 21 January 2012 - 09:25 AM

Is the count you are getting to high?

I would guess that the problem is in you second join. Because of the two possible conditions in the ON clause, it has two possible points on which to join, and both of them will. So for each comment in each thread, two rows will be created, one for each condition in the JOIN. This would leave you with a COUNT value twice what it should be.

You can solve this issue by doing:
COUNT(DISTINCT comment.comment_id)

This will make sure each comment is only counted once.


However, another issue exists in that query. Because of the GROUP BY, only one of the rows will show, and it may not be the one last updated. Meaning that the "full_name" may not be the one you want. To work around this, you could use the same method I used in my first post: creating a derived table (what I called a "virtual" table before) in the FROM clause of this query, which returns the rows in such a way that the latest posted/updated thread/comment is the first/only row for each thread.
Was This Post Helpful? 0
  • +
  • -

#11 e_i_pi   User is offline

  • = -1
  • member icon

Reputation: 879
  • View blog
  • Posts: 1,893
  • Joined: 30-January 09

Re: MySQL: Forum teaser, how to get latest post date and author name?

Posted 21 January 2012 - 10:44 PM

LEFT OUTER JOINs create at least one row regardless of whether the ON clause is satisfied or not. So, threads with no comments will show as having one comment. Also, Atli's suggestions of using COUNT(DISTINCT comment.comment_id) should resolve a problem you are having where comments are counted more than once.
Was This Post Helpful? 0
  • +
  • -

#12 e_i_pi   User is offline

  • = -1
  • member icon

Reputation: 879
  • View blog
  • Posts: 1,893
  • Joined: 30-January 09

Re: MySQL: Forum teaser, how to get latest post date and author name?

Posted 21 January 2012 - 11:19 PM

Okay, try this query, see if a) it works and B) it returns the right data. I haven't tested this, so there could be syntax errors.
SELECT
	thread.title,
	CONCAT(user.first_name, ' ', user.last_name) AS full_name,
	CASE WHEN comment_info.comment_count IS NULL THEN 0 ELSE comment_info.comment_count END AS comment_count
FROM thread
INNER JOIN (
	SELECT
		thread_id,
		user_id,
		most_recent
	FROM (
		SELECT
			thread_id,
			user_id,
			date_created AS most_recent
		FROM thread
		GROUP BY thread_id
		HAVING MAX(date_created)
		UNION ALL
		SELECT
			thread_id,
			user_id,
			date_updated AS most_recent
		FROM thread
		GROUP BY thread_id
		HAVING MAX(date_updated)
		UNION ALL
		SELECT
			thread_id,
			user_id,
			date_created AS most_recent
		FROM comment
		GROUP BY thread_id
		HAVING MAX(date_created)
		UNION ALL
		SELECT
			thread_id,
			user_id,
			date_updated AS most_recent
		FROM comment
		GROUP BY thread_id
		HAVING MAX(date_updated)
	) AS latest_posts
	GROUP BY thread_id
	HAVING MAX(most_recent)
) AS thread_info ON thread_info.thread_id = thread.thread_id
LEFT OUTER JOIN (
	SELECT
		thread_id,
		COUNT(*) AS comment_count
	FROM comment
	GROUP BY thread_id
) AS comment_info ON comment_info.thread_id = thread.thread_id
INNER JOIN user ON user.user_id = thread_info.user_id


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1