CODEWITHSUNDEEP
mysqlsubqueryleft-joinifnullgroupwise-maximum
Goldie
Goldie

Reputation: 1630

Left join on last record (simple discussion board with topics, last posts and users)

I'm trying to make a simple discussion board and as a result I need topics titles with their authors and authors of topics last posts.

So far I have this

SELECT
    `t`.`id`,
    `t`.`title`,
    `t`.`date` as theme_date,
    `u`.`id` as user_id,
    `u`.`username` as user_username,
    COUNT(p.id) as count_posts,
    SUBSTRING_INDEX(GROUP_CONCAT(`p`.`id` ORDER BY `p`.`id` DESC SEPARATOR "| " ), "| ", 1) as last_post_id,
    SUBSTRING_INDEX(GROUP_CONCAT(`p`.`date` ORDER BY `p`.`id` DESC SEPARATOR "| " ), "| ", 1) as last_post_date,
    IFNULL(MAX(p.date), t.date) AS pts
FROM
    `topics` t
LEFT JOIN
    `posts` p ON `p`.`topics_id` = `t`.`id`
LEFT JOIN
    `users` u ON `u`.`id` = `t`.`user_id`
GROUP BY
    `t`.`id`
ORDER BY
    `pts` DESC
LIMIT 10

I got here 10 latest topics titles, their authors usernames and IDs, number of messages in each topic, last ID and date of post in each topic, and everything sorted by activity... basically everything. All I need is authors username of those last posts. I guess I have to make one left join with subquery but I'm kinda stuck. Can someone help me?

Upvotes: 0

Views: 163

Answers (1)

eggyal
eggyal

Reputation: 125925

Rather than form strings containing the concatenation of the entire thread's posts from which you then obtain the first substring (ordered by id), which is not only a performance drag (because it's not sargable and requires expensive string operations) but is also subject to bugs (should the separator happen to appear within the strings being concatenated or the concatenated result exceed MySQL's limits)…

What you are really looking to do instead is obtain the groupwise maximum, which can be obtained by joining the posts table with a subquery upon it. You then merely need join the users table a second time (this time against the last post) to obtain the desired username:

SELECT   topic.id
 ,       topic.title
 ,       topic.date
 ,       topic.user_id
 ,       topic_user.username
 ,       t.count_posts
 ,       lastpost.id
 ,       lastpost.date
 ,       lastpost_user.username
 ,       IFNULL(lastpost.date, topic.date) AS pts

FROM     topics topic
         LEFT JOIN users topic_user ON topic_user.id = topic.user_id
         LEFT JOIN (
           (
             SELECT   topics_id
               ,      COUNT(*)  AS count_posts
               ,      MAX(date) AS date
             FROM     posts
             GROUP BY topics_id
           ) t  JOIN posts lastpost USING (topics_id, date)
           LEFT JOIN users lastpost_user ON lastpost_user.id = lastpost.user_id
         ) ON lastpost.topics_id = topic.id

ORDER BY pts DESC

LIMIT    10

Note that I'm also identifying the lastpost by date rather than id as synthetic keys shouldn't really be used to ascertain natural properties; however, this assumes that date is actually a timestamp.

Upvotes: 1

Related Questions