CODEWITHSUNDEEP
phpmysqlsqlsorting
David Sargent
David Sargent

Reputation: 15

Creating Forum, and Trying to Sort two tables by timestamp. One table Topics and other Replys. How can I sort these?

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

Upvotes: 0

Views: 54

Answers (2)

David Sargent
David Sargent

Reputation: 15

With your help I was able to troubleshoot this to make it work!

Here is the final query that worked flawlessly.

                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,     
                GREATEST(fp.forum_timestamp, COALESCE(fpr.fpr_timestamp, '00-00-00 00:00:00')) 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
                ORDER BY tstamp DESC
            ) sub

            GROUP BY forum_post_id
            ORDER BY tstamp DESC

Upvotes: 0

Alex Monthy
Alex Monthy

Reputation: 1877

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

Upvotes: 1

Related Questions