4

I'm really sorry if this question has already been asked, or answered, but I can't quite seem to find what I need.

I have every other piece of this built, my only question is surrounding inline comments. I would like to do something similar to what Facebook does where they render x comments with a button to display all y comments.

However, the only two ways I can see of doing this are:

  1. Performing a SELECT in the loop that renders each item (I think anyone who might have an answer to this would agree with me that this is a terrible decision)
  2. Performing one large select to pull all comments where news_id is in a certain subset, and then use PHP to iterate over them, select the x most recent, and ignore the rest.

Neither one of these seems like a good solution; however, as they both involve a huge waste of resources.

Does anyone have a potential suggestion for implementing this?

Somnath Muluk
  • 55,015
  • 38
  • 216
  • 226
Colin M
  • 13,010
  • 3
  • 38
  • 58

3 Answers3

1
SELECT * FROM comments_table WHERE article_id = {something} LIMIT {no_of_comments_per_page} SORT BY date DESC

This is a very simple yet powerful query for the comments.

Actual code

<?php
$sql = "SELECT * FROM comments_table WHERE article_id = 24 LIMIT 40 SORT BY date DESC";
$data = mysql_query($sql);
$comments = mysql_fetch_assoc($data);
foreach($comments as $comment){
  $ct++;
  echo "ID: {$ct}";
  echo "<br />";
  echo "Comment: {$comment["comment"]} by {$comment["user"]}";
  echo "Date: {$comment["date"]}";
}
?>
Vish
  • 4,508
  • 10
  • 42
  • 74
  • Yes, but if I show 40 items per page that query must be run 40 times. Alternatively, if I specify an IN() search, I can't limit the number of results per article_id – Colin M Mar 30 '11 at 00:45
  • No, you only need to run this code only once. Check my answer in the edit. – Vish Mar 30 '11 at 00:58
  • I think you're misunderstanding the question. The feed can show 40 items per page, each item can have multiple comments associated. Using this query, I can only fetch items for a single item. – Colin M Mar 30 '11 at 01:02
  • aaah...I get it now...For that you can run a subquery.... Where you will first select all the recent story(feeds) ids and then run the select query with that subquery which will give you the comments with just the feeds ids that you have got. Like SELECT a.comment FROM comments as a, feeds as f WHERE EXISTS (SELECT f.id FROM f WHERE f.id = a.id SORT BY ORDER, LIMIT 10), LIMIT 5 – Vish Mar 30 '11 at 01:10
  • You are awesome. I didn't even think about subqueries...thank you for being my second set of eyes. – Colin M Mar 30 '11 at 01:11
  • Thanks Dude :) Have fun developing your app. – Vish Mar 30 '11 at 01:14
  • For the record, I actually didn't end up using this solution. Benchmarks showed me that actually UNIONing 20 queries together was better than any form of subquery could get me. But you still pointed me in the right direction, so thanks! – Colin M Mar 31 '11 at 04:14
0

I'd use a SELECT with a LIMIT added to the clause, ordering by 'id' in DESC order. Something like.. "SELECT * FROM comments LIMIT 3 DESC"

When the user clicks the "load more comments" button, perform some type of AJAX request with a query similar to.. "SELECT * FROM comments LIMIT X, 3 DESC"

Jonathan
  • 76
  • 2
0

I would do a JOIN, and sort the data out after. Something like:

SELECT articles.id, articles.text, articles.date, 
       comments.id, comments.text, comments.date
FROM articles LEFT JOIN comments ON (comments.article_id = articles.id)
WHERE {some criteria} ORDER BY articles.date, comments.date

The performance issue of having extra comments that would be hidden is really negligible - and you don't have the added overhead of making an ajax request to load the other comments when they click that 'see all' button - you can just hide them, and then display them instantly.

If you wanted only the top 40 posts, you would have to add this condition to the where clause (in fact, you could wrap whatever filtering you're doing in as well):

WHERE articles.id IN (SELECT id FROM articles ORDER by articles.date LIMIT 40)
colinmarc
  • 2,421
  • 1
  • 22
  • 34
  • I see where you're coming from, but the performance issue from having extra comments is really not negligible when you consider that articles can have any number of comments (in the order of thousands). At this point, I'm sure PHP could handle it without an issue but the transport from MySQL to PHP would be a large waste of time. – Colin M Apr 17 '11 at 15:47