CODEWITHSUNDEEP
phpmysqlsql
Allerion
Allerion

Reputation: 405

Ordering By Another Table Column If Exists

I'm putting together a simple forum script with topics/posts. I'm trying to order topics by latest post and I've had some luck with the answer in the following question: MYSQL Order from another Table, but the problem I have is that some topics don't have any posts yet, so the query doesn't select those topics at all.

Current coding is:

SELECT DISTINCT forum.* 
FROM forum 
INNER JOIN forum_posts 
      ON forum.id = forum_posts.parent_id 
GROUP BY forum.id 
ORDER BY forum_posts.parent_id DESC, 
         forum_posts.time_created DESC 
LIMIT ?,?

I want to tell the ORDER BY to order by forum.time_created if there are no matches in forum_posts.parent_id for a topic.

On a side note, I would also like to know how to put a WHERE clause into this query as well. I want to only get rows from forum table "WHERE access <= ?", but can't work out where to put that snippet in.

Any help much appreciated!

EDIT:

Target is to return topics (from forum table) According to following details:

  1. WHERE forum.access <= ?
  2. LIMIT ?,?
  3. ORDER BY Latest Post From forum_posts table with forum_posts.parent_id matching on forum.id, or forum.time_created

EDIT 2:

An example SQLfiddle with relevant data. This doesn't work as the order should really come out as 11,10,9,1,2 http://sqlfiddle.com/#!2/83535/2

Upvotes: 0

Views: 296

Answers (1)

Jon
Jon

Reputation: 13002

Have a look at this: http://sqlfiddle.com/#!2/be909/1

Here's my final query:

SELECT forum.*, recent_forum_posts.time_created as latest_post
FROM forum 
LEFT JOIN (SELECT MAX(forum_posts.time_created) as time_created, forum_posts.parent_id
           FROM forum_posts
           GROUP BY forum_posts.parent_id) AS recent_forum_posts
        ON forum.id = recent_forum_posts.parent_id 
WHERE forum.access > 2
ORDER BY recent_forum_posts.time_created IS NULL DESC,
         recent_forum_posts.time_created DESC,
         forum.time_created DESC 
LIMIT 5

Essentially, the subquery (the bit in brackets after LEFT JOIN) selects the most recent post for each parent_id from the forum_posts table.

That is then LEFT JOINed (as we want to list all forum's even if there are no posts yet).

Upvotes: 1

Related Questions