2

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)

  • It's hard to answer your question without seeing the new code, you created. If you create SQL fiddle with test data and your request, it will be very helpful. – user4035 Apr 11 '13 at 21:41
  • "if a status has 9 comments, the status is shown 9 times. Any advice why/how this happens?" It happens, because you created a query, fetching the data from 2 related tables. And it returns all the corresponding rows from both tables. – user4035 Apr 11 '13 at 22:14
  • Why are you fetching sid field twice: once from the 1-st table, then from the 2-nd table? – user4035 Apr 11 '13 at 22:16
  • good question, now I only select 'created' from the fbss_comments table. Doesn't make any visual difference though. If distinct the right approach to make sure a status is only shown once? thanks for your help so far btw. – Maarten Hartman Apr 11 '13 at 22:33
3

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();
|improve this answer|||||
  • thanks a lot for your extensive answer, I'll test it within a few hours and report back. – Maarten Hartman Apr 12 '13 at 11:05
  • hmm sorry but it's not correct, now I only get statuses with comments (of course, join..). I need to show all statuses, and sort by most recent comments on statuses + most recent status. So either the latest status is on top or the status with the latest comment. this is really hard.. :( shouldn't we use outer join / union? – Maarten Hartman Apr 12 '13 at 18:10
  • @MaartenHartman It's hard to answer without seeing, what is the data and what result does the request produce. Plz, create a fiddle. – user4035 Apr 12 '13 at 20:05
  • 1
    @MaartenHartman IF you still can't fix it, I can come to char for a while. – user4035 Apr 14 '13 at 11:51
  • thank you so much for your help mate. I've decided to solve this in a much easier way: I will just update the timestamp of the status when a comment has been posted to it. In this way I will achieve the same result in a very simple way :) thanks again. – Maarten Hartman Apr 16 '13 at 8:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.