Welcome Guest, Not a member yet? Register   Sign In
SOLVED: Query builder join count results
#1

(This post was last modified: 07-25-2017, 08:16 AM by Wouter60.)

I have a table called 'posts' with id, title, author, date and post content.
I also have 2 tables called 'comments' and 'likes'. Both "sub tables" hold the post_id, user_id and datetime.
Can anyone help me with a query that gives me the author, title, date, number of comments and number of likes for all posts, in one list?
I'm trying to solve it with arrays that are created by 3 different queries, but something tells me I can do it a lot easier with joins.
I just can't figure out how.
Reply
#2

This could work.
Have not tested it, but found a similar question on Stack Overflow

PHP Code:
SELECT
post
.*,
COUNT(distinct com.id) AS num_comments,
COUNT(distinct like.id) AS num_likes
FROM posts 
AS post
LEFT JOIN comments 
AS com ON com.post_id post.id
LEFT JOIN likes 
AS like ON like.post_id post.id 
Reply
#3

Thanks! First, I got only one post title and the total number of comments and likes.
I just needed to add "GROUP BY post.id" to get that fixed.
Reply
#4

(This post was last modified: 07-25-2017, 08:28 AM by Martin7483.)

Glad I could help & Graag gedaan Wink
Reply
#5

Thanks guys..was getting stuck in a similar issue myself. Your little conversation helped me out Smile
Constantly learning.
Reply




Theme © iAndrew 2016 - Forum software by © MyBB