"Last Post By" for forum, Join, Join and loads of Join

Question

From that topic, I asked a question regarding CodeIgniter, I've thought of doing it completely raw now instead.

This is my current query.

 SELECT `forums`.*, 
        Count(topics.id) threads, 
        Count(replies.id) replies, 
        `users`.`url` user_url, 
        `users`.`name` user_name,
        `ranks`.`name` user_rank  
 FROM (`forums`)  
 LEFT JOIN `topics` ON `topics`.`f_id` = `forums`.`id`  
 LEFT JOIN `replies` ON `replies`.`t_id` = `topics`.`id`  
 LEFT JOIN `users` ON `users`.`id` = `topics`.`a_id`  
 LEFT JOIN `ranks` ON `users`.`status` = `ranks`.`id`  
 LEFT JOIN (  
 SELECT `topics`.`url` topic_url, `topics`.`name` topic_name  
 FROM `topics`  
 ORDER BY `created` DESC  
 LIMIT 1  
 ) last_post ON `topics`.`f_id` = `forums`.`id`  
 GROUP BY `forums`.`id`

So what I am trying to do is to get everything in one nifty row. Everything beside "Last Post By" is working, if I remove the later LEFT JOIN I added I get the first, and not the last post.

So my question is; What am I now doing wrong?

Also, a bonus question, I talked to a friend earlier that is a bit more knowledgable when it comes to PHP and MySQL than I am, he told me to just store it all in the database, last_post as well as the count of threads and replies.

Thanks in advance.

Update

Here is forums table

CREATE TABLE IF NOT EXISTS `forums` (  
  `id`    int(11) NOT NULL AUTO_INCREMENT,  
  `c_id`  int(11) NOT NULL,  
  `url`   varchar(255) NOT NULL,  
  `name`  varchar(255) NOT NULL,  
  `desc`  text NOT NULL,  
PRIMARY KEY (`id`),  
KEY `url` (`url`,`name`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;

Here is topics table

CREATE TABLE IF NOT EXISTS `topics` (  
  `id` int(11) NOT NULL AUTO_INCREMENT,  
  `f_id`    int(11) NOT NULL,  
  `url`     varchar(255) NOT NULL,  
  `name`    varchar(255) NOT NULL,  
  `desc`    varchar(255) NOT NULL,  
  `body`    text NOT NULL,  
  `a_id`    int(11) NOT NULL,  
  `created` int(11) NOT NULL,  
  `edited`  int(11) NOT NULL,  
PRIMARY KEY (`id`),  
KEY `head` (`name`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=10 ;

I also tried to update how it was displayed with the markdown editing, as I did the first time, I hope this is more appealing.

I encourge you to take a look at the Markdown help for future questions, answers, and even comments. Following this advice will positively affect your experience here on StackOverflow. Thanks. — Tyler Crompton, Jul 24 '12 at 21:35
The `LIMIT 1` in the last `JOIN` is causing your problem. It only returns one record (and always the same record) that is then used for the rest of the query. Without knowing your table structure, it will be difficult to help you further. — Holger Brandt, Jul 24 '12 at 21:51

score 2 · Accepted Answer · edited May 23 '17 at 10:09

Here is a solution. I don't think it is the most efficient solution but it should work. Note that I moved the Topics SELECT to the first SELECT row.

SELECT `forums`.*, 
        Count(topics.id) threads, 
        Count(replies.id) replies, 
        `users`.`url` user_url, 
        `users`.`name` user_name,
        `ranks`.`name` user_rank,
        (SELECT `topics`.`url` 
         FROM `topics` 
         WHERE `topics`.`f_id` = `forums`.`id` 
         ORDER BY `created` DESC LIMIT 1) topic_url,
        (SELECT  `topics`.`name`
         FROM `topics` 
         WHERE `topics`.`f_id` = `forums`.`id` 
         ORDER BY `created` DESC LIMIT 1) topic_name 
 FROM (`forums`) 
 LEFT JOIN `topics` ON `topics`.`f_id` = `forums`.`id`   
 LEFT JOIN `replies` ON `replies`.`t_id` = `topics`.`id`  
 LEFT JOIN `users` ON `users`.`id` = `topics`.`a_id`  
 LEFT JOIN `ranks` ON `users`.`status` = `ranks`.`id`  
 GROUP BY `forums`.`id`

Possible more efficient way

I don't have the data to test whether this is faster but it is another way of doing it (I based this off of ROW_NUMBER() in MySQL.) This comes with one string attached and that is that the topics table can't have duplicate f_id,created combinations:

SELECT `forums`.*, 
        Count(topics.id) threads, 
        Count(replies.id) replies, 
        `users`.`url` user_url, 
        `users`.`name` user_name,
        `ranks`.`name` user_rank,
        t.url topic_url,
        t.name topic_name
 FROM (`forums`)  
 LEFT JOIN (SELECT t0.id, t0.f_id, t0.url, t0.name, t0.a_id
            FROM topics AS t0
            LEFT JOIN topics AS t1 ON t0.f_id=t1.f_id AND t1.created>t0.created
            WHERE t1.id IS NULL) AS t
            ON t.f_id = forums.id
 LEFT JOIN `replies` ON `replies`.`t_id` = t.`id`  
 LEFT JOIN `users` ON `users`.`id` = t.`a_id`  
 LEFT JOIN `ranks` ON `users`.`status` = `ranks`.`id` 
 GROUP BY `forums`.`id`

Try it out, first to see if it works, and then to see if it is faster. I'd love to hear back from you.

Thank you, that was pretty much what I was looking for, now I finally understand how that selection part worked. Now I have to ask though, what would you recommend as the most efficient way? — Emz, Jul 24 '12 at 22:14
@user1545982, I've updated my answer to give you a potentially faster way of getting what you need. Please try it out. — Holger Brandt, Jul 24 '12 at 23:23
Unknown column 'topics.id' in 'field list' I will check more into it soon, got some other stuff to attend to. — Emz, Jul 24 '12 at 23:38
@user1545982, I think I found where it went wrong. The other joins were using the `topics` table. I switched their reference to `t`. Let me know when you have time. — Holger Brandt, Jul 24 '12 at 23:47
According to CodeIgniters built in output handler the later query takes a bit less time. About 5% to 20%. Which is quite a lot I think. I will see if I get it to work with "replies", which is another table, as well. Thanks for taking your time. EDIT: Hm, I see now that I made some other changes, those 5% to 20% is probably around 2 to 16%, but still. I have to save created and replies.url, replies.name, replies.created as well. Is it just another LEFT JOIN (SELECT r0.id...) as well or? — Emz, Jul 25 '12 at 00:02
@user1545982, Good to know. Thanks for checking it out. Good luck with your project. — Holger Brandt, Jul 25 '12 at 00:06

"Last Post By" for forum, Join, Join and loads of Join

Update

1 Answers1