Thread of the day

Tallon

Hey guys,

Quick question, how is thread of the day chosen?

I know how POTD is, (most / quickest thanks) but not TOTD

I mean, how is this TOTD?

Redonkulous

seamus

It's randomly chosen.

biko

Afaik it's a random selection.

Tallon

seamus wrote: »

It's randomly chosen.

I see, what a shame

seamus

Yep, I've been long saying that it should actually be the "thread of the day". Dead easy to code - find a thread started yesterday with the highest posts/minute rate and/or the largest amount of aggregate thanks.

The problem though is that an After Hours thread will almost always win out.

Tallon

seamus wrote: »

Yep, I've been long saying that it should actually be the "thread of the day". Dead easy to code - find a thread started yesterday with the highest posts/minute rate and/or the largest amount of aggregate thanks.

The problem though is that an After Hours thread will almost always win out.

Exclude AH maybe?

seamus

Or you could go a little more complicated and find the top ten threads from distinct forums and then choose one of them at random.

So effectively choose the most popular thread from each forum, then the top ten of them, then one of those at random.

Tallon

seamus wrote: »

Or you could go a little more complicated and find the top ten threads from distinct forums and then choose one of them at random.

So effectively choose the most popular thread from each forum, then the top ten of them, then one of those at random.

Start writing that code Seamus

faceman

seamus wrote: »

Or you could go a little more complicated and find the top ten threads from distinct forums and then choose one of them at random.

So effectively choose the most popular thread from each forum, then the top ten of them, then one of those at random.

You would probably need to exclude off topic chat threads tho as id expect they would rank very high?

seamus

Tallon wrote: »

Start writing that code Seamus

If I knew what the vbulletin database looked like, I'd do it up in 30 mins.

faceman wrote: »

You would probably need to exclude off topic chat threads tho as id expect they would rank very high?

Well, you only choose threads which were started yesterday. You're basically looking for the most popular new threads of the last 24 hours...

My name is URL

Conor gave some good info on how they might change the system here, if anyone has any ideas on how to do it

seamus

Here you go, a little bit of pseudocode. Dunno if it will work or how much it will rape the database. Without knowing the database schema, it's a bit of a shot in the dark.

SELECT 
		ranks.forumid,
		thr.threadid, 
		thr.threadtitle
	FROM thread thr
	JOIN (SELECT 
				forumid, 
				MAX(totalviews/(UNIX_TIMESTAMP(NOW()) - thread_start)) AS 'thr_rank'
		FROM 
			thread 
		WHERE 
			thread_start >=  UNIX_TIMESTAMP(DATE(DATE_SUB(NOW(), INTERVAL 1 DAY)))
		GROUP BY 
			forumid) ranks
			ON ranks.forumid = thr.forumid AND ranks.thr_rank = (thr.totalviews/(UNIX_TIMESTAMP(NOW()) - thr.thread_start))
	WHERE 
		thr.thread_start >=  UNIX_TIMESTAMP(DATE(DATE_SUB(NOW(), INTERVAL 1 DAY)))
	ORDER BY 
		ranks.thr_rank DESC
	LIMIT 0, 10

For extra fanciness, you can replace the last line with

LIMIT <?php echo rand(0, 9) ?>, 1

Quazzie

There is a rating system for threads. Could the highest rated thread in any single 24 hour period not be thread of the day?

Conor

seamus:

Current output of that query:

+---------+------------+-------------------------------------------------+
| forumid | threadid   | title                                           |
+---------+------------+-------------------------------------------------+
|    1076 | 2055833213 | Joe Gough: Who's been?                          | 
|     994 | 2055833229 | Trying to find a comedians name                 | 
|    1142 | 2055833225 | Best 3D Package to Learn                        | 
|     867 | 2055833220 | Possible to get a stereo system with turntable? | 
+---------+------------+-------------------------------------------------+
4 rows in set (9.19 sec)

Query plan:

+----+-------------+------------+-------+-----------------------------------+----------+---------+---------------+------+----------------------------------------------+
| id | select_type | table      | type  | possible_keys                     | key      | key_len | ref           | rows | Extra                                        |
+----+-------------+------------+-------+-----------------------------------+----------+---------+---------------+------+----------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL                              | NULL     | NULL    | NULL          |  341 | Using filesort                               | 
|  1 | PRIMARY     | thr        | ref   | dateline,forumid,forumid_lastpost | forumid  | 2       | ranks.forumid | 1452 | Using where                                  | 
|  2 | DERIVED     | thread     | range | dateline                          | dateline | 4       | NULL          | 7746 | Using where; Using temporary; Using filesort | 
+----+-------------+------------+-------+-----------------------------------+----------+---------+---------------+------+----------------------------------------------+

TBH, I don't think the efficiency's the problem.

Conor

Quazzie wrote: »

There is a rating system for threads. Could the highest rated thread in any single 24 hour period not be thread of the day?

Too few people use the system, hence it's trivial to game.

seamus

Conor wrote: »

TBH, I don't think the efficiency's the problem.

Probably the variable time...

SET @curr_time = NOW();
SELECT 
		ranks.forumid,
		thr.threadid, 
		thr.threadtitle
	FROM thread thr,
		(SELECT 
				forumid, 
				MAX(totalviews/(UNIX_TIMESTAMP(@curr_time) - thread_start)) AS 'thr_rank'
		FROM 
			thread 
		WHERE 
			thread_start >=  UNIX_TIMESTAMP(DATE(DATE_SUB(@curr_time, INTERVAL 1 DAY)))
		GROUP BY 
			forumid) ranks
	WHERE 
		ranks.forumid = thr.forumid 
		AND ranks.thr_rank = (thr.totalviews/(UNIX_TIMESTAMP(@curr_time) - thr.thread_start))
		AND thr.thread_start >=  UNIX_TIMESTAMP(DATE(DATE_SUB(@curr_time, INTERVAL 1 DAY)))
	ORDER BY 
		ranks.thr_rank DESC
	LIMIT 0, 10

Outside of that, I'm lost without DB access

Quazzie

Conor wrote: »

Too few people use the system, hence it's trivial to game.

We should promote and it'll develop into a whole new type of whore. We'll have rates whores aswell as thanks whores.

Ross

My own way of measuring this would be as dumb and subjective as PotD.

Find all threads created in the last 24 hours, find all the posts in all of those threads, count the thanks in each of those posts, roll them up into each threadid and take the one with the most thanks.

*shrugs*

I might give it a shot at some point.

Conor

Nope, it's not that. Pretty much the same results. I think your metric is a little unfairly skewed towards very new threads in low traffic forums.

I don't want to go copying chunks of the schema since that could be a little dubious in relation to the vBulletin license, but the following tables/fields may be useful if you think about experimenting further:

forum.forumid
forum.replycount
forum.threadcount

thread.threadid
thread.title
thread.forumid
thread.replycount
thread.dateline -- UNIX timestamp for start time
thread.views
thread.votenum
thread.votetotal

post.postid
post.threadid
post.dateline
post.post_thanks_amount

seamus

Conor wrote: »

Nope, it's not that. Pretty much the same results. I think your metric is a little unfairly skewed towards very new threads in low traffic forums.

That makes sense

I'll have a think about it, cheers.

Actually, very quick one, don't even know if it'll work; Cutting out the low view counts...

SET @curr_time = NOW();
SELECT 
		ranks.forumid,
		thr.threadid, 
		thr.title
	FROM thread thr,
		(SELECT 
				forumid, 
				MAX(views/(UNIX_TIMESTAMP(@curr_time) - dateline)) AS 'thr_rank'
		FROM 
			thread 
		WHERE 
			dateline  >=  UNIX_TIMESTAMP(DATE(DATE_SUB(@curr_time, INTERVAL 1 DAY)))
			AND views > AVG(views)
		GROUP BY 
			forumid) ranks
	WHERE 
		ranks.forumid = thr.forumid 
		AND ranks.thr_rank = (thr.views/(UNIX_TIMESTAMP(@curr_time) - thr.dateline ))
		AND thr.dateline >=  UNIX_TIMESTAMP(DATE(DATE_SUB(@curr_time, INTERVAL 1 DAY)))
	ORDER BY 
		ranks.thr_rank DESC
	LIMIT 0, 10

I'm sure something more statistical could be applied to it.