D7 - db_select: order by most recent, commented status

Question

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)

Answer 1

You have a classical case, that requires using 'HAVING' SQL operator. Suppose, we have 2 related tables:

| table1 |
|--------|
|   id   |
|--------|
|    1   |
|    2   |
|--------|

table2.f_id is the FK for table1.id

|         table2        |
|--------+------+-------|
|     id | f_id | order |
|--------+------+-------|
|      1 |    1 |     1 |
|      2 |    1 |     2 |
|      3 |    2 |     3 |
|      4 |    2 |     4 |
|--------+------+-------|

And you want to get all the records from table1 with MAX of the related table2.order. And sort them by table2.order. You must use this SQL to get the data:

SELECT
  table1.id AS id1,
  table2.id AS id2,
  table2.order
FROM
    table1
INNER JOIN
    table2
ON
    table1.id=table2.f_id
GROUP BY
    table1.id
HAVING
    MAX(table2.order)
ORDER BY
    table2.order DESC

And you will get:

| id | id1 | order |
|----+-----+-------|
|  2 |   3 |     3 |
|  1 |   1 |     1 |
|----+-----+-------|

Fiddle

Converting this SQL to Drupal, we'll get this:

$query = db_select("table1");
$query->innerJoin("table2", NULL, "table1.id=table2.f_id");
$query->addField("table1", "id", "id1");
$query->addField("table2", "id", "id2");
$query->addField("table2", "order", "order_field");

$query->groupBy("table1.id");
$query->having("MAX(table2.order)");
$query->orderBy("table2.order", "DESC");
$result = $query->execute();
while($row = $result->fetchAssoc())
{
      var_dump($row);
}

So, for your case, this query must work:

$query = db_select("statuses", "thread");
$query->innerJoin("fbss_comments", "comment", "comment.sid = thread.sid");
$query->fields("thread", array("sid", "sender", "created", "message"));
$query->fields("comment", array("created"));

$query->groupBy("thread.sid");
$query->having("MAX(comment.created)");
$query->orderBy("comment.created", "DESC");
$result = $query->execute();