CODEWITHSUNDEEP
mysqlsql
gmustudent
gmustudent

Reputation: 2209

Turn two queries into one

I'm having some problems with a query I'm writing. This seems like table structure that is very frequent so I'd love some help.

Let's say I have 3 tables similar to a facebook structure. Users, Wall Posts, and Comments. Users can make wall posts, and comment on other wall posts.

On a users page I would like to show a users wall posts and a count of how many comments that post has. This is what I have so far

I query the Wall Post table using the users id as an inner join to the User table. That gives me a result set of wall posts for that user's page. Then I loop through that result set, take the Wall Post id from each result set, and query the Comment table for the Count of comments for that Wall Post Id. This works, however I have to hit the db twice. Can anyone think of a way that I could do this with one query?

First Query Example:

SELECT wallPost.*, user.currentDefault, user.displayName, user.userName 
FROM wallPost 
INNER JOIN user ON user.id = wallPost.sourceUserId 
WHERE wallPost.recipientId = ? ORDER BY wallPost.id DESC

Second Query Example:

SELECT COUNT(id) AS count 
FROM comment 
WHERE wallPostId = ?

Upvotes: 2

Views: 54

Answers (1)

Geek Num 88
Geek Num 88

Reputation: 5312

I would add the count as a subquery and join the subquery to the main query

SELECT
    wallPost.*,
    user.currentDefault,
    user.displayName,
    user.userName,
    wallpost_commentcount.total
FROM
    wallPost
        INNER JOIN user ON user.id=wallPost.sourceUserId
        LEFT JOIN (SELECT wallPostId,COUNT(*) as total FROM comment GROUP BY wallPostId) as wallpost_commentcount ON (wallpost_commentcount.wallPostId=wallPost.id)
WHERE
    wallPost.recipientId = ?
ORDER BY wallPost.id DESC

Please make sure you have an index on comment.wallPostId otherwise this query will take a long time.

I used the LEFT JOIN because you always want to get the wallPost even if there are no comments records yet

Upvotes: 2

Related Questions