...

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



phantom007
01-15-2012, 08:57 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

Old Pedant
01-16-2012, 01:26 AM
I hope you won't take this wrong, but I think you have a couple of flaws in your database design that are making not only this query but other queries in the future more complex than needed.

I, personally, would kill off these fields from your threads table:


`thr_description` TEXT,
`thr_date_created` DATETIME DEFAULT NULL,
`thr_date_updated` DATETIME DEFAULT NULL,

and then, instead, I would *only* use the corresponding fields in the comments table.

In other words, the first message in the thread is simply stored as the first comment in that thread.

And, too, I would *ALWAYS* set the cmnts_date_updated field, even when that comment is first posted. So a comment that has not been updated would have that field and cmnts_date_created identical.

Now you only have to do a really simple query to find the latest post in a thread:


SELECT * FROM comments ORDER BY cmnts_date_updated DESC LIMIT 1

(And of course you can join to the threads and users tables to get the thread title and the user's name, but that won't affect the basic ORDER BY and LIMIT 1.)

Note that you should add an index on cmnts_date_updated if you want any decent performance at all.

I really believe this will simplify a lot of things. For example, to get the text of all postings in a thread, you only have to look in the one table; you don't have to treat the first post special to get it from the threads table, as well.

If you aren't amenable to these changes, we can build a query to do what you want, but it will be a lot clumsier.

Old Pedant
01-16-2012, 01:35 AM
Oh, incidentally, you should never declare foreign keys to be NULLable, even with MyISAM tables. And I also strongly recommend than you declare foreign keys even in MyISAM tables, just for self-documentation, if no other reason.

Finally, why not provide default values when possible?


CREATE TABLE `comments_cmnts` (
cmnts_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
cmnts_usr_id INT REFERENCES users_usr(usr_id),
cmnts_thr_id INT UNSIGNED REFERENCES threads_thr(thr_id),
cmnts_message TEXT,
cmnts_date_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
cmnts_date_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX (cmnts_date_updated),
INDEX (cmnts_thr_id)
);

You clearly need the two indexes I added there. The one on cmnts_date_updated for finding latest posts. The one on cmnts_thr_id for finding all posts in a given thread quickly.

**********

And I have to ask: Why the "double names" on your tables? Why "comments_cmnts" instead of just "comments"??

phantom007
01-21-2012, 01:18 PM
first of all thank you once again Old Pedant 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?


Thanks in advance

phantom007
01-22-2012, 06:37 AM
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

Old Pedant
01-22-2012, 08:18 AM
Sure. Change COUNT(commen_id) to just comment_id and omit the GROUP BY, temporarily, and you'll clearly see the reason why.

That is, do this:


SELECT
thread.title,
CONCAT(user.first_name,' ', user.last_name) AS full_name,
comment.comment_id
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) )


This is one reason why I told you to kill the user_id on the thread table.

You are getting duplicate matches, so the comment_id gets counted multiple times.

Old Pedant
01-22-2012, 08:19 AM
Any time you have a single JOIN the matches a primary key to more than one foreign key, you can almost guarantee that you have a flaw in your db design. As you do here.

Sorry, but I still say you need to rethink the design here.

phantom007
01-22-2012, 08:35 AM
This is one reason why I told you to kill the user_id on the thread table.

Even if I kill the user_id on the thread table, how am I supposed to know which user posted the thread?

Old Pedant
01-22-2012, 08:55 AM
Go back and carefully read what I said in post #2.

I quite frankly don't understand why you treat the first post in a thread special. It will only cause you grief in the long run.

For example, if you want to offer a SEARCH through all posts, you would have to UNION the thread and comment tables in order to effectively search *all* posts.

I'm sorry, but I think you have a design flaw that's going to come back and bite you in more ways in the future.

Old Pedant
01-22-2012, 08:56 AM
(That doesn't mean you shouldn't have the THREAD table. Just that it should *ONLY* contain information about the thread, per se, and nothing about the posts in the thread...including the first post.)

phantom007
01-22-2012, 09:17 AM
Go back and carefully read what I said in post #2.



So you want to say the thread post should have the following 2 fields only:

thread_id and thread_title

and the rest part of can be added to the comments table?

Old Pedant
01-22-2012, 09:28 AM
It could be as few as those two fields.

I wouldn't worry about adding thread_start_date. It's not strictly necessary, as it can be derived from the associated comments, but it could be handy to have. You could also have thr_user_id, so that it's easy to get "find all threads started by xxx" as this forum has, for example. Again, it could be derived from the comments table but it might be nice to have it for efficiency of some later queries.

*IF* you used a TRIGGER, then I might even support thread_last_updated. But not if you don't use a TRIGGER.

phantom007
01-22-2012, 09:35 AM
You could also have thr_user_id, so that it's easy to get "find all threads started by xxx" as this forum has, for example.

Would you do that if you were designing the application? (just taking an idea)

Old Pedant
01-22-2012, 09:36 AM
Think about the kinds of queries you will later need to make. That's what should drive the design, esp. what fields are needed in the threads table.

For example, without a thr_user_id, to find who started a thread you have to do


SELECT userid, postdate FROM comments WHERE userid = xxx AND threadid = yyy ORDER BY postdate LIMIT 1

and that's bad enough for getting one userid, but how do you find "all thread started by use xxx"?

Something as ugly as this:


SELECT COUNT(*)
FROM comments AS C,
( SELECT threadid, MIN(postdate) AS mindate
FROM comments
GROUP BY threadid ) AS M
WHERE C.postdate = M.mindate
AND C.userid = xxx

Okay, that's not *real* ugly, but it's a lot slower than simply


SELECT COUNT(*) FROM threads WHERE userid = xxx

So... plan ahead, that's the best advice I can give.

Simplify, yes, but if you see that adding in some easy to add data item will help later queries a lot...well, it's all about balance.

Old Pedant
01-22-2012, 09:37 AM
Mind you, if you never expect to have a query such as "Find all threads started by user xxx" then the need for userid in the threads table goes way down.

So... As with most programming questions, the real answer is: "It depends ... "

Old Pedant
01-22-2012, 09:43 AM
And don't ask me how I'd design a forum unless you want a treatise. I *LOATHE* forums like this one where all the answers are just lumped together.

I much prefer things like the old USENET forums (news groups, they were called) when each thread has as many subthreads as needed. That is, each post was linked to the one that it was *ANSWERING*.

So you'd see stuff like:


9AM User 1: How do you design a forum?
10AM User 2: Do you want a multithreaded forum or a simple one?
11AM User 1: I don't know. Which is better?
Noon User 2: Well, let me write a treatise..
10AM User 3: Oh that's really easy. You only need two tables
11AM User 1: Which two tables
Noon User 4: I want to know that, too
11AM User 2: That's nutso ... you need at least 3

And so on.

So if you aren't interested in some sub-thread, you can ignore it and only read the answers you care about.

phantom007
01-22-2012, 09:45 AM
oh great!

Thanks for the kind advice.


How do you think so deep? are you an SQL consultant?

Thanks

Old Pedant
01-23-2012, 12:42 AM
LOL! I'm a generic consultant, I guess.

Let's see... I have had (gotta take my shoes off for this) 19 jobs in my career, of which 2 were full time consulting to one company an 1 was working for a consulting company where I did at least 10 different jobs for various clients.

It so happens that I'm currently working as a consultant, yes (I'm semi-retired, working from home), and it involves working with three different databases: MySQL, SQL Server, and (ugh) Access. As well as doing HTML, JavaScript, C#, and VB coding. Very eclectic.

But no, I don't consider myself a database expert, per se. Just that I've worked with many different databases (another 5 to 8--depending on how you count them--in addition to the ones mentioned, including one that I designed and built from the ground up). You get around that much, and you are exposed to all sorts of concepts and methods.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum