Okay, as per IRC, you wanted my SQL. Don't say I didn't warn you, my experience with SQL mis... limited.
SELECT * FROM (SELECT talk2.*, logtext2.subject FROM talk2 INNER JOIN log2 INNER JOIN logtext2 ON logtext2.journalid=log2.journalid and talk2.journalid=logtext2.journalid and talk2.nodeid=log2.jitemid and log2.jitemid=logtext2.jitemid WHERE logtext2.journalid=$userid AND talk2.state<>"D" AND talk2.state<>"S" ORDER BY talk2.datepost DESC) innertable GROUP BY innertable.nodeid ORDER BY innertable.datepost DESC LIMIT 10;
[Yes, three-table join *winces* - I would love to have the subject from logtext2 without getting the whole Entry object first - but that can definitely be substituted!]
I tried with just substituting datepost with jtalkid and for my test case (http://activeentries.yvi.hack.dreamwidth.net/) the results it shows are fine and in the correct order. I am not sure about the nested table thing, though, which I found is necessary since I only want one comment for each post and only the most recent one and GROUP BY and ORDER BY don't play nicely together :(. Is that a problem?
And reading back over it it's probably really horrible and if I shouldn't use any joins I'll be more than happy to do without. This was more for me testing how the databases work together and where what is stored. recent_comments.bml does have some stuff that might work otherwise.
no subject
SELECT * FROM(SELECT talk2.*, logtext2.subject FROM talk2 INNER JOIN log2 INNER JOIN logtext2
ON logtext2.journalid=log2.journalid and talk2.journalid=logtext2.journalid and talk2.nodeid=log2.jitemid and log2.jitemid=logtext2.jitemid
WHERE logtext2.journalid=$userid AND talk2.state<>"D" AND talk2.state<>"S" ORDER BY talk2.datepost DESC) innertable
GROUP BY innertable.nodeid ORDER BY innertable.datepost DESC LIMIT 10;
[Yes, three-table join *winces* - I would love to have the subject from logtext2 without getting the whole Entry object first - but that can definitely be substituted!]
I tried with just substituting datepost with jtalkid and for my test case (http://activeentries.yvi.hack.dreamwidth.net/) the results it shows are fine and in the correct order. I am not sure about the nested table thing, though, which I found is necessary since I only want one comment for each post and only the most recent one and GROUP BY and ORDER BY don't play nicely together :(. Is that a problem?
And reading back over it it's probably really horrible and if I shouldn't use any joins I'll be more than happy to do without. This was more for me testing how the databases work together and where what is stored. recent_comments.bml does have some stuff that might work otherwise.
I'll shut up now.