Vanilla 1 is no longer supported or maintained. If you need a copy, you can get it here.
HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.

MySQL query

edited September 2007 in Vanilla 1.0 Help
I'd like some help with SQL query, which help me to show 5 discussions with newest comments (but discussion must be DISTINCT).

Help somebody??


  • er...

    SELECT DiscussionID, Name FROM `LUM_Discussion` ORDER BY `DateLastActive` DESC LIMIT 5

  • ithcyithcy New
    edited September 2007
    wait, you also need to make sure the query only returns discussions that the user is privileged to view...
    i don't have time to think it up now, but i'l come back to this thread later and if nobody else has done it, i'll post the answer
  • I think he wants 5 different discussions that have new comments. You might have to use:

  • ithcyithcy New
    edited September 2007
    that query i posted would return the 5 different discussions with the newest comments. no distinct is needed as DicussionID is a primary key.

    however, there needs to be some php involved (or else a stored procedure in mysql) to do what i think he really wants, which is to show only discussions that the user is allowed to see. i still don't have time to look at the discussions page code, which does exactly what is needed here except returns the 30 or 50 discussions with the newest comments instead of 5.
  • Thanks, All!
  • Max_BMax_B New
    edited September 2007
    well, if we talk about real life case, things are more complicated, especially if you want to account for whispers :
    $sql = "SELECT d.Name , c.Body, c.CommentID, c.DiscussionID FROM LUM_Comment AS c, LUM_Discussion AS d WHERE c.Deleted = '0' AND d.Active='1' AND d.DiscussionID=c.DiscussionID AND c.DateCreated >= d.DateLastActive #whispers does not touch DateLastActive AND (d.WhisperUserID=$num_util OR d.WhisperUserID!=0 AND d.AuthUserID=$num_util OR d.WhisperUserID=0 AND (c.WhisperUserID=0 OR c.WhisperUserID=$num_util OR c.AuthUserID=$num_util)) #whisper author of recipient GROUP BY d.DiscussionID ORDER BY c.DateCreated DESC LIMIT 5";

    Edit: I copied/edited/pasted the request above from some code of mine. It was made to get most recent comments. It can be simplified a bit. I posted it to show the whisper stuff.
This discussion has been closed.