query combining newest threads and posts

[irkenInvader]

I hope this hasen't been answered before, but here goes:

I'm trying to modify the Overview mod. Specifically, I want the latest threads box to show the threads which have the newest posts in them. In other words, I want to see a list of all the threads which have the latest posts in them.

Here is the latest threads query being used in the Overview mod:

PHP Code:

$query3 = $db->query ("SELECT subject,username,uid,tid,views,replies FROM ".TABLE_PREFIX."threads
WHERE visible='1' ".get_unviewable()." AND closed NOT LIKE 'moved|%' 
ORDER BY dateline DESC LIMIT 0,".$mybb->settings['overview_max'].""); 


And here is the latest posts query used in the Overview mod:

PHP Code:

$query6 = $db->query ("SELECT subject,username,uid,pid,tid FROM ".TABLE_PREFIX."posts WHERE visible='1' ".get_unviewable()."
ORDER BY dateline DESC LIMIT 0,".$mybb->settings['overview_max'].""); 


My SQL-foo is weak, but I imagine I need some kind of join between those two tables to get what I want. Something that will let me capture 'subject', 'username',' 'uid', 'tid', 'views', and[/php] 'replies' from those posts that are newest.

Can anyone give me a hand with this?

[LeX-]

in that latest thread query; change dateline into lastpost

[irkenInvader]

I have accomplished what I was looking for ...

PHP Code:

$query3 = $db->query ("SELECT p.tid,t.subject,t.username,t.uid,t.views,t.replies
FROM ".TABLE_PREFIX."posts p LEFT JOIN ".TABLE_PREFIX."threads t ON p.tid=t.tid
WHERE p.visible='1' ".get_unviewable()." AND t.closed NOT LIKE 'moved|%' 
ORDER BY p.dateline DESC LIMIT 0,".$mybb->settings['overview_max'].""); 


The above is the modified query I use to get the info I'm looking for, just in case any one else is looking for this.

[frostschutz]

As Lex said, you should be able to just query the threads with order by lastpost, instead of such an expensive join operation.

[irkenInvader]

Thank you, LeX- and frostschutz.

I've changed my query back to the original and modified the ORDER BY section, as suggested by you, to use the lastpost field instead of JOINing the two tables. However, I have come to realize I really want the last posts username in my output, not the thread's username.

Is there an easy way to get that without another query?

---

edit -- hehe ... silly me.

I'm guessing that's exactly what the latestposter field is for?

Thanks again for your helps.