MySQL Query Design for Latest Post per Forum

Question

Hello, i have this 3 tables

forums_forum

+-----+--------+-------------+-------+-----+
| fid | name   | description | index | cid |
+-----+--------+-------------+-------+-----+
|  36 | gdghdf | hjghj       |    54 |   5 |
|  45 | yutuy  | iuyi        |    99 |   6 |
+-----+--------+-------------+-------+-----+

forums_threads

+----+-----+-----+-------+-------+------+-----------+------+
| id | tid | fid | moved | mfrom | view | important | lock |
+----+-----+-----+-------+-------+------+-----------+------+
|  1 |   4 |  36 |     0 | NULL  |    0 |         0 |    0 |
|  2 |  12 |  36 |     0 | NULL  |    7 |         0 |    0 |
|  3 |   9 |  15 |     0 | NULL  |    0 |         0 |    0 |
+----+-----+-----+-------+-------+------+-----------+------+

forums_posts

+----+-------+--------+--------+---------------------+--------+--------+-----+
| id | title | detail | author | date                | edited | editby | tid |
+----+-------+--------+--------+---------------------+--------+--------+-----+
|  1 | asfsd | sdfsd  |      1 | 2010-07-01 21:31:29 |      0 | NULL   |   4 |
+----+-------+--------+--------+---------------------+--------+--------+-----+

I'm trying to create query which return result -> for every unique 'fid', one row from 'forums_posts' (ORDER BY 'date').

forums_forum.fid = forums_threads.fid forums_threads.tid = forums_posts.tid

Thanks

Answer 1

Well i suggest some JOINs for you.

SELECT C.date, C.title, A.name
FROM forums_forum A 
   JOIN forums_threads B ON A.fid=B.fid 
   JOIN forums_posts C ON B.tid=C.tid
ORDER BY C.date DESC LIMIT 1

or .. not tested:

SELECT MAX(c.date), C.date, C.title, A.name
FROM forums_forum A 
   JOIN forums_threads B ON A.fid=B.fid 
   JOIN forums_posts C ON B.tid=C.tid
LIMIT 1

;-)

p.s. you might get troubles by naming a column "date" as well as "index" and "view", better use other names.

p.p.s. are there fids (i assume this is your primary key and should be unique) whiche occure more then once?

Answer 2

This is the venerable greatest-n-per-group problem that comes up frequently on Stack Overflow. Here's a solution given your tables:

SELECT p.* FROM forums_posts p JOIN forums_threads t ON p.tid = t.tid
WHERE NOT EXISTS (
    SELECT * FROM forums_posts p2 JOIN forums_threads t2 ON p2.tid = t2.tid
    WHERE t.fid = t2.fid AND p.date < p2.date
);

Answer 3

Alternative to a JOIN, you can add a column to your forums_forums table with a name like last_updated. On every posting to a thread, simply run an additional UPDATE like.

UPDATE forums SET last_updated = NOW()

Then, to get the forums by the order of forums, your SELECT becomes much simpler, and performant.

SELECT * FROM forums_forum ORDER BY last_updated DESC