Get data from relational key reference to same table [Sort topics by date]

[deadimp]

I know it's a bit of a confusing and generic title, but here's my situation:

I'm designing some forum software based off of Thacmus, and I want to know the easiest way sort topic in the forum view according to the date on their latest post.

 

All topics / posts are stored in the table `posts`. The fields in focus here are `id`, `date`, `topic_id`, and `latest_id`.

`topic_id` is the relational key for a post's topic (if it's a topic itself, this value is -1), and `latest_id` is just a reference to topic's latest post.

One implementation I'm thinking of is having the an empty topic's (a topic without replies) latest post just reference to itself, so something like this query could work without any extra voodoo:

select from `post` where `topic_id`='-1' order by (select `date` from `post` where `id`=`latest_id`) desc

I think this could be done better using a join, but I'm just a MySQL novice. As you see, if the post's latest id references to itself, then it won't return a null date on the nested select statement.

 

Anyone know of a better way?

 

NOTE: I didn't included the forum info in there 'cause that's just implied.

[Barand]

try

SELECT p1.id, MAX(p2.date) as latest
FROM posts p1
INNER JOIN posts p2 ON p1.id = p2.topic_id
WHERE p1.topic_id = -1
GROUP BY p1.id

[deadimp]

Thanks for the help!

 

I've tried out the join approach, screwed around with left/right/inner joins, but I still haven't exactly learned what the difference is and how I can make them do what I want (essentially the problem in all programming). I wanted it to be able to handle `latest_id`=-1, but when I tested out the joins the results only brought up topics with a valid relation (topic.latest_id=post.id).

So I ended up just using a basic sub-query as I had before, but also stuck in a conditional (once I learned how to do that):

select `t`.* from `post` `t` where `t`.`topic_id`='-1'
order by if( `t`.`latest_id`='-1', `date`, (select `date` from `post` where `id`=`t`.`latest_id`) ) desc

There's an alternate sub-query I was considering, but I'm not sure it's faster than the first:

select max(`date`) from `post` where `topic_id`=`id`

Since it would have to go through multiple rows to get all of the data, I'm sure it'd be slower (though by an insignificant amount, most likely).

 

Is there a more aesthetic way to do this with joining, or is the sub-query way the simplest?

[fenway]

Joining is almost always faster, though sometimes harder to read.

[Barand]

Just noticed I forgot the ORDER BY in my last post

 

SELECT p1.id, MAX(p2.date) as latest
FROM posts p1
INNER JOIN posts p2 ON p1.id = p2.topic_id
WHERE p1.topic_id = -1
GROUP BY p1.id
ORDER BY latest DESC

[deadimp]

How do I have the join account for possibly missing latest posts?

I read in the MySQL manual that the left join creates NULL fields for relations that don't exist in table B. I tried it on my localhost with some test data and it didn't return a row for topics without latest posts.

 

The self-reference method isn't ideal for myself, since it would need to know its id ahead of time to use the information. That, or it would need a second query to update `latest_id` to itself.

Then again, as I'm thinking about this, maybe I should just stick with the second query, unless someone has a reasonable objection to it.

[Barand]

if you change my query to LEFT JOIN you'll get those topics with no posts (their latest dates will be NULL)

[deadimp]

The left join works now, and I don't know what I was doing before that made it not seem to... Must've been the data I was working with.

This is the final solution I came up with:

select topic.* from `post` topic
left join `post` latest on latest.`id`=topic.`latest_id`
where topic.`topic_id`='-1' and topic.`forum_id`='1' group by topic.`id`
order by if(topic.`latest_id`='-1', topic.`date`, latest.`date`) desc

Thanks!