title: SQL - Multiple Joins (News Feeds)
body: <p>I am trying to create a News Feed for my website, that shows 3 types of information: Questions, Answers (related to questions) and Discussions.</p>
  
  <p>I have a main table called Events which records all activities of the platform, and a separate table for each type of record (Users, Questions, Answers and Discussions) as shown below:</p>
  
  <p>Table Events</p>
  
  <pre><code>EventID  EventType   fkQuestionID   fkAnswerID   fkDiscussionID
  1        Question    1              NULL         NULL
  2        Question    2              NULL         NULL
  3        Discussion  NULL           NULL         1
  4        Answer      1              1            NULL
  5        Question    3              NULL         NULL
  6        Discussion  NULL           NULL         2
  7        Answer      2              2            NULL
  8        Discussion  NULL           NULL         3
  </code></pre>
  
  <p>Table Questions</p>
  
  <pre><code>QuestionID  fkUserID    QuestionTitle
  1           1           Who is Homer Simpson?
  2           2           What is the capital of Madagascar?
  3           3           What superpower would you choose?
  </code></pre>
  
  <p>Table Answers</p>
  
  <pre><code>AnswerID    fkQuestionID    fkUserID    Answer
  1           1               2           He is a characted of a...
  2           2               4           The capital is...
  </code></pre>
  
  <p>Table Discussions</p>
  
  <pre><code>DiscussionID    fkUserID    DiscussionTitle
  1               3           The best day of my life
  2               1           The worst zombie movies
  3               2           The funiest scens of...
  </code></pre>
  
  <p>Table Users</p>
  
  <pre><code>UserID  Name
  1       Jack
  2       Ana
  3       Rose
  4       Brad
  </code></pre>
  
  <p>This is the expected result:</p>
  
  <p><a href="https://i.stack.imgur.com/hMwgs.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/hMwgs.png" alt="enter image description here"></a></p>
  
  <p>So far, I created the following query:</p>
  
  <pre><code>SELECT E.EventID,
  E.EventType,
  E.fkQuestionID,
  E.fkAnswerID,
  E.fkDiscussionID,
  Q.QuestionTitle,
  U1.Name as QuestionBy,
  A.Answer,
  U2.Name as AnswerBy,
  D.DiscussionTitle,
  U3.Name as PostBy
  FROM events E
  LEFT JOIN questions Q ON Q.QuestionID=E.fkQuestionID
  LEFT JOIN users U1 ON U1.UserID=Q.fkUserID
  LEFT JOIN (
  SELECT fkQuestionID, MAX(AnswerID) AS AnswerID
  FROM answers
  GROUP BY fkQuestionID
  ) t ON t.fkQuestionID = E.fkQuestionID  //show the last answer
  LEFT JOIN answers A ON A.pkAnswerID = t.AnswerID
  LEFT JOIN users U2 ON U2.UserID=A.fkUserID
  LEFT JOIN dicussions D ON D.DiscussionID=E.fkDiscussionID
  LEFT JOIN users U3 ON U3.UserID=D.fkUserID
  </code></pre>
  
  <p>Given that the table Events will store a large amount of rows, I am in doubt about few things, also regarding the slow performance:</p>
  
  <ul>
  <li><p>The current query is already taking +2s to process (30 rows). As I am making a type of News Feed page, instead of making a huge query with all data at once, I am considering to use a loop and make several separate queries to bring the remaining information for each type of Event and load it with ajax (decrease server response and increase the impression of page load). Is it a stupid idea?</p></li>
  <li><p>For the join with the table Users, is there a better way instead of using many LEFT JOINS (U1, U2, U3) for each type of Event? I am afraid of using many LEFT JOINS for all types of events.</p></li>
  <li><p>If someone writes a new Answer, I tried to bring only the last Answer (max AnswerID) related to the Question. However, I am struggling to hide the other events related to the same Question (in order to avoid duplicated feeds/events of the same question). Does anyone know how can I fix this?</p></li>
  </ul>