I'm writing my custom module. I use the modules Statuses and 'fbss_comments' (part of the Statuses module). Comments are used to comment on statuses.
Using the following code I get a list of results:
$query = db_select("statuses", "thread")->distinct();
$query->join("fbss_comments", "comment", "comment.sid = thread.sid");
$query->fields("thread", array("sid", "sender", "created", "message"));
$query->fields("comment", array("created"));
$query->orderBy("comment.created", "DESC");
$result = $query->execute();
Now the list of results will be sorted by the most recent posted status. I want to achieve the following: sort the result list by statuses with the most recent comments
Some clarification: normally status(100) will be shown above status(30). When I comment on status(30), status(30) should be shown above status(100). On top of this, when I create a new status(101), this status should be shown on top of the result list.
I hope my goal is clear to you, sadly, I have no idea how to achieve this.
Any advice or help is much appreciated
edit: I've managed to order the results by the most recent commented statuses, however, if a status has 9 comments, the status is shown 9 times. Any advice why/how this happens?
edit 2: I probably should use ->distinct(), I've added it after the db select statement, but this doesn't work (probably because I select two tables)
sid
field twice: once from the 1-st table, then from the 2-nd table?