StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POJoining activity table with the source tables in MYSQL
text
Body
copied!<p>I have a MYSQL 'activity' table where all activity by a user gets logged. Let's say that a user's activities can be either to ask a question or answer a question. So if a user asks a question, their question gets put into the 'question' table and also the activity gets put into the 'activity' table. Same thing if user provides an answer, except that the answer goes into the 'answers' table and the 'activity' table. </p> <p>The question table's fields are:</p> <pre><code>q_id question </code></pre> <p>The answers table's fields are:</p> <pre><code>a_id q_id answer </code></pre> <p>The activity table's fields are:</p> <pre><code>activity_id q_id user_id action_type </code></pre> <p>(There are more columns then shown above, but these illustrate the point.) </p> <p>What I want to be able to do is show a user's recent actions on the site, and actually show what the actions were. </p> <p>My current MYSQL query simply joins the activity table with the question table as follows (using PDO):</p> <pre><code>"SELECT * FROM activity LEFT JOIN question ON activity.q_id = question.q_id WHERE activity.user_id = :user_id ORDER BY activity.a_id desc" </code></pre> <p>This works okay, and can always give me the question for display in the activities list. However, if the activity that is returned is an answer, I would like to be able to show the answer as well. I tried just using a triple join as follows:</p> <pre><code>"SELECT * FROM activity LEFT JOIN question ON activity.q_id = question.q_id LEFT JOIN answers ON activity.q_id = answers.q_id WHERE activity.user_id = :user_id ORDER BY activity.a_id desc" </code></pre> <p>But this just gives me a list of rows that is twice as long as the first query, and does not display the answers when the action_type was an answer.</p> <p>I figure this should be possible, but the more I think about it I believe that maybe I should scrap the activity table and just perform the query directly on the question and answers tables with a UNION? Any opinions on this decision, and any thoughts on the queries? </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload