Multiple mySQL joins in a PDO query

Question

I have a question about a complex mySQL query involving a users table, posts table, and comments table with 2 relationship tables for the comments. Basically I need to call all comments of a certain post and retrieve the name of the user which posted the comment. My table structure is as follows:

users:
--Fields:
----id
----name
----email
----password

posts:
--Fields:
----id
----post_title
----post_category
----post_content
----image_url
----created_at

comments:
--Fields:
----id
----comment
----mood
----created_at

post_comments (which holds the relationship between the post and the comment):
--Fields:
----id
----post_id
----comment_id

user_comments (which holds the relationship between the user and the comment):
--Fields:
----id
----user_id
----comment_id

So far I've written the query to retrieve the comments by topic id as:

SELECT pc.id, c.id AS comment_id, c.comment, c.mood, p.id AS post_id 
FROM post_comments pc 
INNER JOIN posts p ON pc.post_id=p.id 
INNER JOIN comments c ON pc.comment_id=p.id 
WHERE pc.post_id=:post_id

This works perfectly but I'm not sure how to go further as far as calling the username through the user_comment relationship table.

Answer 1

I would do it like

SELECT
    users.name
FROM
    post_comments
JOIN
    comments
    ON comments.id = post_comments.comment_id
JOIN
    user_comments
    ON user_comments.comment_id = comments.id
JOIN
    users
    ON users.id = user_comments.user_id
WHERE
    post_comments.post_id = 1

Unless you need a field of your posts table in the query, I would start from post_comments (otherwise start with posts).

As discussed in the comments, a 1:n relationship can be represented with a column on one table to reference the other table.

In your case:

users:
--Fields:
----id
----name
----email
----password

posts:
--Fields:
----id
----post_title
----post_category
----post_content
----image_url
----created_at

comments:
--Fields:
----id
----post_id (which holds the relationship between the post and the comment)
----user_id (which holds the relationship between the user and the comment)
----comment
----mood
----created_at

Therefore, the query would change to:

SELECT
    users.name
FROM
    comments
JOIN
    users
    ON users.id = comments.user_id
WHERE
    comments.post_id = 1

A rule of thumb is:

• 1:n relationship (e.g. book has many pages, a page belongs to exactly one book)

  -> 2 tables (table pages includes a book_id column referencing the primary key of the books table)

• n:m relationship (e.g. a recipe has many ingredients, an ingredient can be used in many recipes)

  -> 3 tables (table recipes, table ingredients, table ingredients_recipes with recipe_id and ingredient_id)