1

I have table called comments with following structure:

id | entry_id | date | comment

I also have a table called entries with following structure:

entry_id | title | date | entry

I just want to show recent comments and entries, to which these comments were added.

Now I'm using this query:

SELECT c.id,
       c.date,
       c.comment,
       e.entry_id,
       e.title
FROM   entries e
       INNER JOIN comments c
        ON e.entry_id = c.entry_id
GROUP  BY c.date DESC
LIMIT  50

And I output the results so:

#entry_id
  1 hour ago:
  Some comment

#entry_id
  2 hours ago:
  Some comment

Comments are ordered by date. What I'm trying to do is simply group comments by same entry_id, e.g.:

#entry_id
  1 hour ago:
  Some comment

  2 hours ago:
  Some comment without  repeating the `entry_id`

#other entry_id
  5 hours ago:
  Some comment

How would I do that? No need to write a code for me, just say how would you do that (in pseudo code, for example). This is comments grouping like on facebook or google+ stream, I hope you understand what I mean.

Buddy
  • 1,808
  • 3
  • 19
  • 28

3 Answers3

1

Used this: Grouping arrays in PHP

I think I shouldn't write so much details about my MySQL tables structure, done it with PHP and arrays. Thanks to all for attention.

$groups = array();

foreach($items as $item)
    $groups[$item['value']][] = $item;
Community
  • 1
  • 1
Buddy
  • 1,808
  • 3
  • 19
  • 28
0

First group by the entry_ID and then group by the max date. What you care about is the most recent one. (This is how Facebook does it at least.)

Here is the code in TSQL

In the inner query I select the entry_id and the latest comment's date.

Then I link to that list ordering by either the latest comment or the latest entry (depending on what happened most recently) and then ordering them by entry_id (so all of an entries comments will be first and finally by the date so the comments within an entry will be in the correct order.

Note: I did not test this code so it might have some typos.

SELECT * FROM
  (SELECT e.entry_id, MAX(c.date) as latest
  FROM   entries e
  INNER JOIN comments c ON e.entry_id = c.entry_id
  GROUP BY e.entry_id DESC, max(c.date)
  ) AS LIST
LEFT JOIN entries e ON e.entry_id = LIST.entry_ID
LEFT JOIN comments c on c.entry_ID = LIST.entry_ID
ORDER BY MAX(e.date,list.latest) as time_of_entry_or_comment DESC,
                                    e.entry_ID, 
                                    c.date DESC
Hogan
  • 69,564
  • 10
  • 76
  • 117
  • The problem is my table structure, if I'll group by entry_id then I only will be able to output the last single comment for every entry. – Buddy Sep 02 '11 at 19:39
  • You just use that query for the order of the entry_ids, then you link that result back the list of comments to get all comments in the order you want.... would you like some code? – Hogan Sep 03 '11 at 00:32
  • Yea, show me some code, please. I think I don't understand you properly. – Buddy Sep 03 '11 at 08:45
  • Ohh, you didn't understand what I wanted. I just wanted to be able to group comments by same entry_id. I made it using PHP: http://stackoverflow.com/questions/982344/grouping-arrays-in-php/982365#982365 I already odrder comments by date and it's OK for me. Sorry that you had to write this query that I don't really need. – Buddy Sep 04 '11 at 10:49
  • There is a performance hit to using PHP over sql. Also, my algorithm gives you a facebook like you asked for, maybe you meant something else? – Hogan Sep 04 '11 at 11:10
0

I would imagine you do a query such as this one

select
  e.entry, e.title, c.comment, c.date
from 
  comments c join entries e on c.entry_id = e.entry_id
order by 
  e.entry_id, c.date desc

and the loop over your result set while entry_id is still the same. Although I'm not sure based on your question where you want the output. In the code or in the sql editor.

dispake
  • 3,259
  • 2
  • 19
  • 22