创建论坛，并尝试按时间戳对两个表进行排序。 一个表主题和其他答复。 我该如何分类呢？

Question

I am trying to sort two tables that I created for a forum by timestamp.

I have searched with no luck yet. Please help.

My first table is "forum_posts" and timestamp is "forum_timestamp" My second table is "forum_posts_replys" is "fpr_timestamp"

With the following query I am able to sort by timestamp or the other, but I would like to sort both. If there are not any replys in a topic yet and it was just posted I would like to see it on the top, but if there is a reply in another topic I would like that reply to show first.

                (SELECT fp.*, fpr.*
            FROM forum_posts fp
            LEFT JOIN forum_posts_replys fpr
            ON fp.forum_post_id = fpr.fpr_post_id
            WHERE `fp`.`forum_id`='$f_id'
            GROUP BY fp.forum_post_id)
            ORDER BY `forum_timestamp` DESC

I also tried GREATEST() and no luck.

The page I am trying to use this on is the main topic listing page. The page just shows topics with their titles and a count of replys and how long ago each was posted.

My current query 06/14/14:

                SELECT sub.*
            FROM
            (SELECT 
             fp.forum_post_id as forum_post_id, fp.forum_id as forum_id, 
             fp.forum_user_id as forum_user_id, fp.forum_title as forum_title, 
             fp.forum_content as forum_content, fp.forum_edit_date as forum_edit_date,
             fp.forum_timestamp as forum_timestamp, fpr.id as id,
             fpr.fpr_post_id as fpr_post_id, fpr.fpr_id as fpr_id,
             fpr.fpr_user_id as fpr_user_id, fpr.fpr_title as fpr_title,
             fpr.fpr_content as fpr_content, fpr.fpr_edit_date as fpr_edit_date,
             fpr.fpr_timestamp as fpr_timestamp,
             ifnull(fpr.fpr_timestamp,forum_timestamp) as tstamp
            FROM forum_posts fp
            LEFT JOIN forum_posts_replys fpr
            ON fp.forum_post_id = fpr.fpr_post_id
            WHERE `fp`.`forum_id`='$f_id'
            GROUP BY fp.forum_post_id) sub

            ORDER BY tstamp DESC

Still only seems to sort by forum_timestamp

Answer 1

Use a subquery.

        SELECT fp.*, fpr.*
        from

        (SELECT fp.*, fpr.*, ifnull(fpr.fpr_timestamp,forum_timestamp) as tstamp
        FROM forum_posts fp
        LEFT JOIN forum_posts_replys fpr
        ON fp.forum_post_id = fpr.fpr_post_id
        WHERE `fp`.`forum_id`='$f_id'
        GROUP BY fp.forum_post_id) sub

        order by tstamp desc

Edit: List the wanted fields explicitly and use the table alias for the subquery:

        SELECT sub.*
        from

        (SELECT fp.field_1 as f1, fp.field_2 as f2, fpr.field_1 as f3, fpr.field_2 as f4, ifnull(fpr.fpr_timestamp,forum_timestamp) as tstamp
        FROM forum_posts fp
        LEFT JOIN forum_posts_replys fpr
        ON fp.forum_post_id = fpr.fpr_post_id
        WHERE `fp`.`forum_id`='$f_id'
        GROUP BY fp.forum_post_id) sub

        order by tstamp desc